applying a default scope with reference to a relation in yii

Question

I can't find too much documentation on applying a default scope to a model in yii, I was wondering if someone could explain or point me in the right direction.

The quick version of my question:

Is it possible to add a relation to a default scope, or to add a 'with' criteria by default to every AR search on a model?

The long version of my question:

A quick summary of my app:

I have two models, provider and item. Which have a m:1 relationship where a provider can have many item's, but each item can only have one provider.

So far I have these relations:

class Provider extends CActiveRecord
{
    ...
    public function relations()
    {
        return array(
            'items' => array(self::HAS_MANY, 'Item', 'id_provider', 'order'=>'rank DESC'),
        );
    }
    ...
}

class Item extends CActiveRecord
{
    ...
    public function relations()
    {
        return array(
            'provider' => array(self::BELONGS_TO, 'Provider', 'id_provider'),
        );
    }
    ...
}

Within my items model I've already got a defaultScope which filters out all offline items (i.e. only displays items which are set to offline = false):

public function defaultScope()
{
    $alias = $this->getTableAlias(false,false);
    return array(
        'condition'=>"`$alias`.`offline` = false",
    );
}

What I want to do now, is also filter out items where their provider is set to offline (i.e. only show items where provider.offline = false alongside the current item.offline = false).

I've tried joining the providers table in the defaultScope:

public function defaultScope()
{
    $alias = $this->getTableAlias(false,false);
    return array(
        'join'=>"JOIN `provider` AS `provider` ON `provider`.`id` = `$alias`.`id_provider`",
        'condition'=>"`$alias`.`offline` = false AND `provider`.`offline` = false",
    );
}

However the JOIN applies after the ON statement, and causese an error (CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'provider.offline' in 'on clause').

I've also tried adding a with criteria to the defaultScope:

public function defaultScope()
{
    $alias = $this->getTableAlias(false,false);
    return array(
        'with'=>"provider",
        'condition'=>"`$alias`.`offline` = false AND `provider`.`offline` = false",
    );
}

But I get the same error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'provider.offline' in 'on clause'`).

Any suggestions?

Antithesis

Answer 1

There are a couple things I would try:

First, change your condition to apply to the whole thing (and don't forget that if there are not items for a provider, it will not return the provider)

public function defaultScope()
{
    return array(
        'with'=> array("provider" => array(
            'condition'=> "t.offline = false AND provider.offline = false",
        )
    );
}

Second, try adding scopes to your models and then referencing them in the default scope like so:

public function defaultScope()
{
    return array(
        'scopes'=> array('default'),
    );
}

class Provider extends CActiveRecord
{
    ...
    public function scopes()
    {
        ...
    }
    ...
}

class Item extends CActiveRecord
{
    ...
    public function scopes()
    {
        ...
    }
    ...
}

Staging

Of course! I was putting the condition in the main 'condition' statement, not within the 'with'. Your first suggestion works perfectly, thanks! – Antithesis

Answer 2

Just had a similar problem. While Benjamin's first suggestion pointed me in the right direction (much appreciated!), I came across an unexpected issue using 'condition' and 'with', which it might be helpful to be aware of.

If the table you're joining to ('provider' in the example) has its own defaultScope, it appears this gets applied as part of the SQL ON clause when you use 'with', restricting the rows returned.

Because an outer join is used, you still get a record back for every row in the primary table ('item'), but the 'provider' fields may be null for some rows if the provider's defaultScope prevented those rows being returned.

This doesn't cause an issue until you try to apply a 'condition' involving one of those provider fields. This is done as part of the WHERE clause which is processed after the join, but because that field is null the condition will fail, and the record won't be returned.

In some cases this may be the behaviour you want, but in other cases you might want to move the tests on the 'provider' fields to within the join by using the on option:

public function defaultScope()
{
return array(
    'with'=> array("provider" => array(
        'condition'=> "t.offline = false",
        'on'=>"provider.offline = false",
    )
);
}

Crashing