Retrieving records with MANY_MANY relationship?

I have four relevant tables.

Auction, Category, AuctionCategory, and CategoryParent.

Each auction can be listed in multiple categories, so there is a MANY_MANY relationship between the two using the AuctionCategory (auctionId, categoryId) table.

Each category can also have multiple parents, so there is a MANY_MANY relationship with itself handled by the AuctionCategory (categoryId, parentId) table.

class Category extends CActiveRecord



	public function relations()


            'auctions'=>array(self::MANY_MANY, 'Auction',

                'AuctionCategory(categoryId, auctionId)'),

            'parents'=>array(self::MANY_MANY, 'Category',

                'CategoryParent(categoryId, parentId)'),

            'children'=>array(self::MANY_MANY, 'Category',

                'CategoryParent(parentId, categoryId)'),




class Auction extends CActiveRecord



	public function relations()


            'categories'=>array(self::MANY_MANY, 'Category',

                'AuctionCategory(auctionId, categoryId)'),




I’m am looking for a “Yii way” to retrieve all the auctions with a specified category (and that categories child categories) to display in a CListView. As of right now, I can retrieve those records with the following SQL (assuming the category and that category’s children are represented by name in the IN clause):


	FROM Auction a

	INNER JOIN AuctionCategory ac


	INNER JOIN Category c


        WHERE IN("catName", "catChildName", etc)

I recently had to play around with this. Check out the "with" cactiverecord documentation as well as "relations()",

specifically this:


    'author'=>array('select'=>'id, name'),

    'comments'=>array('condition'=>'approved=1', 'order'=>'create_time'),


So to get auctions in a specific category it would be:


    'categories'=>array('condition'=>'id=:category',  'params'=>array(':category'=>$c))


Thanks for the reply. I used your advice and came up with this. Sloppy, but seems to be working:

// pull the category being requested by name


// this will contain the names of all the categories for the IN clause,

// starting with the category being requested


// if there are children categories, add them to the array



    foreach($category->children as $child)





// prepare IN clause

$criteria = new CDbCriteria();

$criteria->addInCondition("name", $categories);


Since I want to pull not only all the auctions in a certain category, but also the auctions in any child categories, I used the CDbCriteria::addInCondition() method to populate the IN clause with the names of the child categories, just like the SQL statement in my first post.

How do I pull the same records using CActiveDataProvider, so that I can feed it to CListView?

Incase anyone wonders the same thing, I took the array of objects ($auctions, last line of code in my last post), and used it as the "rawData" argument for CArrayDataProvider:

$criteria = new CDbCriteria();


$criteria->together = true;

$criteria->addInCondition("name", Category::catTree($catName)); //catTree() returns an array, each element holding a name of a child category


$dataProvider=new CArrayDataProvider($auctions, array(






Seems to be working fine for CListView.