HOw can I write Join Queries using Criteria

saiprem · May 13, 2010, 5:32am

Hi YII experts,

Can you please tell me how can I write join queries in YII framework

here I wrote using Criteria, but the script is throwing me an error in the constructor part of the CDbCriteria.

here is my code




$Criteria = new CDbCriteria;

$Criteria->join = 'LEFT JOIN tests ON tests.id = 1';

$Criteria->join = 'LEFT JOIN test_section ON test_section.test_id = tests.id';

$Criteria->join = 'LEFT JOIN test_sections ON test_section.test_section_id = test_sections.id';

$Criteria->join = 'LEFT JOIN test_section_questions ON test_section_questions.test_id = tests.id';

$Criteria->addCondition = "test_sections.id = test_section_questions.test_section_id AND questions.id = test_section_questions.question_id";

$Criteria->group = 'GROUP BY questions.id';

					  

$dataProvider=new CActiveDataProvider('Questions', array(

		'criteria'=>$criteria,

		'pagination'=>array(

		'pageSize'=>50,

		),

	));

I am getting the following error





PHP Error

Description


Invalid argument supplied for foreach()

Source File


C:\xampp\www\framework\db\schema\CDbCriteria.php(102)


00090:     public $with;

00091:     /**

00092:      * @var string the alias name of the table. If not set, it means the alias is 't'.

00093:      */

00094:     public $alias;

00095: 

00096:     /**

00097:      * Constructor.

00098:      * @param array criteria initial property values (indexed by property name)

00099:      */

00100:     public function __construct($data=array())

00101:     {

00102: foreach($data as $name=>$value)

00103:             $this->$name=$value;

00104:     }

00105: 

