[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 Wed Oct 22 12:23:25 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.