Doctrine 1
  1. Doctrine 1
  2. DC-513

Complex HAVING clause generates incorrect SQL in Doctrine_Query::getCountSqlQuery() and Doctrine_Query::getLimitSubquery() [patch included]

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.2.1
    • Fix Version/s: 1.2.2
    • Component/s: Pager, Query
    • Labels:
      None
    • Environment:
      php 5.3.0

      Description

      I created a DQL query with a complex having clause, such as:

      Doctrine_Query::create()
      ->from('Record r')
      ->addSelect('r.*')
      ->addSelect('((ACOS(SIN( ... AS miles') // Determines miles from lat/longs
      ->having('(r.location IS NULL) OR (miles <= r.location)');

      The query works fine when executed by itself. However, when passed to Doctrine_Query::getCountSqlQuery(), it strips the r.location from the select fields, however this field remains in the having clause, and SQL errors out saying that it can't find the field. Then, when it is passed to Doctrine_Query::buildSqlQuery(true) by Doctrine_Pager->execute() (true designates to use getLimitSubquery()), getLimitSubquery() does the same thing and doesn't include these fields in the subquery.

      I built a patch that I am using for my application to get around this. What it does is it searches for anything matching `[a-z0-9_]`.`[a-z0-9_]` in getCountSqlQuery() and getLimitSubquery() in the HAVING clause (for instance, would match `r`.`location`) and adds the field to the select query. This is a patch for Doctrine-1.2.1, and I haven't tested it extensively, so I'm not sure if it breaks anything, however in the quick testing I have done it seems to be working OK.

      I greatly appreciate all the work the Doctrine team has done for this amazing ORM - keep up the good work! If I can be of any other assistance, please let me know.

        Activity

        Hide
        Jonathan H. Wage added a comment -

        Thanks for the issue and patch.

        Show
        Jonathan H. Wage added a comment - Thanks for the issue and patch.

          People

          • Assignee:
            Guilherme Blanco
            Reporter:
            Dave Kozma
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: