yii cdbcriteria:复杂的连接 [英] yii cdbcriteria: complex joins
问题描述
我最近使用 Yii 启动了一个项目,我正在尝试习惯查询构建器.现在,我想使用连接进行查询并访问查询中连接表的数据,但我无法使以下内容正常工作:
I recently started a project using Yii and I'm trying to get used to the query builder. Now, I want to make a query using joins and access the joining tables' data in the query but I haven't been able to get the following to work:
我的(简化的)数据库表:
My (simplified) db-tables:
客户(#id,姓名)
员工(#id,姓名)
customer_employee(#customerid, #employeeid)
会计(#id,customerid,started_date,finished_date,月,年)
customer(#id, name)
employee(#id, name)
customer_employee(#customerid, #employeeid)
accounting(#id, customerid, started_date, finished_date, month, year)
- 客户和员工之间的多对多关系
- 客户和会计之间的一对多关系
我想执行以下查询,它将选择与某个员工关联的所有客户并显示他们的会计状态(started_date &finished_date)(如果适用)(否则为 null).
I want to execute the following query, which would select all the customers associated with a certain employee and display their accounting status (started_date & finished_date) if applicable (otherwise null).
以下查询完美无缺,只是我无法让它与 cdbcriteria 和 Yii 查询构建器一起使用:(此外,硬编码 id 仅用于此示例)
The following query works perfectly, it's just that I can't get it to work with the cdbcriteria and Yii query builder: (also, hardcoded id is just for this example)
SELECT name, started_date, finished_date
FROM customer
RIGHT JOIN customer_employee ON customer.id=customer_employee.customerid
LEFT JOIN accounting ON customer.id=accounting.customerid
WHERE customer_employee.employeeid=2';
请帮忙!
推荐答案
1.创建命令
Yii::app()->db->createCommand()
->select('name, started_date, finished_date')
->from('customer c')
->rightJoin('customer_employee ce', 'c.id=ce.customerid')
->leftJoin('accounting a', 'c.id=a.customerid')
->where('ce.employeeid=:id', array(':id'=>2))
->queryRow();
2.CdbCriteria
2. CdbCriteria
$criteria = new CDbCriteria;
$criteria->select = 'name, started_date, finished_date';
$criteria->join = 'RIGHT JOIN customer_employee ON customer.id=customer_employee.customerid ';
$criteria->join .= 'LEFT JOIN accounting ON customer.id=accounting.customerid';
$criteria->condition = 'customer_employee.employeeid=:id';
$criteria->params = array(':id'=>2);
$customers = Customers::model()->find($criteria);
*.不要忘记规则:http://www.yiiframework.com/doc/guide/1.1/en/database.arr
我没有测试你的 SQL,但如果对你有用,这些应该也适用于 Yii.
I didn't tested your SQLs, but if worked for you, these should, also work in Yii.
这篇关于yii cdbcriteria:复杂的连接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!