Query failed
spflanze - March 16, 2009 - 16:05
| Project: | User List API |
| Version: | 5.x-1.0 |
| Component: | Code |
| Category: | bug report |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | needs review |
Jump to:
Description
When the Node Count component of this module is enabled I get this error:
* warning: pg_query() [function.pg-query]: Query failed: ERROR: column "u.name" must appear in the GROUP BY clause or be used in an aggregate function in F:\Websites\Mirrors\drupal5\includes\database.pgsql.inc on line 126.
* user warning: query: SELECT DISTINCT u.uid, u.name, u.status, u.created, u.access, COUNT(DISTINCT node.nid) AS nid_count FROM users u LEFT JOIN users_roles ur ON (u.uid = ur.uid) LEFT JOIN node node ON (u.uid = node.uid) WHERE u.uid != 0 GROUP BY u.uid ORDER BY u.created DESC LIMIT 50 OFFSET 0 in F:\Websites\Mirrors\drupal5\includes\database.pgsql.inc on line 145.I am using Postgresql 8.2.12 with Drupal 5.15
#1
I have fixed this. The problem is Postgresql is more restrictive in what it can have in the query's field list when aggregate functions and GROUP BY is used. The answer is to use a subquery. Here is the function "userlist_build_query()" in file "userlist.module" containing the modifications that fixed this:
<?php
function userlist_build_query()
{ global $userlist_callbacks;
if (!isset($userlist_callbacks))
{ $userlist_callbacks = module_invoke_all('userlist');
}
$filters = user_filters();
$where = $args = $join = array();
$fields = array('u.uid', 'u.name', 'u.status', 'u.created', 'u.access');
//begin userlist hook
foreach ($userlist_callbacks as $callback => $value)
{ if (isset($value['filter']))
{ $filters[$callback] = $value['filter'] + array('table' => $value['table'], 'field' => $value['field'], 'join' => $value['join'], 'aggregate' => $value['aggregate']);
}
//==table
if ($table = userlist_get_table_name($value['table']))
{ if (!in_array($table, array('u', 'ur')))
{ //join if not users or users_roles
$join[] = $value['join'] ?
'LEFT JOIN {' . $table . '} ' . $table . ' ON (u.uid = ' . $table . '.uid' . ' AND ' . $table . '.' . $value['join'] . ')' :
'LEFT JOIN {' . $table . '} ' . $table . ' ON (u.uid = ' . $table . '.uid' . ')';
}
}
//==fields
if ($table && $value['field'])
{ if ($value['aggregate'] = userlist_get_aggregate_function($value['aggregate']))
{ switch ($GLOBALS['db_type'])
{ case 'mysql':
case 'mysqli':
$fields[] = strtoupper($value['aggregate']) . '(DISTINCT ' . $table . '.' . $value['field'] . ') AS ' . $value['field'] . '_' . $value['aggregate'];
$group_by = ' GROUP BY u.uid';
break;
case 'pgsql':
$join[] = 'LEFT JOIN ( SELECT ' . strtoupper($value['aggregate']) .
'(DISTINCT ' . $table . '.' . $value['field'] . ') AS ' .
$value['field'] . '_' . $value['aggregate'] . ' FROM ' . $table . ' GROUP BY uid ) AS agr_' . $table .
' ON (' . $table . '.uid = u.uid)';
$fields[] = $value['field'] . '_' . $value['aggregate'];
break;
}// end switch
}else
{ $fields[] = $table . '.' . $value['field'];
}// end if
}
//end userlist hook
// Build query
foreach ($_SESSION['user_overview_filter'] as $filter)
{ list($key, $value) = $filter;
// This checks to see if this permission filter is an enabled permission for the authenticated role.
// If so, then all users would be listed, and we can skip adding it to the filter query.
if ($key == 'permission')
{ $account = new stdClass();
$account->uid = 'user_filter';
$account->roles = array(DRUPAL_AUTHENTICATED_RID => 1);
if (user_access($value, $account)) continue;
}
}
//begin userlist hook
//==args
if ($filters[$key]['callback'] && function_exists($filters[$key]['callback']))
{ list($args[],$operator) = call_user_func($filters[$key]['callback'], $value);
}
else
{ $args[] = $value;
}
//==where
if ($filters[$key]['table'] && $filters[$key]['field'] && $filters[$key]['arg'])
{ $table = userlist_get_table_name($filters[$key]['table']);
$field = $filters[$key]['field'];
if (!$operator)
{ $operator = $filters[$key]['operator'] ? $filters[$key]['operator'] : ">";
}
if ($filters[$key]['aggregate'] = userlist_get_aggregate_function($filters[$key]['aggregate']))
{ // format: field_count > %d
$having[] = $field . '_' . $filters[$key]['aggregate'] . " " . $operator . " " . $filters[$key]['arg'];
// format: COUNT(DISTINCT table.field) > %d
$having_count[] = strtoupper($filters[$key]['aggregate']) . '(DISTINCT ' . $table . '.' . $field . ') ' . $operator . " " . $filters[$key]['arg'];
//pop from args
$args_having[] = array_pop($args);
}
else
{// format: table.field > %d
$where[] = $table . '.' . $field . " " . $operator . " " . $filters[$key]['arg'];
}
}
elseif ($filters[$key]['where'])
{ $where[] = $filters[$key]['where'];
}
if($filters[$key]['join'])
{ $join[] = $filters[$key]['join'];
}
//end userlist hook
unset($operator);
}
$fields = implode(', ', array_unique($fields));
$join = count($join) ? ' ' .implode(' ', array_unique($join)) : '';
$where = count($where) ? 'AND ' .implode(' AND ', $where) : '';
$args = array_merge($args, (array)$args_having);
if (count($having)) $having = ' HAVING ' . implode(' AND ', $having);
if (count($having_count)) $having_count = ' HAVING ' . implode(' AND ', $having_count);
return array(
'fields' => $fields,
'join' => $join,
'where' => $where,
'args' => $args,
'having' => $having,
'having_count' => $having_count,
'group_by' => $group_by,
);
}
?>
I do not have a means of testing mysql queries on my system so the query changes are in effect only when Postgresql is in use. In the Mysql or Mysqli case the query is as it was before although most likely the query change will also work for these.
My version of this function also fixes another problem by including these lines:
<?phpif($filters[$key]['join'])
{ $join[] = $filters[$key]['join'];
}
?>
These lines are in effect for all three database types, Postgresql, Mysql and Mysqli. These lines fix the bug that produces this other error when user filtering is in effect:
Please be advised that as of a few days ago I didn't know anything about SQL. I had to learn it to fix this problem. Also I am new to PHP programing and programing Drupal modules. My experience is mostly in C++. So I am not totally confident I got this fix right. All I know for certain is that it made the error messages disappear. Use at your own risk. :)
#2
Thanks for digging.
I was scratching my head on this one as I do not use Postgresql... I'll take a look at what you came up with.