Api-platform: Distinct killing performance

[irmantas]

Hi, not sure is this right place to ask, maybe this issue should be referenced to Doctrine project. Anyway maybe you can help solve this issue. When getting some resource collection from backend it seems that all queries a have DISTINCT in it.

For example when fetching articles collections api-platform/doctrine creates this kind of query:

SELECT DISTINCT a0_.id AS id_0, a0_.date_created AS date_created_1 FROM articles a0_ ORDER BY a0_.date_created DESC LIMIT 30

Where DISTINCT is totally unnecessary and killing all application performance. At this case query runs quite fast, but if eager loading is enabled, and there are some joins query may took more than 1 min because of DISTINCT.

[dunglas]

Indeed, this should be discussed on the Doctrine side because this behavior is the one of Doctrine Paginator. Except if we can find an API Platform-specific way of improving this query in some cases.
IIRC Doctrine implemented this query like this because it’s the only working implementation for the general case.

[soyuka]

Use the partial pagination it won't do any distinct anymore :).

[irmantas]

Thank you @soyuka this tip solved issue for me.