[solved] CActiveRecord scope to only get items when "2nd level" relation is not empty

class Category extends CActiveRecord



	public function relations()


		return array(

			'parent' => array(self::BELONGS_TO, 'Category', 'parent_id'),

			'children' => array(self::HAS_MANY, 'Category', 'parent_id'),

			'products' => array(self::MANY_MANY, 'Product', 'shop_categories_products(category_id, product_id)'),




	//scope wannabe

	public function notEmpty()



			'with' => array('children', 'children.products'),

//			'condition' => 'children.id is not null OR children.products.id is not null',


		return $this;



with the scope "notEmpty" i want to filter categories that contain products or child categories with products

of course my "children.products.id is not null" condition is not working

i tried to define the ‘children.products’ with as ‘children.products’=>array(‘joinType’=>‘right join’), doesn’t work

any ideas?

Hi, i don’t use the build in Yii Active Record (instead i use Doctrine2). So my answer is documentation based… Your try to use “joinType” is the right one. But instead of “RIGHT JOIN” you have to use “INNER JOIN”. The IS NULL condition in not needed…

Regards Robert

didn’t even think of trying that… thanks