join 3 tables in a criteria

Yii 1.1 General Discussion
1

HI,

in my criteria i want to join 3 tables i tried this




		

        $criteria->select='t.nom ,count(compte.idcompte) as cb, sum(envoiglobal.clicks) as clicks, sum(envoiglobal.nbree) as nbsent, sum(envoiglobal.nbrebounced) as bounces, sum(envoiglobal.unsubscribes) as unsubscribes, ROUND((sum(envoiglobal.clicks)/sum(envoiglobal.nbree))*100,2) as pr1  ';

		

		$criteria->join= 'LEFT JOIN envoiglobal,compte ON t.idlist= envoiglobal.idlist and t.idlidt=compte.idlist ';

		

		$criteria->condition="t.idlist = envoiglobal.idlist and  compte.idlist=t.idlist and dateenvoi = '$date' and compte.active=3 ";

		$criteria->group='nom';

but it doesn’t work, i get this error


CDbCommand failed to execute the SQL statement: SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near ","

and if i don’t join compte




		

        $criteria->select='t.nom ,count(compte.idcompte) as cb, sum(envoiglobal.clicks) as clicks, sum(envoiglobal.nbree) as nbsent, sum(envoiglobal.nbrebounced) as bounces, sum(envoiglobal.unsubscribes) as unsubscribes, ROUND((sum(envoiglobal.clicks)/sum(envoiglobal.nbree))*100,2) as pr1  ';

		

		$criteria->join= 'LEFT JOIN envoiglobal ON t.idlist= envoiglobal.idlist ';

		

		$criteria->condition="t.idlist = envoiglobal.idlist and  compte.idlist=t.idlist and dateenvoi = '$date' and compte.active=3 ";

		$criteria->group='nom';

i get




CDbCommand failed to execute the SQL statement: SQLSTATE[42P01]: Undefined table: 7 ERROR: missing FROM-clause entry in subquery for table "compte"

please any help, I’m stuck there it’s been one week :(

thank youu

2

Did you try this format?




'join' => 'LEFT JOIN ... ON ... LEFT JOIN ... ON ...',

(untested)

/Tommy

3

yes i’ve already tried it, without result

4

any help please!!!

5

hi koukiya,

you write $criteria->select but didn’t mention FROM which table?? Don’t know whether correct or not.

Hope can help you. XD