Hi, I'm getting a postgreSQL error when using views and tac_lite on the default 'frontpage' view. It looks like the part in question is handled by views though. I know how the SQL should look, but I can't after hours of looking figure out where this select statement is getting formed...
Here's the error:
warning: pg_query() [function.pg-query]: Query failed: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list in /usr/share/drupal/includes/database.pgsql.inc on line 84.
user warning: query: SELECT DISTINCT(node.nid) FROM node node INNER JOIN node_access na ON na.nid = node.nid WHERE (na.grant_view >= 1 AND ((na.gid = -1 AND na.realm = 'all') OR (na.gid = 1 AND na.realm = 'tac_lite') OR (na.gid = 0 AND na.realm = 'tac_lite'))) AND (node.promote = '1') AND (node.status = '1') ORDER BY node.sticky DESC, node.created DESC LIMIT 10 OFFSET 0 in /usr/share/drupal/includes/database.pgsql.inc on line 110.
It should have the node.sticky and node.created in the select statement like this snippet:
SELECT DISTINCT(node.nid), node.sticky, node.created FROM ...
Anyone know where the erroring sql is being formed? There is this cool bit of code... but I added a drupal_set_message to it and it never fired.
/*
* Set the base field to be distinct.
*/
function set_distinct() {
if (count($this->fields) && substr($this->fields[0], 0, 7) != 'DISTINCT') {
$field = $this->fields[0];
$this->fields[0] = "DISTINCT($field)";
$this->count_field = "DISTINCT($field)";
}
}
Other ideas? I've gotta get this working.
Thanks!
Jamie.
Comments
Comment #1
JamieR commentedThis isn't just a problem on the frontpage view - it's any view that I add an order_by to... help?
Comment #2
merlinofchaos commentedSigh, postgres.
I think I've figured out how to fix this right, and it's committed to both branches. These fixes will go out in views-4.7.x-1.1 and views-5.x-1.1-beta
Comment #3
JamieR commentedThank you! I look forward to the update.
Comment #4
gordon commentedThis patch has broken the random sort.
Now using the random sort you get something like
SELECT rand() AS _rand() FROM node node ORDER BY rand()
This is wrong.
Comment #5
fagoyes, I've also reported this there
Comment #6
merlinofchaos commentedMarking this back to fixed; the rand() has its own issue above.
Comment #7
(not verified) commented