There are situations in which Active Record will generate nonsense queries from what a nieve Yii user might think is reasonable code. One such situation is a CActiveDataProvider using CDbCriteria to get data from parent and 1:n child table with a condition on the child table.

The example ¶

A web app manages people and phones. I stripped it down to the essentials that demonstrate the problem and its fix.

We have model Person in a 1:n relation with Phone. We want to find persons with/without phones using CActiveDataProvider and CDbCriteria and run into a surprising error. We fix it with CDbCriteria.together = true;.

The schema ¶

People are in table person and phones in table phone. Each person can have zero, one or more phones. Phones can exist without being owned by anyone. So phone has a non-identifying 1:n relation to person.

[sql]
CREATE TABLE person (
  id INT NOT NULL PRIMARY KEY
);

CREATE TABLE phone (
  id INT NOT NULL PRIMARY KEY,
  person_id INT,
  CONSTRAINT fk_phone_person
    FOREIGN KEY (person_id) REFERENCES person (id)
    ON DELETE SET NULL 
    ON UPDATE RESTRICT
);

The view ¶

We want an admin CGridView of persons with a bool column indicating if that person has any phones. And we want a filter on that column. So we want the view to look like:

$this->widget('zii.widgets.grid.CGridView', array(
	'id'=>'person-grid',
	'dataProvider'=>$model->search(),
	'filter'=>$model,
	'columns'=>array(
		'id',
        array(
            'name' => 'hasPhone',
            'value' => '(isset($data->phones) && $data->phones ? "Yes" : "No")',
            'filter' => CHtml::activeTextField($model, 'hasPhone'),
        ),
        ...

This requires a few things in the Person model:

The model ¶

class Person extends CActiveRecord {
    /**
     * @var filter search criterion. null/'' = don't care, 'no'/'0' mean has no 
     * phone, any other string value = has phone(s)
     */
    public $hasPhone;

    ...

    public function rules() {
        return array(
            ...
            array('id, hasPhone', 'safe', 'on'=>'search'),

    public function relations() {
        return array(
            'phones' => array(self::HAS_MANY, 'Phone', 'person_id'),
            ...

Now in the search() method you might think that this would work:

public function search() {
        $criteria = new CDbCriteria();
        $criteria->with = array('phones');
        $criteria->compare('t.id', $this->id, true);
        $hasPhone = trim($this->hasPhone);
        if ($hasPhone) {
            $criteria->addCondition(
                'phones.person_id is ' 
                . (preg_match('/^(?:no|0)$/iu', $hasPhone) ? '' : 'not') 
                . ' null'
            );
        }
        return new CActiveDataProvider($this, array('criteria' => $criteria));
    }

(Perhaps parsing hasPhone form input into null, true or false should by rights be in the controller. I put it here so we can ignore the controller.)

The problem ¶

In this example, Active Record (Yii 1.1.8) generates queries that fail. For example:

[sql]
SELECT t.id AS t0_c0 FROM person t 
WHERE (phones.person_id is null) LIMIT 10

Obvious nonsense.

The fix ¶

Add this one line to search():

$criteria->together = true;

CDbCriteria.together is not one of Yii's best known and understood thingies which is why I wrote this article.

What's going on? ¶

I'm not really sure but here's my observations.

One might imagine that specifying the phones relation in CDbCriteria.with() would suffice. It tells AR that you want to use the related table and get data from it up front, not lazily get the related data later. But tracing the application's queries shows that when CActiveDataProvider gets data, it handles 1:n related tables a bit different. AR gets the data from the parent table and 1:1 related child tables with one query joining all those 1:1 child tables. 1:n child tables are not part of this query. AR gets data from those with subsequent queries, one query per 1:n child table, specifying the rows it wants with a WHERE PK IN (id1, id2, ...) clause.

That seems like a reasonable optimization for retrieving data—joining 1:n tables could result in a big data set. So giving users control with CDbCriteria.together is very nice.

But that does not strike me as a good reason to leave out the join to a 1:n table when a column in that table is referenced in the query's WITH clause.

So we have to remember to specify togetherness when Yii forgets and separates queries that cannot be separated.