yii cdbcriteria: complex joins

Question

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:

customer(#id, name)
employee(#id, name)
customer_employee(#customerid, #employeeid)
accounting(#id, customerid, started_date, finished_date, month, year)

• many-to-many relation between customer and employee
• one-to-many relation between customer and accounting

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

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';

Please help!

Answer 1

I have not run it but some thing like the following is what you need

$criteria = new CDbCriteria(); 
$criteria->select = "name, started_date, finished_date"; 
$criteria->join = "RIGHT JOIN customer_employee ON customer.id=customer_employee.customerid  LEFT JOIN accounting ON customer.id=accounting.customerid"; 
$criteria->condition = "customer_employee.employeeid=2"; 

$models = Customer::model()->findAll($criteria);

Answer 2

$criteria = new CDbCriteria(); 
$criteria->select = "name, started_date, finished_date"; 
$criteria->join = "RIGHT JOIN customer_employee ON customer.id=customer_employee.customerid  LEFT JOIN accounting ON customer.id=accounting.customerid"; 
$criteria->condition = "customer_employee.employeeid=2"; 

$models = Customer::model()->findAll($criteria);

This is how to get data with command for table customer_employee

foreach($model as $value)
        {

        }

Answer 3

A bit late in the day but see this post on my blog which addresses both parts of this difficult sub-query style SQL.

Firstly, to build a Search that relies on attributes from other models Secondly, to use related models simply without using the full Yii AR model

http://sudwebdesign.com/yii-parameterising-a-sub-select-in-sql-builder/932

Answer 4

1. createCommand

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

$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);

*. don't forget the rules: http://www.yiiframework.com/doc/guide/1.1/en/database.arr

I didn't tested your SQLs, but if worked for you, these should, also work in Yii.