Yii CDBCriteria复合连接查询
我有三张桌子：
- 银行
- bank_details
- 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,
),
));
}错误： CDbCommand 500发现CDbCommand执行CDbCommand语句失败: SQLSTATE42S22:列未找到:在'on子句‘中有1054个未知列'b.bank_id’
任何帮助都将不胜感激。
回答 4
Stack Overflow用户
发布于 2013-11-12 08:18:55
您需要像这样为您的表设置别名：$criteria->alias='b';或使用默认别名"t“而不是"b”
在http://www.yiiframework.com/doc/api/1.1/CDbCriteria#alias-detail阅读更多内容
Stack Overflow用户
发布于 2013-11-12 08:19:58
尝试：
public function search() {
$criteria = new CDbCriteria;
$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 = 'bank.name';
$criteria->with = array(
'bankBankDetails' => array('joinType'=>'LEFT JOIN'),
'bankBankDetails.bank' => array('joinType'=>'LEFT JOIN'),
);
$criteria->addCondition('t.bank_details_id = bankBankDetails.bank_details_id');
return new CActiveDataProvider($this, array(
'criteria' => $criteria,
'pagination' => array(
'pageSize' => 10,
),
));
}主表(在FROM中)有别名t，在compare之后，您已经有了一些条件，需要添加到现有的，而不是重写。
更新
如果您从模型bank_details进行查询，那么主表(在FROM中)必须是bank_details。查询将是：
SELECT b.name
FROM bank_details bd
LEFT JOIN bank_bank_details bbd ON bd.bank_details_id = bbd.bank_details_id
LEFT JOIN bank b bbd ON bbd.bank_id = b.bank_id
WHERE bd.bank_details_id = bbd.bank_details_id
LIMIT 0 , 30Stack Overflow用户
发布于 2013-11-12 07:32:31
在这里，$criteria->select = 'b.name';只选择银行名称，没有选择id。
不确定yii格式，比如
$criteria->select = 'b.name,b.bank_id';
或
$criteria->select = array('b.name,b.bank_id');
https://stackoverflow.com/questions/19923306
复制相似问题
腾讯云开发者
