0

I am new to yii1 and I'm now trying to create the following query in yii1 with the query builder.

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

I have done the following so far:

$criteria1->select = array('t.*');
$criteria1->group = 't.parent_id, t.id';

I now thought that I need a second $criteria for my subquery

$criteria2 = new CdbCriteria;
$criteria2->addNotInCondition('t.parent_id' != ??????)
$criteria1->mergeWith($criteria2);

But how do I get the parent_id? I return $criteria1 to a GridDataProvider in the end, so I do not have a findAll() anywhere. Is it possibly to build the above SQL with Yii Query Builder or how can I achieve that?

UPDATE

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


$sql = Yii::app()->db->createCommand()
                    ->select('*')
                    ->from('categories')
                    ->text;
$criteria->addNotInCondition('parent_id',$sql);
$criteria->group = 't.parent_id, t.id';

That seems to work, but what I don't seem to get is the parent categories which have not got any children. I also can not see them with my SQL above, so how would I access them?

UPDATE 2

It seems to be that I need the following SQL Query to achieve my task, but I don't seem to be able to get it into Yii any advice?

SELECT id, parent_id FROM categories AS a
WHERE NOT EXISTS (SELECT * FROM categories AS b WHERE a.id = b.parent_id)
GROUP BY parent_id, id
Luka
  • 748
  • 2
  • 9
  • 36
  • you can review this solution for your problem: http://stackoverflow.com/questions/8467698/sub-queries-activerecord-yii – NikuNj Rathod Sep 12 '16 at 09:15
  • Thank you for your comment, but I can not see the answer right now. I have updated above and the update shows me all the Subcategories with their Parent category, but I can not see the Parent Category, when there is now Child. So the Error is already in the SQL Query itself, because that does not return me the records for parents without children. – Luka Sep 12 '16 at 09:44

0 Answers0