yii find models through related models by using having

Question

For example I have two related models with MANY_MANY relation. Need to find all models which name contains 'test' or in which relation model2.name contains 'test'.

On sql I wrote this query and it's what I want to get from ActiveRecord in result by using standard relations mechanism and CDbCriteria.

SELECT 
  m1.* 
FROM
  model1 m1 
  LEFT JOIN model_model mm 
    ON mm.from_id = m1.id  
  LEFT JOIN model2 m2 
    ON mm.to_id = m2.id 
GROUP BY m1.id 
HAVING (
    m1.name LIKE '%test%' 
    OR GROUP_CONCAT(m2.name) LIKE '%test%'
);

Simple use Activerecord.findBySql is not good solution because I have many models such as above. So for faster combination any models a relations is prefered.

When I use CDbCriteria.with Yii generate 2 query. When I use CDbCriteria.with with CDbCriteria.together Yii tried to select all columns from related tables, it's redundantly and may be slowly in future because number of relations can become much more than in this example.

Have any idea?

Thanks.

Ákos Kiszely Ákos Kiszely · Accepted Answer · 2014-01-23T08:38:43

You should define the relation in "M1" model class:

public function relations()
{
        return array(        
            'M2s' => array(self::MANY_MANY, 'M2',
                'model_model(from_id, to_id)'),

        );
}

In your M2 model create a scope:

public function nameScope($name)
{
    $this->getDbCriteria()->mergeWith(array(
        'condition'=>'name LIKE :name',
        'params'=>array(':name'=>"%{$name}%"),
    ));
    return $this;
}

If your ready you can do this:

M1::model()->findAll(
    array(
        'condition' => 'name LIKE %test%',
        'with' => array(
            'M2s' => array(
                'scopes' => array(
                    'nameScope' => 'test',
                )
            )
        ),
    )
);

yii find models through related models by using having

1 Answers