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?

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()
    {
        ...
    }
    ...
}