Doctrine 1
  1. Doctrine 1
  2. DC-701

Aggregates functions do not return proper values when using many relationships and limits

    Details

    • Type: Bug Bug
    • Status: Reopened
    • Priority: Blocker Blocker
    • Resolution: Unresolved
    • Affects Version/s: 1.2.2
    • Fix Version/s: None
    • 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...

        Activity

        Hide
        will ferrer added a comment -

        Reopened because I added a patch that I think in essence fixes the issues.

        Show
        will ferrer added a comment - Reopened because I added a patch that I think in essence fixes the issues.
        Hide
        Jonathan H. Wage added a comment -
        Show
        Jonathan H. Wage added a comment - Thanks for the issue and patches. Fixed here http://github.com/doctrine/doctrine1/commit/2ad78e62e360133efc04bf6897bf679c7f3d833b
        Hide
        will ferrer added a comment -

        individual patch for this issue with out other features included

        Show
        will ferrer added a comment - individual patch for this issue with out other features included
        Hide
        will ferrer added a comment -

        adding test cases for these features

        Show
        will ferrer added a comment - adding test cases for these features
        Hide
        will ferrer added a comment -

        reopened because I posted some test cases to add to doctrine along with the patchs previously posted

        Show
        will ferrer added a comment - reopened because I posted some test cases to add to doctrine along with the patchs previously posted

          People

          • Assignee:
            Jonathan H. Wage
            Reporter:
            will ferrer
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated: