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):


SELECT DISTINCT a.*

	FROM Auction a

	INNER JOIN AuctionCategory ac

		ON ac.auctionId=a.id

	INNER JOIN Category c

		ON c.id=ac.categoryId

        WHERE c.name IN("catName", "catChildName", etc)

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

specifically this:


Post::model()->with(array(

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

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

))->findAll();

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


Auctions::model()->with(array(

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

))->findAll();

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

$category=Category::model()->findByAttributes(array('name'=>$catName));


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

// starting with the category being requested

$categories=array($category->name);


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

if(count($category->children)>0)

{

    foreach($category->children as $child)

    {

        $categories[]=$child->name;

    }

}


// prepare IN clause

$criteria = new CDbCriteria();

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


$auctions=Auction::model()->with('categories')->findAll($criteria);

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->with='categories';

$criteria->together = true;

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


$auctions=Auction::model()->findAll($criteria);


$dataProvider=new CArrayDataProvider($auctions, array(

            'id'=>'auction',

            'pagination'=>array(

                'pageSize'=>25,

            ),

        ));

Seems to be working fine for CListView.