Doctrine 1
  1. Doctrine 1
  2. DC-188

Pager breaks when HAVING clause references existing table column

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 1.1.5
    • Fix Version/s: None
    • Component/s: Pager
    • Labels:
      None
    • Environment:
      Local: Windows/Cygwin/XAMPP
      Stage & Prod: CentOS
      Apache / MySQL
      Framework: Zend

      Description

      Pager breaks when HAVING clause references any existing table column.

      Query used:

      $q = Doctrine_Query::create()
      ->select("u.id, u.first_name, u.last_name, u.is_active")
      ->addSelect('COUNT(u.id) as rolecount')
      ->from('User u')
      ->leftJoin('u.Roles r')
      ->groupBy('u.id')
      ->having("rolecount > 2 and u.is_active = 1");

      Execution of query by itself works fine (eg via execute(), fetch...)

      When putting this query in a Doctrine_Pager, error outputs:

      $pager = new Doctrine_Pager(
      $q,
      $currentPage,
      $resultsPerPage
      );

      $pager->execute();

      Error Received:
      SQLSTATE[42S22]: Column not found: 1054 Unknown column 'u.is_active' in 'having clause'

        Activity

        Benedict Bacayon created issue -
        Hide
        ryan added a comment - - edited

        a quick fix for this is to enclose the field in parentheses when selecting it, then use an alias to reference it inside the having clause. eg

        $q = Doctrine_Query::create()
        ->select("u.id, u.first_name, u.last_name, (u.is_active) u_is_active")
        ->addSelect('COUNT(u.id) as rolecount')
        ->from('User u')
        ->leftJoin('u.Roles r')
        ->groupBy('u.id')
        ->having("rolecount > 2 and u_is_active = 1");

        Show
        ryan added a comment - - edited a quick fix for this is to enclose the field in parentheses when selecting it, then use an alias to reference it inside the having clause. eg $q = Doctrine_Query::create() ->select("u.id, u.first_name, u.last_name, (u.is_active) u_is_active") ->addSelect('COUNT(u.id) as rolecount') ->from('User u') ->leftJoin('u.Roles r') ->groupBy('u.id') ->having("rolecount > 2 and u_is_active = 1");

        This list may be incomplete, as errors occurred whilst retrieving source from linked applications:

        • Request to http://www.doctrine-project.org/fisheye/ failed: Error in remote call to 'FishEye 0 (http://www.doctrine-project.org/fisheye/)' (http://www.doctrine-project.org/fisheye) [AbstractRestCommand{path='/rest-service-fe/search-v1/crossRepositoryQuery', params={query=DC-188, expand=changesets[0:20].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

          People

          • Assignee:
            Guilherme Blanco
            Reporter:
            Benedict Bacayon
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated: