StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POYii with join using CDbCriteria and CActiveDataProvider
text
Body
copied!<p>This question seems to popup a lot however none of the answers have helped me solve my problem. </p> <h1>Summary</h1> <ul> <li>I'm using Yii to create an application;</li> <li>I have three tables, i'm trying to do a Join and filter on two of them;</li> <li>I'm trying to use CDbCriteria and CActiveDataProvider to do the join and filter;</li> <li>I have models for all the tables however when I try join them I get an SQL error.</li> </ul> <h1>Tables</h1> <p><img src="https://dl.dropbox.com/u/96225407/stackoverflow/Screen%20Shot%202013-02-15%20at%209.31.27%20AM.png" alt="Database UML"></p> <p>I have created an Model for the tables I want to join and filter on.</p> <p><strong>Record</strong></p> <pre><code>class Record extends CActiveRecord { public $owner; ... public function rules() { return array( array('given_name, family_name, dob, gender', 'required'), array('qr_id, site_id', 'numerical', 'integerOnly' => true), array('given_name, family_name, madin_name', 'length', 'max' => 100), array('country_of_birth, country_of_death, title', 'length', 'max' => 45), array('gender', 'length', 'max' => 5), array('dod, profile, epitaph', 'safe'), array('id, qr_id, given_name, family_name, madin_name, dob, dod, country_of_birth, country_of_death, gender, owner', 'safe', 'on' => 'search'), ); } ... public function relations() { return array( 'families_left' => array(self::HAS_MANY, 'Family', 'record_left_id'), 'families_right' => array(self::HAS_MANY, 'Family', 'record_right_id'), 'headstones' => array(self::HAS_MANY, 'Headstone', 'record_id'), 'other_names' => array(self::HAS_MANY, 'OtherName', 'record_id'), 'users' => array(self::MANY_MANY, 'Users', 'record_owner(record_id, user_id)'), 'record_owner' => array(self::HAS_MANY, 'RecordOwner', 'record_id'), ); } ... } </code></pre> <p><strong>RecordOwner</strong></p> <pre><code>class RecordOwner extends CActiveRecord { ... public function relations() { // NOTE: you may need to adjust the relation name and the related // class name for the relations automatically generated below. return array(); } ... } </code></pre> <h1>Problem</h1> <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> <p><strong>search()</strong></p> <pre><code>public function search() { // Warning: Please modify the following code to remove attributes that // should not be searched. $criteria = new CDbCriteria; $criteria->compare('id', $this->id); $criteria->compare('qr_id', $this->qr_id); $criteria->compare('given_name', $this->given_name, true); $criteria->compare('family_name', $this->family_name, true); $criteria->compare('madin_name', $this->madin_name, true); $criteria->compare('dob', $this->dob, true); $criteria->compare('dod', $this->dod, true); $criteria->compare('country_of_birth', $this->country_of_birth, true); $criteria->compare('country_of_death', $this->country_of_death, true); $criteria->compare('gender', $this->gender, true); $criteria->compare('title', $this->title, true); $criteria->with = array('record_owner'); $criteria->compare( 'record_owner.user_id', $this->owner, true ); return new CActiveDataProvider( $this, array( 'criteria' => $criteria, 'pagination' => array( 'pageSize' => Yii::app()->params['pageSize'], ) ) ); } </code></pre> <p><strong>SQL Error</strong></p> <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 </code></pre> <h1>Question</h1> <p>How should I be doing this Join and Filter?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload