Lullabot Ideas
We know stuff. We empower you to know stuff too.
Views Distinct / Node Access Problems
Blog by Karen StevensonJune 19, 2009 - 7:26am
I've been battling a core bug that creates problems when you use node access systems like Organic Groups and try to create views that are limited to distinct nodes. When you are using a node access system and you set 'distinct' to 'true' in any node view, you get ugly ugly error messages like:
user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISTINCT(node.nid), node_data_field_date.field_date_value AS node_data_field_' at line 1 query: SELECT COUNT(*) FROM (SELECT DISTINCT(node.nid) AS DISTINCT(node.nid), node_data_field_date.field_date_value AS node_data_field_date_field_date_value FROM node node LEFT JOIN term_node term_node ON node.vid = term_node.vid INNER JOIN term_data term_data ON term_node.tid = term_data.tid LEFT JOIN content_field_date node_data_field_date ON node.vid = node_data_field_date.vid WHERE (node.status <> 0) AND (node.type in ('event')) AND (term_data.name = 'children') ORDER BY node_data_field_date_field_date_value ASC ) count_aliasYuck!!
This is actually a core bug, see http://drupal.org/node/284392. Core's db_rewrite_sql() will rewrite the query from DISTINCT(node.nid) AS nid to an incorrect query of DISTINCT(node.nid) AS DISTINCT(node.nid). This invalid query will cause a fatal error keeping the query from executing.
I've been looking for a way to work around this problem until core gets fixed without either hacking core or hacking Views, and I finally found a way to do it using the Views hook_views_pre_execute(). The code snippet I add to this hook will replace the problem code in the Views query just before it gets sent to db_rewrite_sql() with a value that db_rewrite_sql() can handle properly. The core function will then rewrite our replaced text, nid AS nid, back to the correct value of DISTINCT(node.nid) AS nid in the final query.
You have to implement this from a module, but I nearly always create a custom module for snippets like this. To my custom module I add the following function:
<?php
function MODULENAME_views_pre_execute(&$view) {
$replace = array('DISTINCT(node.nid) AS nid' => 'nid AS nid');
$view->build_info['query'] = strtr($view->build_info['query'], $replace);
$view->build_info['count_query'] = strtr($view->build_info['count_query'], $replace);
}
?>No patches to maintain for core. No patches to keep up in Views. I just have to remove this when the core bug gets fixed.
I encourage everyone to participate in the core issue, http://drupal.org/node/284392, and get it corrected for once and for all. In the meantime, this technique is a workaround that can be used on production sites that need it.
UPDATE
There is a patch that will hopefully be going into Views to work around this bug. See http://drupal.org/node/501552. If that patch gets in, using a patched version of Views will eliminate any need for this trick. We still need to get the core bug fixed, tho, so please keep that effort moving forward.
Comments
Yes!
Solutions are always staring us in the face. The genius is in the seeing!
Thanks for posting this!
Thanks for posting this!
Whew!
Thanks for pointing to the location of that bug. I had assumed it was a bug in Views and that I'd be left unable to create a View that depended on SELECT DISTINCT.
That workaround will come in might handy! Thanks!!
how to create a custom module?
Thank you for the efforts on this, Karen. This problem affects TWO of my sites.
Can you provide a dummy-proof way of implementing what you describe here? I don't know how to create a module, and applying a patch (#24 in the thread) is a little scary to me.
Can you make your new "Patch Module" available?
----------------------
Also, I don't want to muddy the waters, but can I muddy the waters?
Everyone keeps saying the issue is related to node access modules. So on my most critical site (launching in a couple days!) I disabled the "nodeaccess" module. I found that "Distinct = Yes" can be enabled without the error! "Yay!"
or not
"Distinct = Yes" doesn't do anything to the view. The duplicates are still there. I tried also adding the filter "Taxonomy: Term ID (with depth)" and selected "reduce duplicates, but that doesn't do anything either.
All I'm trying to do is display a list of taxonomy terms (like you see on any blog site) for all nodes of a certain type. That's it. I'm using a view type of "Node", though I've tried using the others.
So I'm wondering if the problems persist even after I disable a node access module? But perhaps the errors just go away?
I'm happy to do any testing for you if you explain to me how.
DISTINCT field
I have:
Latest Drupal 6
Views 6.x-2.x-dev 2009-Jun-26
Workflow 6.x-1.1
I had the SQL error when I would try and activate the Distinct, and then I updated to the latest dev View and now I get no error, but the duplicates are still there.
Here's the tiny custom module
For those who don't want to roll their own, here's a completely untested little module. We're probably going to need this soon but haven't yet had time to test it out.
- Marc
Module seems to work.
Nice module - thanks for adding it. Trying it and it seems to work for me so far.
If after using this fix you
If after using this fix you get an error like this:
user warning: Column 'nid' in field list is ambiguous query: SELECT nid AS nid, node.title AS node_title,...
Try this:
$replace = array('DISTINCT(node.nid) AS nid' => 'node.nid AS nid');
Updated version of the module
We finally got around to trying out the custom module and it didn't work for us. Version 6.x-0.2 is working in our limited test environment.
I'm not sure how the previous version(s) were working for anyone -- strtr() always takes its replacement strings in from/to order.
Help push the core issue
Just want to point out that the module should be considered a temporary "fix" and that anyone who gets here should consider helping out by testing the most recent patch submitted for this issue at http://drupal.org/node/284392 -- this needs to get fixed in core.