Search Based On Checked List Of Items


I have spent a good amount of time looking this up so either I’m missing it or I’m just not getting it (still pretty new to Yii).

I have three tables,

  • listing (id, …)

  • category (id, …)

  • listing_category (listing_id, category_id)

I want to search listings based on a bunch of parameters includes an activeCheckboxList which lists all the categories.

I have the search working, but the issue is the results are being returned with any listing that has at least one selected category when what I’m looking for is all listings with ALL selected categories. The results are more of an “OR” than an “AND”.

Listing relations:

'categories' => array(self::MANY_MANY, 'Categories', 'listing_category(listing_id, category_id)'),

'listingCategories' => array(self::HAS_MANY, 'ListingCategories', 'listing_id'),

Category relations:

'listings' => array(self::MANY_MANY, 'Listings', 'listing_category(category_id, listing_id)', 'index'=>'id'),

Listing_Category relations:

'category' => array(self::BELONGS_TO, 'Categories', 'category_id'),

'listing' => array(self::BELONGS_TO, 'Listings', 'listing_id'),

I use "afterFind" function to put the selected values into the Listing model attribute "categoryIds":

    public function afterFind()


        $this->categoryIds = array_keys($this->categories);



… and the Search function:

public function search()


    $criteria = new CDbCriteria();

    $criteria->compare('', $this->id);


    $criteria->compare('category_id', $this->categoryIds);

    $criteria->with = array('listingCategories');

    $criteria->together = true; 

    return new CActiveDataProvider($this, array(

            'criteria' => $criteria,

Any suggestions?

Check the docs: I think there is a third parameter for compare() that indicates if it is an OR or an AND compare, that defaults to OR. If it’s not compare() it might be addCondition().