typo3 extbase: m:n relationship, get items with relation count > 0

Question

I have 2 extbase models: product and category. A product can be assigned to multiple categories, this is an m:n relation. Now I need to find all categories with at least 1 article. Is this possible with an extbase query?

I tried this:

$query = $this->createQuery();
$query->getQuerySettings()->setIgnoreEnableFields(true);
$constraints[] = $query->greaterThan('products', 0);
$query = $query->matching(
    $query->logicalAnd($constraints)
);
return $query->execute();

But that just returns no categories at all. Can I do this with extbase queries? Or, if not, how else can I achieve this?

Answer 1

As far as I know, it is not possible with the default QueryBuilder methods. The only way I know is to use the statement() method and write your own sql query. It could look like this:

$sql = "select * from tx_myextension_domain_model_category cats
            join tx_myextension_category_product_mm rel on cats.uid = rel.uid_local
            join tx_myextension_domain_model_product p on rel.uid_foreign = p.uid
            where p.deleted = 0 and cats.deleted = 0 group by cats.uid";

$query = $this->createQuery();
$query->statement()->execute();
return $query->execute();