Reusable criterias generates, under some circumstances, a non fast SQL statement

[alexpozzi]

Given

• I have two entities, article and category. The article entity has a many to one reference to the category entity.
• In my application I need to be able to search articles by category.
• My article's definition includes category's information and I don't want Doctrine to execute extra queries to get all category details.
• Other entities references the category object and I want to reuse some of the logic to generate DQL queries (so we are using criteria's factories)

When
I query my articles by category using the following code (simplified in order to stay in the context):

// CategoriesCriteria
public function create(array $categories): Criteria
{
  return Criteria::create()
    ->andWhere(
      Criteria::expr()->in('category', $categories)
    );
}

// ArticleRepository.php
public function getArticlesByCategories(array $categories): array
{
  $this->createQueryBuilder('article')
    ->select('content')
    ->addSelect('category')
    ->leftJoin('category', 'category')
    ->andWhere(CategoriesCriteria::create($categories))
    -> getQuery()
    ->getResults()
  ;
}

Then
The generated SQL is the following

SELECT article.*, category.* // not really like this but it's to explain
FROM article
LEFT JOIN category ON article.category_id = category.id
WHERE category.id IN (1,2,3);

Now this is working and I get the right articles but it's not super fast (especially with more complex queries) as the category filter is applied on the joined category.id field instead of directly to the article.category_id field.

I noticed that this is caused by the fact that my criteria's factory doesn't specify the name of the main entity when generating the DQL expression (uses just category instead of article.category).

If I change the expression used in my criteria's factory to Criteria::expr()->in('article.category', $categories) everything works as expected and the generated SQL looks like:

SELECT article.*, category.* // not really like this but it's to explain
FROM article
LEFT JOIN category ON article.category_id = category.id
WHERE article.category_id IN (1,2,3);

The problem is that it makes my criteria's factory non-reusable.
I know I can pass the name of the as an input table and concatenate that to my expression but I was wondering if there was a way to tell Doctrine to apply the criteria to the from instead of to a join.

[derrabus]

What makes this query slow is the left join rather than the criteria being applied at the wrong side of the join. An inner join should yield the same result with a better performance. But I feel this doesn't really answer your question. 😓

[alexpozzi]

Not really, even when using an inner join the query is slower then using the condition on the article.category_id field (the real query is not that simple).