Geek Answers Handbook

Yii with joining using CDbCriteria and CActiveDataProvider

This question seems to pop up, but none of the answers helped me solve my problem.

Summary

  • I use Yii to create an application;
  • I have three tables, I'm trying to do Join and filter two of them:
  • I am trying to use CDbCriteria and CActiveDataProvider to connect and filter;
  • I have models for all tables, but when I try to join them, I get an SQL error.

Tables

Database UML

I created a model for tables that I want to join and filter.

Record

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'), ); } ... }

Recordordner

 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(); } ... }

Problem

I have updates added with the condition for record_owner on CDbCriteria, I added a comparison on record_owner.user_id, but now I get SQL errors.

Search()

 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'], ) ) ); }

SQL error

 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

Question

How do I do this Join and Filter?

+4
source share
2 answers

Add $criteria->together = true; into the search method.

Take a look at this for an explanation:

http://www.yiiframework.com/doc/api/1.1/CDbCriteria#together-detail

In particular,

If this property is not set, if the main table is bounded or paginated, an SQL statement will be executed for each HAS_MANY relationship. Otherwise, a single SQL statement will be executed for all.

Since you did not set this value and used pagination, record_owner going to receive a separate request for the result of the achievement. Assuming, of course, that the request is truly complete.

By setting $criteria->together = true; , you force a query, which is a single query that is executed by performing a table join, which you need to filter your query by one of the columns in the corresponding table.

+3
source

The solution proposed by Willem. was a fix in one line.

Add $ criteria-> together = true; to the search () method.

 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 ); $criteria->together = true; return new CActiveDataProvider( $this, array( 'criteria' => $criteria, 'pagination' => array( 'pageSize' => Yii::app()->params['pageSize'], ) ) ); }
+2
source

More articles:


All Articles