Details
-
Type:
Bug
-
Status:
Reopened
-
Priority:
Blocker
-
Resolution: Unresolved
-
Affects Version/s: 1.2.2
-
Fix Version/s: 1.2.4
-
Component/s: None
-
Labels:None
-
Environment:XP Xamp
Description
Hi All
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:
$q = Doctrine_Query::create();
$q->from('Customer Customer');
$q->addSelect('COUNT(Customer.id) as COUNT_customer_id');
$q->addSelect('Customer_Order.id as order_id');
$q->leftJoin('Customer.Order Customer_Order'); // Many relationship here
$q->limit(20);
It produces this correct DQL:
SELECT COUNT(Customer.id) as COUNT_customer_id, Customer_Order.id as order_id FROM Customer Customer LEFT JOIN Customer.Order Customer_Order LIMIT 20
However the SQL it produces will not return an accurate count – the count is restricted by the limit:
SELECT COUNT(p.id) AS p__0, p2.id AS p2__1
FROM product_customers p
LEFT JOIN product_orders p2 ON p.id = p2.customer_id
WHERE p.id IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20')
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:
SELECT DISTINCT p3.id FROM product_customers p3 LIMIT 20
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.
detect_relations: false package: Example options: type: INNODB charset: utf8 Order: tableName: orders columns: order_id: type: integer(4) primary: true notnull: true customer_id: type: integer(4) order_date: timestamp relations: Customer: type: one local: customer_id foreign: customer_id options: type: InnoDB Customer: tableName: customers columns: customer_id: type: integer(4) primary: true notnull: true autoincrement: true firstname: type: string(45) lastname: type: string(45) streetaddress: type: string(45) city: type: string(45) state: type: string(45) postalcode: type: string(45) relations: Order: type: many local: customer_id foreign: customer_id options: type: InnoDB
Thanks much.
Will Ferrer
edit: split SQL code to make the discussion readable without huge horizontal scrolling...
Is this still a problem? I am not sure if this can be patched easily. The limit subquery algorithm is flawed deeply but also a core part of the current way Doctrine 1 works.