findAll by multiple conditions

Question

I have 4 tables:

• content
• relation
• subject
• group

content:

id(int)
title(varchar)
text(text)

subject:

id(int)
title(varchar)
prent_id(int)

group:

id(int)
title(varchar)

relation:

id(int)
content_id(int)
group_id(int)
subject_id(int)

relation to content is many to many.

group to content is many to many.

subject to content is many to many.

Content may belong to several subjects or several groups.

I want to execute a query somewhat like this:

select * from relation where group_id = 1,3 and subject_id = 1,4,6,7

Answer 1

You can use findAllByAttributes:

$relation_models = Relation::model()->findAllByAttributes(array(
        'group_id'=>array('2','3'),
        'subject_id'=>array('4','5','7')
    )
);

That will give you something like (actual query may contain alias):

select * from relation where group_id IN (1,3) and subject_id IN (1,4,6,7)

---

However if you want : where group_id IN (1,3) OR subject_id IN (1,4,6,7) (i.e OR instead of AND) then you will have to use CDbCriteria:

$criteria = new CDbCriteria;
$criteria->condition = 'group_id IN (1,3) OR subject_id IN (1,4,6,7)'
$relation_models = Relation::model()->findAll($criteria);

You could also use addInCondition :

$criteria->addInCondition('group_id', array('1','2'), 'OR');
$criteria->addInCondition('subject_id', array('1','4','6','7'), 'OR');
$relation_models = Relation::model()->findAll($criteria);

---

Remember to bind parameters incase you are taking input from user, though. Something like this:

$criteria->condition = 'group_id IN (:gid1, :gid2) OR subject_id IN (:sid1, :sid2, :sid3, :sid4)';     
$criteria->params = array(':gid1'=>$xyz,':gid2'=>'2',':sid1'=>$uvw,':sid2'=>$abc,':sid3'=>'6',':sid4'=>'7');
$relation_models = Relation::model()->findAll($criteria);

Answer 2

You can use with()

the orginal relation names will be in your relation model if you select Build Relations option on gii when you were creating model.

    $relation_models = Relation::model()->with('contents','groups','subjects')->findAllByAttributes(array(
        'group_id'=>array('2','3'),
        'subject_id'=>array('4','5','7')
    ));