CDbCriteria->with

Hi all,

i have to translate this query:

select count(*), s.nome_stati

from anagrafica_richieste ar,

stati_richieste sr,

stati s

where author = 'utente.autenticato'

and ar.idrichieste_ai = sr.idrichieste

and sr.idstati = s.idstati_ai

group by sr.idstati

to “Yii” language, i’m writing this CDbCriteria object into the model class of “AnagraficaRichieste” table (the first tab of the query):

	public function tebellaRiassuntiva() {

		

		//$elements = $this->with('stati_richieste','stati')->findAll()

		$crit = new CDbCriteria();

		$crit->with = array(

						'statiRichiestes',

						'statiRichiestes.idStatiPerLabel');

		$crit->alias = array(

						self::tableName()=>'ar',

						'statiRichiestes'=>'sr',

						'statiRichiestes.idStatiPerLabel'=>'s');

		$crit->together = true;

		$crit->select = array(

						"count(*)",

						"s.nome_stati");

		$crit->condition = array(

						"ar.author = ':username'",

						"sr.idrichieste = ar.idrichieste_ai",

						"sr.idstati = s.idstati_ai");

		$crit->params = array(

						':username'=>Yii::app()->user->getState('username'));	

		$crit->group = 	"sr.idstati";

			

		$adp = new CActiveDataProvider($this,array(

			'criteria'=>$crit

		));

		

		$pippo = self::model()->find($crit);

		

		return $pippo;	

	}

my question:

in the WITH parameter i set the name of 2 relations correctly defined into the same class,

but i have this error message on the name of the table:

"strpos() expects parameter 1 to be string, array given"

2864

how can i translate the "original" query to the framework language?

[i]sry about the lenght of this post

[/i][size="2"]…thanks in advance!! [/size]

Hi,

CDbCriteria is intended to be used in conjunction with CActiveRecord. But in you case it seems that there is no practical need to use CDbCriteria as it will only add complexity with no visible benefit. Try to use Query Builder for fetching needed data:

public function tebellaRiassuntiva() {

	$dbC = Yii::app()->db->createCommand();

	//uncomment next line if you want to acces data in Object manner: $row->nome_stati

	//$dbC->setFetchMode(PDO::FETCH_OBJ);//fetch each row AS Object


	$dbC->select(array('count(*) AS count', 's.nome_stati'))

		->from(array('anagrafica_richieste ar', 'stati_richieste sr', 'stati s'))

		->where(array(

			'and',

			'author = :author',

			'ar.idrichieste_ai = sr.idrichieste',

			'sr.idstati = s.idstati_ai',

		))

		->group('sr.idstati');

	$dbC->params[':author'] = Yii::app()->user->getState('username');


	return $dbC->queryAll();

}

PS: error "strpos() expects parameter 1 to be string, array given" rised because you have passed array for $crit->alias, but it is a string property!

PPS: try to read DB related Yii guide part, to eliminate misunderstanding of core Yii concepts in the future

Maybe he is migrating to yii from a custom project? He said, he has the relations already set in the model. For consistency, I think it is a good idea to transform oure sql into active record, as long as it is not very complicated code. But I would start with very few options and then add them gradually to the criteria, to see what works and what doesn’t.

Hi guys,

thanks of your interesting,

so, now i’m following the “createCommand” solution and its works fine, so really thank you MadAnd!

this is my code

		$rs = Yii::app()->db->createCommand()

			->setFetchMode(PDO::FETCH_OBJ)

			->select('count(*) count, s.nome_stati nome_stati')

			->from('anagrafica_richieste ar, stati_richieste sr, stati s')

			->where(array(

						'and',

						'ar.author = :username',

						'sr.idrichieste = ar.idrichieste_ai',

						'sr.idstati = s.idstati_ai'),

					array(

						':username'=>Yii::app()->user->getState('username'))

					)

			->group('sr.idstati')

			->queryAll();

ok it works…but at this point i have a conceptual question:

[b]is this the best practice to write a "PDO" query?..if i change the DB(from MySQL to Postgress 4example) under this application is it work fine too?

[/b]

…Thanks in advance!

It depends on what exactly you want to achieve:

• if you need a convenient way for performing CRUD the best choice in Yii is to use ActiveRecord. Also ActiveRecord is very convenient thing in conjunction with zii widgets (CGridView, CListView etc.). This requires writing less SQL code by hand, thus increase DB independence. But the penalty of using AR is its higher memory consumption when selecting many rows (each result row is being represented by separate AR instance).
• in case of fetching big amount of data (rows) or more complex queries Query Builder is the better choice. Using its API one can write complex SQL queries while staying DB independent in most cases, because Query Builder writes declarative parts of query by internal routines using appropriate DB driver.
• in case of very complex queries there is much sense to use DAO and write SQL queries completely by hand. If we use some DBMS-specific constructs in our queries - it won’t be applicable for other DBMS without adjustments.

So you are free to use that method that suite your needs or is more convenient.

PS: if you change you DBMS to PostgreSQL, the query written above won’t work. Postgres will complain that there is s.nome_statistati column is the SELECT list, but no such column in the GROUP BY list. This is because Postgres is more restrictive in SQL syntax than MySQL, but this theme is a off-topic here

ok, so according to the case, is better the use of a method than others…

Thanks MadAnd.