3

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!!

6

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.

  • Wow!!! That was the problem.. Many thanks for your precious help!!! AT the end I have to write a second query for counting the records but now I can use having in the main query. Anyway it seems a D7 bug? right? – delmi Jan 6 '12 at 13:53
  • No, it's not really a bug. PagerDefault tries to create a working query automatically for as many solutions as possible, but it is not possible to have one that works always. – Berdir Jan 6 '12 at 14:35
  • To define a custom count query, use $query->setCountQuery($your_count_query). See api.drupal.org/api/drupal/includes--pager.inc/function/… – Berdir Jan 6 '12 at 14:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.