slow query: PreferredContentType

[BacLuc]

SELECT c0_.id          AS id_0,
       c0_.createTime  AS createtime_1,
       c0_.updateTime  AS updatetime_2,
       c0_.name        AS name_3,
       c0_.active      AS active_4,
       c0_.entityClass AS entityclass_5,
       c0_.jsonConfig  AS jsonconfig_6
FROM content_type c0_
         INNER JOIN category_contenttype c2_ ON c0_.id = c2_.contenttype_id
         INNER JOIN category c1_ ON c1_.id = c2_.category_id
WHERE c0_.id IN (SELECT c3_.id
                 FROM content_type c3_
                          INNER JOIN category_contenttype c5_ ON c3_.id = c5_.contenttype_id
                          INNER JOIN category c4_ ON c4_.id = c5_.category_id
                 WHERE c4_.id = $1)
ORDER BY c0_.createTime DESC, c0_.name ASC;

[usu]

Affected endpoint: http://localhost:3000/api/content_types?categories=%2Fapi%2Fcategories%2Feddb2cd8cee0

Generated Query:

SELECT
  c0_.id AS id_0,
  c0_.createTime AS createtime_1,
  c0_.updateTime AS updatetime_2,
  c0_.name AS name_3,
  c0_.active AS active_4,
  c0_.entityClass AS entityclass_5,
  c0_.jsonConfig AS jsonconfig_6
FROM
  content_type c0_
  INNER JOIN category_contenttype c2_ ON c0_.id = c2_.contenttype_id
  INNER JOIN category c1_ ON c1_.id = c2_.category_id
WHERE
  c0_.id IN (
    SELECT
      c3_.id
    FROM
      content_type c3_
      INNER JOIN category_contenttype c5_ ON c3_.id = c5_.contenttype_id
      INNER JOIN category c4_ ON c4_.id = c5_.category_id
    WHERE
      c4_.id = ?
  )
ORDER BY
  c0_.createTime DESC,
  c0_.name ASC

This query is wrong (generates a wrong join-query with way too many results). The culprit is api platform FilterEagerLoadingExtension which changes the where-clause wrongly. FilterEagerLoadingExtension was meant for OneToMany relations (as commented in the file itself), however it is applied here to a ManyToMany relation.

This problem was probably solved by api-platform/core#4680 but never merged.

[BacLuc]

Maybe improved with:

• Hotfix performance improvements #6851