php Yii 使用 CDbCriteria 和 CActiveDataProvider 进行连接

Yii with join using CDbCriteria and CActiveDataProvider

提问by Levi Putna

This question seems to popup a lot however none of the answers have helped me solve my problem.

这个问题似乎经常出现，但是没有一个答案能帮助我解决我的问题。

Summary

概括

• I'm using Yii to create an application;
• I have three tables, i'm trying to do a Join and filter on two of them;
• I'm trying to use CDbCriteria and CActiveDataProvider to do the join and filter;
• I have models for all the tables however when I try join them I get an SQL error.

• 我正在使用 Yii 创建一个应用程序；
• 我有三个表，我正在尝试对其中两个进行连接和过滤；
• 我正在尝试使用 CDbCriteria 和 CActiveDataProvider 进行连接和过滤；
• 我有所有表的模型，但是当我尝试加入它们时，出现 SQL 错误。

Tables

表

I have created an Model for the tables I want to join and filter on.

我为要加入和过滤的表创建了一个模型。

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

RecordOwner

唱片所有者

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 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.

我更新了搜索，在 CDbCriteria 中添加了 record_owner 上的条件，我在 record_owner.user_id 上添加了一个比较，但现在出现 SQL 错误。

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

SQL 错误

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 should I be doing this Join and Filter?

我应该如何做这个加入和过滤？

采纳答案by Willem Renzema

Add $criteria->together = true;to the searchmethod.

添加$criteria->together = true;到search方法中。

Take a look at this for an explanation:

看看这个解释：

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

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

In particular,

特别是，

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.

未设置此属性时，如果主表受限或分页，则会为每个 HAS_MANY 关系执行一条 SQL 语句。否则，将为所有执行单个 SQL 语句。

Since you were not setting this value, and were using pagination, the record_owners were going to be obtained by a separate query on reach result. Assuming, of course, the query actually finished.

由于您没有设置此值，而是使用了分页，因此record_owners 将通过对到达结果的单独查询获得。当然，假设查询实际上已完成。

By setting $criteria->together = true;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.

通过设置，$criteria->together = true;您可以强制将查询设为单个查询，这是通过执行表连接来完成的，这是您想要按相关表中的列之一过滤查询的方式。

回答by Levi Putna

The solution as suggested by Willem. was a one line fix.

Willem 建议的解决方案。是一个单行修复。

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

添加 $criteria->together = true; 到 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 );
    $criteria->together = true;

    return new CActiveDataProvider(
        $this,
        array(
             'criteria'   => $criteria,
             'pagination' => array(
                 'pageSize' => Yii::app()->params['pageSize'],
             )
        )
    );
}