Affects Version/s: 1.2.2
Fix Version/s: None
I have encountered a problem that seems very core to the way that doctrine works – if you apply an aggregate function to a column in a table and then join to another table via a many relationship while also using a limit, like so:
It produces this correct DQL:
However the SQL it produces will not return an accurate count – the count is restricted by the limit:
This produces a count of 21 instead of what it should be (1000).
The reason for this is because Doctrine's internal functionality does an intermediary query where it gets gets the ids needed from the customer table in order to use them as the IN portion of the constraints on the final query – like so:
It may seem strange that I am applying a limit to a query which will aggregate to 1 to row . In the actual queries that alerted me to this problem I am using a group by. The reason I am not reporting this bug with a group by in my example however is that you can not use a group by with a many relationship and a limit in doctrine 1.2.2 as it works currently (see bug:
DC-594). However I am personally able to use a limit with a group by and many relationship since I am using a version of the code I patched my self to repair bug DC-594.
Here is my sample schema in case its helpful.
edit: split SQL code to make the discussion readable without huge horizontal scrolling...