DDC-2220: Add joins to Collection Filtering API

[doctrinebot]

Jira issue originally created by user deatheriam:

The recently added collection filtering API only goes half way in achieving a full fledged solution to filter huge collections. It still lacks joins. Look at the next two snippets:

    $criteria = Criteria::create()
        ->where(Criteria::expr()->eq('storeId', $store->getId()))
        ->andWhere(Criteria::expr()->eq('Category', 20))
        ->orderBy(array('popularity' => 'DESC'));
    return $this->BrandCategories->matching($criteria);

This piece of code works but what if there is a need to filter the BrandCategories collection by Categories with some extra criteria:

    $criteria = Criteria::create()
        ->where(Criteria::expr()->eq('storeId', $store->getId()))
        ->andWhere(Criteria::expr()->eq('Category', 20))
        ->andWhere(Criteria::expr()->eq('Category.name', 'Electronics'))
        ->orderBy(array('popularity' => 'DESC'));
    return $this->BrandCategories->matching($criteria);

That would not work.

Ideally we should have a possibility to join other entities, the Category entity in our case here:

    $criteria = Criteria::create()
        ->where(Criteria::expr()->eq('storeId', $store->getId()))
        ->andWhere(Criteria::expr()->eq('Category', 20))
        ->innerJoin(Criteria::expr()->field('Category', 'Category'))
        ->andWhere(Criteria::expr()->eq('Category.name', 'Electronics'))
        ->orderBy(array('popularity' => 'DESC'));
    return $this->BrandCategories->matching($criteria);

What do you think about it, does it make sense to add such functionality?

[doctrinebot]

Comment created by @beberlei:

This is not a good idea, because the API has to be small to allow many different implementations, for example the in memory implementation on ArrayCollection, or the implementaiton on MongoDB ODM.

[doctrinebot]

Issue was closed with resolution "Won't Fix"

[estahn]

@beberlei Would it be possible to provide an alternative?

It might not even be necessary to define a join at the Criteria API. This should be implicit, e.g.

        $expr = Criteria::expr();
        $criteria = Criteria::create();

        return $criteria->where(
            $expr->andX(
                $expr->eq('status', 'active'),
                $expr->eq('supplier.status', 'active')
            )
        );

In this case association behind supplier will be joined automatically.

When using the library rulzerz they are doing autowiring, e.g.

status = "active" AND supplier.status = "active"

The responsible code:

https://github.com/K-Phoen/rulerz/blob/master/src/Executor/DoctrineQueryBuilder/AutoJoin.php

The same would be great for this example:

        $criteria = [
            'id' => 33,
            'status' => 'active',
            'supplier.status' => 'active'
        ];

        /** @var Product $product */
        $product = $repository->findOneBy($criteria);

[LorenzoTuri]

Hi! Sorry for writing in an already closed issue, but years later this issue (filtering associations using criteria), is still very relevant.

Is there hope that this kind of issue/feature will be implemented? It's a very common scenario, f.ex. filtering products whose category has a particular name, or posts with a particular metafields, or every other kind of association filters.

The only workaround I've found for this was to switch to queryBuilder, using joins and where (or other subfilters), but this defeats the "criteria" purpose (in my opinion), since it can be used only for very common queries...

[janopae]

Comment created by @beberlei:

This is not a good idea, because the API has to be small to allow many different implementations, for example the in memory implementation on ArrayCollection, or the implementaiton on MongoDB ODM.

At least these days, the ClosureExpressionVisitor used for filtering ArrayCollections already does support the very kind of implicit Join proposed in this issue: https://github.com/doctrine/collections/blob/420480fc085bc65f3c956af13abe8e7546f94813/src/Expr/ClosureExpressionVisitor.php#L43

As this Feature has been supported in ArrayCollections and not in PersistentCollections for quite a while now, wouldn't it be a first step if at least ORM PersistentCollections supported it (while MongoDB ODM could still decide to implement it)?

How likely would it be for a PR implementing this in ORM to be rejected?