Active record: selecting only models with related models or descendants that have related models

So, there are categories and products, and i need to select the categories that are not empty in order to build the product categories menu.

Not empty mean that the category either contains products or a descendant category contains products. I wanted to do it with a scope, but i can’t seem to be getting far.

Something kind of like the following, but i need to recursively check more levels down the categories’ hierarchy

/**

 * This is the model class for table "shopCategories".

 *

 * The followings are the available columns in table 'shop_categories':

 * @property integer $id

 * @property integer $parentId

 * @property integer $order

 * @property integer $isActive

 * @property integer $cstamp

 * @property integer $mstamp

 *

 * The followings are the available model relations:

 * @property ProductCategory $parent

 * @property ProductCategory[] $children

 * @property ProductCategoryL10n[] $l10n

 * @property ProductCategoriesProducts[] $productsJunction

 */

class ProductCategory extends BogoActiveRecord

{

	...


	public function nonEmpty()

	{

		$this->getDbCriteria()->mergeWith(array(

			'with' => array(

				'children',

				'children.products'=>array(

					'condition'=>'products.isActive=1',

					'joinType'=>'INNER JOIN', 'limit'=>1

				),

			),

		));


		return $this;

	}


	...

}

Sometimes writing SQL is easier.

class ProductCategory extends BogoActiveRecord

{

    public static function fetchNonEmpty()

    {

        $sql = "SELECT c.* "

            . "FROM category AS c JOIN products AS p on c.id = p.cat_id "

            . "WHERE p.isActive = 1 "

            . "GROUP BY c.id ORDER BY c.order";

        return self::model()->findAllBySql($sql);

    }