mirror of
https://github.com/doctrine/orm.git
synced 2026-03-23 22:42:18 +01:00
[PR #8333] Implement expression to filter some field with IN a native temporary table #10927
Reference in New Issue
Block a user
Delete Branch "%!s()"
Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?
Original Pull Request: https://github.com/doctrine/orm/pull/8333
State: closed
Merged: No
This patch is to fix a specific issue in a large project and is
submitted to doctrine/orm for proof of concept/idea and to ask for
feedback on how this issue can be resolved. It's not intended to be a
final patch ready to be merged (missing tests and probably some coding
guideline violations) as I want some input from the doctrine developers
on how to approch this problem (which will be described later). Also...
gotta find a better name than "MEMBERTEMP OF"
To describe the problem in very simple terms: "How to use an IN
expression to select from a native table?"
In my project I have a very complicated query that 1) includes a large
section with joins and subqueryies for ACL and 2) a large IN expression
with over 8000 int IDs. This query is written with DQL and uses over 1
minute to execute which is unexceptable. I have been researching alot
and discovered that mysql is not performing the query efficiently as I
had hoped, but putting all the IDs inside a temporary table and then
later referencing that table in a IN-subquery makes the query use about
0.60 sec. DQL however does not allow someone to reference native tables
as DQL is specificly designed (in my eyes) to work with entities known
to Doctrine. I have tried several workarounds or "hacks" to allow
referencing (for instance using a "FakeEntity" where I set the table
name in runtime on ClassMetadata) the temporary table. Using a custom
string function does not work and using a custom SQL walker seem to not
be a feasable solution as we still cant reference the table name itself
without an entity.
The following patch tries to replicate the "MEMBER OF" expression but
instead allow a "string primary" for a collection. This string is never
"validated" and is just put directly into the resulting SQL as a table
name. No criteria support has been added since we put all the ids
already filtered into the temporary table.
For instance:
SELECT user FROM App\User AS user WHERE user.id MEMBERTEMP OF 'some_table'would becomeSELECT user.id FROM users AS user WHERE user.id IN(SELECT t0.id FROM some_table AS t0)