{"id":5603,"date":"2014-04-03T21:36:42","date_gmt":"2014-04-03T21:36:42","guid":{"rendered":"https:\/\/unknownerror.org\/index.php\/2014\/04\/03\/yii-with-join-using-cdbcriteria-and-cactivedataprovider-collection-of-common-programming-errors\/"},"modified":"2014-04-03T21:36:42","modified_gmt":"2014-04-03T21:36:42","slug":"yii-with-join-using-cdbcriteria-and-cactivedataprovider-collection-of-common-programming-errors","status":"publish","type":"post","link":"https:\/\/unknownerror.org\/index.php\/2014\/04\/03\/yii-with-join-using-cdbcriteria-and-cactivedataprovider-collection-of-common-programming-errors\/","title":{"rendered":"Yii with join using CDbCriteria and CActiveDataProvider-Collection of common programming errors"},"content":{"rendered":"<ul>\n<li><img decoding=\"async\" src=\"http:\/\/www.gravatar.com\/avatar\/bbdf106aaa55d8a7f0e78c99c3c60da3?s=32&amp;d=identicon&amp;r=PG\" \/><br \/>\nLevi Putna<\/p>\n<p>This question seems to popup a lot however none of the answers have helped me solve my problem.<\/p>\n<h2>Summary<\/h2>\n<ul>\n<li>I&#8217;m using Yii to create an application;<\/li>\n<li>I have three tables, i&#8217;m trying to do a Join and filter on two of them;<\/li>\n<li>I&#8217;m trying to use CDbCriteria and CActiveDataProvider to do the join and filter;<\/li>\n<li>I have models for all the tables however when I try join them I get an SQL error.<\/li>\n<\/ul>\n<p><img decoding=\"async\" src=\"http:\/\/dl.dropbox.com\/u\/96225407\/stackoverflow\/Screen%20Shot%202013-02-15%20at%209.31.27%20AM.png\" \/><\/p>\n<p>I have created an Model for the tables I want to join and filter on.<\/p>\n<p><strong>Record<\/strong><\/p>\n<pre><code>class Record extends CActiveRecord {\n    public $owner;\n    ...\n    public function rules() {\n        return array(\n            array('given_name, family_name, dob, gender', 'required'),\n            array('qr_id, site_id', 'numerical', 'integerOnly' =&gt; true),\n            array('given_name, family_name, madin_name', 'length', 'max' =&gt; 100),\n            array('country_of_birth, country_of_death, title', 'length', 'max' =&gt; 45),\n            array('gender', 'length', 'max' =&gt; 5),\n            array('dod, profile, epitaph', 'safe'),\n            array('id, qr_id, given_name, family_name, madin_name, dob, dod, country_of_birth, country_of_death, gender, owner', 'safe', 'on' =&gt; 'search'),\n    );\n}\n\n    ...\n    public function relations() {\n        return array(\n            'families_left'  =&gt; array(self::HAS_MANY, 'Family', 'record_left_id'),\n            'families_right' =&gt; array(self::HAS_MANY, 'Family', 'record_right_id'),\n            'headstones'     =&gt; array(self::HAS_MANY, 'Headstone', 'record_id'),\n            'other_names'     =&gt; array(self::HAS_MANY, 'OtherName', 'record_id'),\n            'users'          =&gt; array(self::MANY_MANY, 'Users', 'record_owner(record_id, user_id)'),\n            'record_owner'   =&gt; array(self::HAS_MANY, 'RecordOwner', 'record_id'),\n        );\n    }\n    ...\n}\n<\/code><\/pre>\n<p><strong>RecordOwner<\/strong><\/p>\n<pre><code>class RecordOwner extends CActiveRecord {\n    ...\n    public function relations() {\n        \/\/ NOTE: you may need to adjust the relation name and the related\n        \/\/ class name for the relations automatically generated below.\n        return array();\n    }\n    ...\n}\n<\/code><\/pre>\n<h2>Problem<\/h2>\n<p>I have updates the search added a with condition on record_owner to the CDbCriteria, I have added a compare on record_owner.user_id but am now getting SQL errors.<\/p>\n<p><strong>search()<\/strong><\/p>\n<pre><code>public function search() {\n    \/\/ Warning: Please modify the following code to remove attributes that\n    \/\/ should not be searched.\n\n    $criteria = new CDbCriteria;\n\n    $criteria-&gt;compare('id', $this-&gt;id);\n    $criteria-&gt;compare('qr_id', $this-&gt;qr_id);\n    $criteria-&gt;compare('given_name', $this-&gt;given_name, true);\n    $criteria-&gt;compare('family_name', $this-&gt;family_name, true);\n    $criteria-&gt;compare('madin_name', $this-&gt;madin_name, true);\n    $criteria-&gt;compare('dob', $this-&gt;dob, true);\n    $criteria-&gt;compare('dod', $this-&gt;dod, true);\n    $criteria-&gt;compare('country_of_birth', $this-&gt;country_of_birth, true);\n    $criteria-&gt;compare('country_of_death', $this-&gt;country_of_death, true);\n    $criteria-&gt;compare('gender', $this-&gt;gender, true);\n    $criteria-&gt;compare('title', $this-&gt;title, true);\n\n    $criteria-&gt;with = array('record_owner');\n    $criteria-&gt;compare( 'record_owner.user_id', $this-&gt;owner, true );\n\n    return new CActiveDataProvider(\n        $this,\n        array(\n             'criteria'   =&gt; $criteria,\n             'pagination' =&gt; array(\n                 'pageSize' =&gt; Yii::app()-&gt;params['pageSize'],\n             )\n        )\n    );\n}\n<\/code><\/pre>\n<p><strong>SQL Error<\/strong><\/p>\n<pre><code>CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'record_owner.user_id' in 'where clause'. The SQL statement executed was: SELECT `t`.`id` AS `t0_c0`, `t`.`qr_id` AS `t0_c1`, `t`.`given_name` AS `t0_c2`, `t`.`family_name` AS `t0_c3`, `t`.`madin_name` AS `t0_c4`, `t`.`dob` AS `t0_c5`, `t`.`dod` AS `t0_c6`, `t`.`country_of_birth` AS `t0_c7`, `t`.`country_of_death` AS `t0_c8`, `t`.`gender` AS `t0_c9`, `t`.`profile` AS `t0_c10`, `t`.`epitaph` AS `t0_c11`, `t`.`site_id` AS `t0_c12`, `t`.`title` AS `t0_c13` FROM `record` `t` WHERE (record_owner.user_id LIKE :ycp0) ORDER BY `t`.`given_name` LIMIT 25 \n<\/code><\/pre>\n<h2>Question<\/h2>\n<p>How should I be doing this Join and Filter?<\/p>\n<\/li>\n<li><img decoding=\"async\" src=\"http:\/\/www.gravatar.com\/avatar\/6c5b0663c367fc401502f289688f1ec6?s=32&amp;d=identicon&amp;r=PG\" \/><br \/>\nWillem Renzema<\/p>\n<p>Add <code>$criteria-&gt;together = true;<\/code> to the <code>search<\/code> method.<\/p>\n<p>Take a look at this for an explanation:<\/p>\n<p>http:\/\/www.yiiframework.com\/doc\/api\/1.1\/CDbCriteria#together-detail<\/p>\n<p>In particular,<\/p>\n<blockquote>\n<p>When this property is not set, if the primary table is limited or paginated, a SQL statement will be executed for each HAS_MANY relation. Otherwise, a single SQL statement will be executed for all.<\/p>\n<\/blockquote>\n<p>Since you were not setting this value, and were using pagination, the <code>record_owner<\/code>s were going to be obtained by a separate query on reach result. Assuming, of course, the query actually finished.<\/p>\n<p>By setting <code>$criteria-&gt;together = true;<\/code> you force the query that is made to be a single query, which is done by performing a table join, which is what you want in order to filter your query by one of the columns in the related table.<\/p>\n<\/li>\n<li><img decoding=\"async\" src=\"http:\/\/www.gravatar.com\/avatar\/bbdf106aaa55d8a7f0e78c99c3c60da3?s=32&amp;d=identicon&amp;r=PG\" \/><br \/>\nLevi Putna<\/p>\n<p>The solution as suggested by Willem. was a one line fix.<\/p>\n<p>Add $criteria-&gt;together = true; to the search() method.<\/p>\n<p>public function search() { \/\/ Warning: Please modify the following code to remove attributes that \/\/ should not be searched.<\/p>\n<pre><code>$criteria = new CDbCriteria;\n\n$criteria-&gt;compare('id', $this-&gt;id);\n$criteria-&gt;compare('qr_id', $this-&gt;qr_id);\n$criteria-&gt;compare('given_name', $this-&gt;given_name, true);\n$criteria-&gt;compare('family_name', $this-&gt;family_name, true);\n$criteria-&gt;compare('madin_name', $this-&gt;madin_name, true);\n$criteria-&gt;compare('dob', $this-&gt;dob, true);\n$criteria-&gt;compare('dod', $this-&gt;dod, true);\n$criteria-&gt;compare('country_of_birth', $this-&gt;country_of_birth, true);\n$criteria-&gt;compare('country_of_death', $this-&gt;country_of_death, true);\n$criteria-&gt;compare('gender', $this-&gt;gender, true);\n$criteria-&gt;compare('title', $this-&gt;title, true);\n\n$criteria-&gt;with = array('record_owner');\n$criteria-&gt;compare( 'record_owner.user_id', $this-&gt;owner, true );\n$criteria-&gt;together = true;\n\nreturn new CActiveDataProvider(\n    $this,\n    array(\n         'criteria'   =&gt; $criteria,\n         'pagination' =&gt; array(\n             'pageSize' =&gt; Yii::app()-&gt;params['pageSize'],\n         )\n    )\n);\n<\/code><\/pre>\n<p>}<\/p>\n<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Levi Putna This question seems to popup a lot however none of the answers have helped me solve my problem. Summary I&#8217;m using Yii to create an application; I have three tables, i&#8217;m trying to do a Join and filter on two of them; I&#8217;m trying to use CDbCriteria and CActiveDataProvider to do the join [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-5603","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts\/5603","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/comments?post=5603"}],"version-history":[{"count":0,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts\/5603\/revisions"}],"wp:attachment":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/media?parent=5603"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/categories?post=5603"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/tags?post=5603"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}