D7 bug with db_select, 'addExpression' and 'having' conditions?

Question

As I asked in the drupal forum http://drupal.org/node/1393748 (no answers..) I have an issue with a db_select query.

It seems that addExpression "GROUP_CONCAT" and "HAVING" is causing an issue.

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'ATTO' in 'having clause': SELECT COUNT(*) AS expression FROM (SELECT 1 AS ...

<?php
$query = db_select("adm_f", "f");
    $query->fields("f", array("F_ID", "F_TIT", "F_TITOR", "F_DES", "F_ANN", "F_PAE", "F_REG", "F_IMG"));
    $query->addExpression("GROUP_CONCAT(t.TAG_DESC separator ', ')", 'ATTO');
    $query->leftJoin('adm_a', 'a', 'a.F_ID = f.F_ID');
    $query->leftJoin('adm_t', 't', 'a.TAG_ID = t.TAG_ID');
    $query->condition('t.TAG_TIP', 'code', '=');
    // other conditions..
    $query->groupBy('f.F_ID');
    $query->groupBy('f.F_TIT');
    $query->groupBy('f.F_TITOR');
    $query->groupBy('f.F_DESC');
    $query->groupBy('f.F_ANN');
    $query->groupBy('f.F_PAE');
    $query->groupBy('f.F_REG');
    $query->groupBy('f.F_IMG');

    // BUG ???
    if(trim($_GET['F_ATTO']) != '')
        $query->havingCondition('ATTO', '%'.trim($_GET['F_ATTO']).'%', 'LIKE'); 

$query = $query->extend('PagerDefault')->limit(10);
    // echo($query->__toString()); exit;
    // execute
    $rs = $query->execute();
?>

I solved the problem with a workaround, but I would like to use HAVING and not a second query...

Many thanks for your help!!

Answer 1

Sounds like the problem is the paging, because it's the count query that gives you the error. For debugging, try to disable the paging.

If it works without paging, it means that the automatically generated count query doesn't work and you need to create one manually and then set it with $query->setCountQuery() after doing the extend('PagerDefault').

The automatically generated query removes all selected fields, which then causes the error when you are trying to access it in the having condition.