00106:     /**

00107:      * Appends a condition to the existing {@link condition}.

00108:      * The new condition and the existing condition will be concatenated via the specified operator

00109:      * which defaults to 'AND'.

00110:      * The new condition can also be an array. In this case, all elements in the array

00111:      * will be concatenated together via the operator.

00112:      * This method handles the case when the existing condition is empty.

00113:      * After calling this method, the {@link condition} property will be modified.

00114:      * @param mixed the new condition. It can be either a string or an array of strings.


Stack Trace


#0 C:\xampp\www\framework\web\CActiveDataProvider.php(75): CDbCriteria->__construct()

#1 C:\xampp\www\framework\base\CComponent.php(152): CActiveDataProvider->setCriteria()

#2 C:\xampp\www\framework\web\CActiveDataProvider.php(56): CActiveDataProvider->__set()

#3 C:\xampp\www\gmat3\protected\controllers\TestsController.php(248): CActiveDataProvider->__construct()

#4 C:\xampp\www\gmat3\protected\controllers\TestsController.php(189): TestsController->linearTest()

#5 C:\xampp\www\framework\web\actions\CInlineAction.php(32): TestsController->actionStart()

#6 C:\xampp\www\framework\web\CController.php(300): CInlineAction->run()

#7 C:\xampp\www\framework\web\filters\CFilterChain.php(129): TestsController->runAction()

#8 C:\xampp\www\framework\web\filters\CFilter.php(41): CFilterChain->run()

#9 C:\xampp\www\framework\web\CController.php(993): CAccessControlFilter->filter()

#10 C:\xampp\www\framework\web\filters\CInlineFilter.php(59): TestsController->filterAccessControl()

#11 C:\xampp\www\framework\web\filters\CFilterChain.php(126): CInlineFilter->filter()

#12 C:\xampp\www\framework\web\CController.php(283): CFilterChain->run()

#13 C:\xampp\www\framework\web\CController.php(257): TestsController->runActionWithFilters()

#14 C:\xampp\www\framework\web\CWebApplication.php(320): TestsController->run()

#15 C:\xampp\www\framework\web\CWebApplication.php(120): CWebApplication->runController()

#16 C:\xampp\www\framework\base\CApplication.php(135): CWebApplication->processRequest()

#17 C:\xampp\www\gmat3\index.php(12): CWebApplication->run()

My actual query is :

SELECT q.* FROM questions q LEFT JOIN tests t ON t.id = 1 LEFT JOIN test_section ts ON ts.test_id = t.id LEFT JOIN test_sections tss ON ts.test_section_id = tss.id LEFT JOIN test_section_questions tsq ON tsq.test_id = t.id AND tss.id WHERE q.id = tsq.question_id group by q.id

If I execute this query manually in phpmyAdmin, I am getting the results properly, I don’t know what is happened to my code, where I am doing wrong.

Waiting for your valuable response

mdomba · May 13, 2010, 7:30am


$Criteria->addCondition = "1=1"

as per documentation http://www.yiiframew…ondition-detail

APPENDS a condition to the existing condition, as you don’t have an existing condition you get a query like

select * from x where AND 1=1 - and that’s why the error

try with


$Criteria->condition="1=1"

One more thing, you have




$this->join ="A"; 

$this->join="B";

in the end the value of the join is only the last one "B"…

You shoud use .= to concatenate the JOINS eg.




$this->join="A";

$this->join.="B";

Edit:

Now that I looked a second time… the first join criteria is strange




$Criteria->join = 'LEFT JOIN tests ON tests.id = 1';

saiprem · May 13, 2010, 8:00am

mdomba:

$Criteria->addCondition = "1=1"
as per documentation http://www.yiiframew…ondition-detail

APPENDS a condition to the existing condition, as you don’t have an existing condition you get a query like

select * from x where AND 1=1 - and that’s why the error

try with
$Criteria->condition="1=1"
One more thing, you have
$this->join ="A"; 

$this->join="B";
in the end the value of the join is only the last one "B"…

You shoud use .= to concatenate the JOINS eg.
$this->join="A";

$this->join.="B";
Edit:

Now that I looked a second time… the first join criteria is strange
$Criteria->join = 'LEFT JOIN tests ON tests.id = 1';

Yes in the first join criteria I am sending the value dynamically, I can remove that and add it in where condition, I tried by modifying all as you said, but again I got the same error.

Here I did my query like this

$Criteria = new CDbCriteria;

$Criteria->join = ‘LEFT JOIN tests ON tests.id = 1’;

$Criteria->join .= ‘LEFT JOIN test_section ON test_section.test_id = tests.id’;

$Criteria->join .= ‘LEFT JOIN test_sections ON test_section.test_section_id = test_sections.id’;

$Criteria->join .= ‘LEFT JOIN test_section_questions ON test_section_questions.test_id = tests.id’;

$Criteria->Condition = "test_sections.id = test_section_questions.test_section_id AND questions.id = test_section_questions.question_id";

$Criteria->Condition = "1=1";

Nothing change in my error, it is displaying me the same error

andy_s · May 13, 2010, 11:35am

I think you forget spaces in the end of each join: $Criteria->join = 'LEFT JOIN tests ON tests.id = 1 ';

james2010 · June 8, 2010, 8:43pm

could anyone help i try this join




$sth = $db->prepare("SELECT * FROM products LEFT JOIN products_categories ON

			products_categories.products_id = products.products_id WHERE products_categories.categories_id = :var");

			$sth->bindValue(':var',$value,PDO::PARAM_STR); 

			$sth->execute();

with DbCriteria




$criteria = new CDbCriteria;

 $criteria->join ='LEFT JOIN products_categories ON products_categories.products_id = products.products_id';

		  $criteria->condition = "products_categories.categories_id =  :value";

		  $criteria->params= array(":value" => "C1");

zyphers · June 9, 2010, 3:03am




        $criteria = new CDbCriteria;

        $criteria->select = 't.*';

        $criteria->join ='LEFT JOIN products_categories ON products_categories.products_id = t.products_id';

        $criteria->condition = 'products_categories.categories_id = :value';

        $criteria->params = array(":value" => "C1");

The code above will work just fine if you call




        Product::model()->findAll($criteria);

With assuming that you have "Product" model that representing the "products" table.

The “t” is as alias name of “products” table. It is Yii’s default.

And I’m sorry I can’t 100% sure of this because I can’t really run the code but it is what I’m normally do.

Hope it helps.

james2010 · June 9, 2010, 7:32am

you the best. it works. thanx

Rasika · July 9, 2010, 6:12am

how can i write

SELECT DISTINCT user_email

FROM hubs_user

INNER JOIN hubs_property_inquiry ON hubs_user.user_id = hubs_property_inquiry.owner_id;

this query in yii

pls help me out

Madbreaks · September 15, 2010, 7:29pm

addCondition is a method, not a property.

Pretty sure case is the problem there, should be $Criteria->condition = "…"

saiprem · December 7, 2010, 5:33am

Yes tou are right @Madbreaks, but even though it hasn’t worked for me. I tried in different way and it has worked for me as expected

The following script has worked for me




$questionSql = "SELECT q . * , tsq.test_section_id, qa.is_passage, tss.name, qa.name areaName, too.name topicName, qo.id optId, qo.option_order, qo.description, qo.is_correct

FROM tests t

LEFT JOIN test_section ts ON ts.test_id = t.id

INNER JOIN test_sections tss ON ts.test_section_id = tss.id

LEFT JOIN test_section_questions tsq ON tsq.test_id = t.id

AND tsq.test_section_id = ts.test_section_id

INNER JOIN questions q ON tsq.question_id = q.id

INNER JOIN question_areas qa ON qa.id = q.question_area_id

INNER JOIN topics too ON too.id = q.topic_id

LEFT JOIN question_options qo ON qo.question_id = q.id

WHERE q.id != '' AND qo.id!=''

AND t.id =".$testId."

ORDER BY qo.question_id, tsq.question_order, tsq.test_section_id, qo.option_order"; 


$command = '';

$connection = $this->establishConnection();

if (!$connection->getAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY))

 $connection->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);

			

$command = $connection->createCommand($questionSql);

$questionReaderAll = $command->query();

Now I have another issue…what if I used POSTGRES DB…, the above code will not works… So I tried the same query in the following way, but which is not working





$criteria = new CDbCriteria;

$criteria->select = array('t.*,  tsq.test_section_id, qa.is_passage, tss.name, qa.name areaName, too.name topicName, qo.id optId, qo.option_order, qo.description, qo.is_correct');


$criteria->join ='

LEFT JOIN test_section ts ON ts.test_id = t.id

INNER JOIN test_sections tss ON ts.test_section_id = tss.id

LEFT JOIN test_section_questions tsq ON tsq.test_id = t.id AND tsq.test_section_id = ts.test_section_id

INNER JOIN questions q ON tsq.question_id = q.id 

INNER JOIN question_areas qa ON qa.id = q.question_area_id

INNER JOIN topics too ON too.id = q.topic_id

LEFT JOIN question_options qo ON qo.question_id = q.id';


$criteria->condition = "q.id != ''";

$criteria->condition = "qo.id!=''";

$criteria->condition = 't.id ='.$testId;

$criteria->order = 'qo.question_id, tsq.question_order, tsq.test_section_id, qo.option_order';


$QuestionsTestsData = Tests::model()->findAll($criteria);

The above JOIN query is working fine with criteria and giving the count of results as expected, but the data what I am selecting in the $criteria->select is not coming.

I have even checked the attributes of thje fetched results, it is giving only the results of t.*.

Can any one please help me.

kokomo · December 7, 2010, 10:27am

Maybe yourI problem is that you use $criteria->condition=… three times.

Try to use addCondition

saiprem · December 7, 2010, 1:23pm

Thanks @Kokomo, but that seems not the problem, if that is making difference, then

it should throw me an error, but it is not.

Also giving me the expected count of records but not the selected data. Retrieving the complete data from only one table.

So I tried in the following way



&#036;criteria-&gt;addCondition(&quot;q.id&#33;=''&quot;);


&#036;criteria-&gt;addCondition(&quot;qo.id&#33;=''&quot;);


&#036;criteria-&gt;addCondition(&quot;t.id=&#036;testId&quot;);

Even the above one also giving the same output

zaccaria · December 7, 2010, 2:50pm

Are you sure that Yii generates the sql you think?

Check the sql with logs, and test this sql in phpmyadmin. Maybe Yii si working properly and the query itself is bagous.

hitu1512 · July 9, 2012, 5:28am

how to write this query in yii

select post.title, user.username from post, user where post.member_id<>user.id AND post.parent_id<>0;

Thanking You.

coolashu · July 9, 2012, 1:49pm

There are several ways to write such queries,like

$query = "select post.title, user.username from post, user where post.member_id=user.id AND post.parent_id<>0";

$result = Yii::app()->db->createCommand($query)->queryAll();

or you can use CSqlDataProvider

$result = new CSqlDataProvider($query);