Yii 1.1: 1:n relations sometimes require CDbCriteria.together

13 followers

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.

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:

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.

Total 4 comments

#12117 report it
rAWTAZ at 2013/02/27 06:35pm
Very helpful article

Thank you tom[] for writing a great article that hopefully most people will be able to find via Google. Otherwise we'll have to refer them to it in the channel (#yii@freenode) :-)

#9557 report it
andrew1 at 2012/08/22 11:52pm
Cheers!

That one line saved me hours of debugging!

My HAS_MANY relations in my with were being ignored causing the selected columns to not be there - until adding together which sorted it!

#9533 report it
Boaz at 2012/08/21 05:36am
another idea

Still stuck and the wiki didn't help me. But I'm reading it again more carefully. What I have thought is that in your case, when you simply need to filter persons that have or don't have phones, I would have defined a STAT relationship and used it for comparison by check if its value is zero or more than zero. That should have cut it, I think.

[EDIT: found my problem, which was not related to this article but rather to my mistake on specified column name]

#8880 report it
dRock at 2012/07/05 11:24pm
yes!

Thank you, I'm now unstuck.

Leave a comment

Please to leave your comment.

Write new article