php - Yii CDBCriteria复杂联接查询

我有3张桌子：


银行
银行明细
bank_bank_details


它们之间的关系：

银行型号：

public function relations() {
        return array(
            'bankBankDetails' => array(self::HAS_MANY, 'BankBankDetails', 'bank_id'),
        );
}

bank_details模型：

public function relations() {
        return array(
            'bankBankDetails' => array(self::HAS_MANY, 'BankBankDetails', 'bank_details_id'),
        );
    }

bank_bank_details模型：

public function relations()
    {
        return array(
            'bank' => array(self::BELONGS_TO, 'Bank', 'bank_id'),
            'bankDetails' => array(self::BELONGS_TO, 'BankDetails', 'bank_details_id'),
        );
    }

我想在bank_details模型的管理视图中获取银行详细信息，例如bank_name，ifsc等信息。

我生成的普通SQL查询可以正常运行：

SELECT b.name
FROM bank b
LEFT JOIN bank_bank_details bbd ON bbd.bank_id = b.bank_id
LEFT JOIN bank_details bd ON bd.bank_details_id = bbd.bank_details_id
WHERE bd.bank_details_id = bbd.bank_details_id
LIMIT 0 , 30

现在，我只想将此与Yii CDBCriteria集成在一起，但对我不起作用。请检查以下代码：

public function search() {
        $criteria = new CDbCriteria;
//        select b.name
//        from bank b
//        left join bank_bank_details bbd
//        on bbd.bank_id = b.bank_id
//        left join bank_details bd on bd.bank_details_id = bbd.bank_details_id
//        WHERE bd.bank_details_id = bbd . bank_details_id;

        $criteria->compare('bank_details_id', $this->bank_details_id);
        $criteria->compare('first_holder_name', $this->first_holder_name, true);
        $criteria->compare('nominee1', $this->nominee1, true);
        $criteria->select = 'b.name';
        $criteria->join = 'LEFT JOIN bank_bank_details bbd ON bbd.bank_id = b.bank_id ';
        $criteria->join .= 'LEFT JOIN bank_details bd ON bd.bank_details_id = bbd.bank_details_id';
        $criteria->condition = 'bd.bank_details_id = bbd.bank_details_id';

        return new CActiveDataProvider($this, array(
            'criteria' => $criteria,
            'pagination' => array(
                'pageSize' => 10,
            ),
        ));
    }

错误：
        发现错误500
        CDbCommand无法执行SQL语句：SQLSTATE [42S22]：找不到列：1054“ on子句”中的未知列“ b.bank_id”

任何帮助，将不胜感激。

最佳答案

您需要像这样为表设置别名：
$criteria->alias='b';或使用默认别名“ t”代替“ b”

阅读更多
http://www.yiiframework.com/doc/api/1.1/CDbCriteria#alias-detail

关于php - Yii CDBCriteria复杂联接查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/19923306/