$criteria and mergeWith for subquery

Hi,

I have a bit of a possibly strange question. I have Main- and Subcategories on my page. This is set up as Parents and Children, meaning in my database I have a field called parent_id. I have now got a function which shows all the children and automatically displays the Parent with it. Unfortunately I also get the Parent itself displayed, so it does show up twice in my list, which I only want to happen, when there is no child, since it will be pulled in otherwise through that child. I achieve that by grouping parentId.




$criteria1->select = array('t.*');

$criteria1->group = 't.parent_id, t.id';



That all works fine. I was now wondering how I can make sure that the parent won’t be shown, if it got a child, so I thought that if I create a Subquery where I exclude categories which id show up as a parent_id, then this should be the answer. So I am pretty much trying a


SELECT id, parent_id 

FROM categories 

WHERE parent_id NOT in (SELECT distinct id) GROUP BY parent_id, id

So my thought is that I will need a:




$criteria2 = new CdbCriteria;

$criteria2->addNotInCondition('t.parent_id' != <img src='http://www.yiiframework.com/forum/public/style_emoticons/default/huh.gif' class='bbc_emoticon' alt='???' />???)

$criteria1->mergeWith($criteria2);



But how can I achieve this? The findAll() is at the end, so I don’t have an id at this point returned. Is it possible at all?