[mr_goodcat]

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

[seenivasan]

Dear Friend

Just to simulate your scenario

I have the table edibe.id,e_id,name.

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.
In that case query may throw errors.
That is why I used IN condition.

Regards.

[mr_goodcat]

seenivasan, on 24 January 2013 - 11:21 PM, said:

Dear Friend

Just to simulate your scenario

I have the table edibe.id,e_id,name.

vegetable, fruit, millet,cereal are catagories with e_id=0.

The following is the code using ActiveRecord.

It's all ok, but is there a way not to use table name implicitly and use table alias like "{{category}}" instead?