Filtering by nested entity's id leads to an unnecessary join

[ln-e]

For exampe I have 3 entities relation like offer => product => category.

According to documentation i can filter on nested properties like so:

    offer.numeric_filter:
        parent:    'api_platform.doctrine.orm.numeric_filter'
        arguments: [ { product.color: ~ } ]
        tags:      [ { name: 'api_platform.filter', id: 'offer.numeric' } ]

If I want to get all offers where it is in the category with id = 1, I can't filter like product.category this doesn't work.
I have to write product.category.id. And it works, but this leads to an extra table join.
In such case I thinks that product.category can be just filtered like regular column.

api-platfrom/core: v2.1.0-beta.1

[teohhanhui]

You should use the SearchFilter for this: https://github.com/api-platform/docs/blob/e551c5b539e1863f5cde553a44bee0023a289a5c/core/filters.md#search-filter (starting with It is possible to filter on relations too:)

[ln-e]

@teohhanhui thanks for you reply, but it still make an unnessesary join, even with search filter.

I'll take demo app with books (https://github.com/api-platform/demo) and add new entity:

/**
 * @ORM\Entity
 * @ApiResource()
 */
class BookCategory
{
    /**
     * @var int
     *
     * @ORM\Column(type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @var string The title of the book
     *
     * @Assert\Type(type="string")
     * @Assert\NotBlank
     * @ORM\Column
     * @ApiProperty()
     */
    private $title;
...
}

Add field to book

class Book {
...
    /**
     * @var Book The item that is being reviewed/rated
     *
     * @ORM\ManyToOne(targetEntity="AppBundle\Entity\BookCategory")
     * @ORM\JoinColumn(name="category_id", nullable=true, onDelete="SET NULL")
     * @ApiProperty()
     */
    private $category;
...
}

And modify demo filter like so

review.search_filter:
        parent: 'api_platform.doctrine.orm.search_filter'
        arguments: [ { book.category: 'exact' } ]
        tags: [ { name: 'api_platform.filter', id: 'review.search_filter' } ]

Make query

curl -X GET --header 'Accept: application/ld+json' 'http://127.0.0.1:8000/reviews?book.category=1' 

Open Profiler and looks on queries

SELECT 
  DISTINCT r0_.id AS id_0 
FROM 
  review r0_ 
  INNER JOIN book b1_ ON r0_.book_id = b1_.id 
  INNER JOIN book_category b2_ ON b1_.category_id = b2_.id 
WHERE 
  b2_.id = ? 
LIMIT 
  30 OFFSET 0

In such case it can be just

SELECT 
  DISTINCT r0_.id AS id_0 
FROM 
  review r0_ 
  INNER JOIN book b1_ ON r0_.book_id = b1_.id 
WHERE 
  b1_.category_id = ?
LIMIT 
  30 OFFSET 0

[soyuka]

What's the ApiPlatform version you're using? By default I think that we add joins when a field on a related entity is the filtered one (ref). Maybe we could add a condition when it's the identifier to avoid this join?

[ln-e]

In previous comment I use demo app with api-platform/core v2.0.3

Update to 2.1.0-beta and generated sql query become more complicated:

SELECT 
  DISTINCT r0_.id AS id_0, 
  r0_.id AS id_1 
FROM 
  review r0_ 
  INNER JOIN book b1_ ON r0_.book_id = b1_.id 
  INNER JOIN book_category b2_ ON b1_.category_id = b2_.id 
WHERE 
  r0_.id IN (
    SELECT 
      r3_.id 
    FROM 
      review r3_ 
      INNER JOIN book b4_ ON r3_.book_id = b4_.id 
      INNER JOIN book_category b5_ ON b4_.category_id = b5_.id 
    WHERE 
      b5_.id = ?
  ) 
ORDER BY 
  r0_.id ASC 
LIMIT 
  30 OFFSET 0

I don't know api-platform internals well, but i suppose that in such cases there is no need for extra join's or subquery

[soyuka]

See #950 about this.

Can you try to disable EagerLoading to see if it fits better to your expectations?

[ln-e]

Oh, now I see, subquery is needed with eager loading, but only when there is no pagination...

When we enable pagination we make 3 queries

1. select the total count of entities. We just count our entities, we don't care about getting all subresources.
2. select the ids of entities on the current page. The same. Just get the id's of requested resource, not subresource.
3. select entities by ids from second query. Well, we have list of id, there is no need for any conditions at all.

Currently generated query for getting entities looks overhead (r0_.id IN () and r0_.id IN ()):

SELECT 
  r0_.id AS id_0, 
  r0_.body AS body_1, 
  r0_.rating AS rating_2, 
  r0_.author AS author_3, 
  r0_.publication_date AS publication_date_4, 
  r0_.book_id AS book_id_5 
FROM 
  review r0_ 
  INNER JOIN book b1_ ON r0_.book_id = b1_.id 
  INNER JOIN book_category b2_ ON b1_.category_id = b2_.id 
WHERE 
  r0_.id IN (
    SELECT 
      r3_.id 
    FROM 
      review r3_ 
      INNER JOIN book b4_ ON r3_.book_id = b4_.id 
      INNER JOIN book_category b5_ ON b4_.category_id = b5_.id 
    WHERE 
      b5_.id = ?
  ) 
  AND r0_.id IN (?) 
ORDER BY 
  r0_.id ASC

This is looks like job for doctrine's paginator to remove where conditions while getting entities by ids.

But this is not about my original question: When we filter by nested entity's id, there is no need for join.

[soyuka]

This is looks like job for doctrine's paginator to remove where conditions while getting entities by ids.

Hmm, this is wrong because to count you need to keep the "WHERE" clause no?

When we filter by nested entity's id, there is no need for join.

Indeed, this should be pretty easy to fix (see Bridge\Doctrine\Orm\Filter\AbstractFilter). Would you mind doing a PR to improve it?

[ln-e]

Hmm, this is wrong because to count you need to keep the "WHERE" clause no?

We need where only for first (select count) and second (select id) queries. After that, we know exactly which entities to select. On the third query there is no need for other where. We select entities by their id.

Indeed, this should be pretty easy to fix (see Bridge\Doctrine\Orm\Filter\AbstractFilter). Would you mind doing a PR to improve it?

I'll try, but it looks like SearchFilter related, not AbstractFilter, see my PR

[soyuka]

Thanks @IN-e