I have two types of categories stored in the same table (tbl_category) — categories and subcategories:
categoryId parentId alias
Categories have parentId equal to zero, subcategories — equal to categoryId of their parent.
How do I retrieve a subcategory using CActiveRecord, if I know aliases of both category and subcategory? I need an equialent of the following SQL query:
SELECT s.* FROM tbl_category AS s
WHERE s.alias LIKE 'subcategoryAlias' AND
s.parentId = (SELECT categoryId FROM tbl_category WHERE parentId = 0 AND alias LIKE 'categoryAlias')
vegetable, fruit, millet,cereal are catagories with e_id=0.
The following is the code using ActiveRecord.
$items=Edible::model()->findAll(array(
'condition'=>"t.name LIKE :sub AND t.e_id IN(SELECT edible.id FROM edible WHERE e_id=0 AND edible.name LIKE :main)",
'params'=>array(":sub"=>"%a%",":main"=>"%r%"),
));
foreach($items as $item)
echo $item->name."</br>";//brings apple,orange,banana,grape from FRUITS and wheat and maze from CEREAL
We are using LIKE, there there are many chances to get more than one record.