Orm: 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);