[DC-188] Pager breaks when HAVING clause references existing table column Created: 04/Nov/09  Updated: 20/Jul/10

Status: Open
Project: Doctrine 1
Component/s: Pager
Affects Version/s: 1.1.5
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Benedict Bacayon Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
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'

 Comments   
Comment by ryan [ 20/Jul/10 ]

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");

Generated at Sat May 18 08:33:06 UTC 2013 using JIRA 5.2.7#850-sha1:b2af0c8dc8537b36121c6a579fabbdf79fc919e5.