Views Distinct / Node Access Problems

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_alias
  

Yuck!!

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:

  
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.