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)