Doesn't work with distinct query on views 2.2
| Project: | Content Access |
| Version: | 6.x-1.0-beta1 |
| Component: | Code |
| Category: | bug report |
| Priority: | critical |
| Assigned: | Unassigned |
| Status: | by design |
| Issue tags: | select distinct as distinct bug |
Jump to:
content_access 6.x-1.0-beta1 seems not working with views 2.2. When I specified a page view to be distinct, I got this query (after I installed the content_access too):
SELECT COUNT(*) FROM (SELECT DISTINCT(node.nid) AS DISTINCT(node.nid), node_data_field_photo.field_photo_fid AS node_data_field_photo_field_photo_fid, node_data_field_photo.field_photo_list AS node_data_field_photo_field_photo_list, node_data_field_photo.field_photo_data AS node_data_field_photo_field_photo_data, node_data_field_photo.nid AS node_data_field_photo_nid, node.type AS node_type, node.title AS node_title, node_data_field_position.field_position_value AS node_data_field_position_field_position_value, node_data_field_position.nid AS node_data_field_position_nid, node_revisions.teaser AS node_revisions_teaser, node_revisions.format AS node_revisions_format FROM node node LEFT JOIN content_field_photo node_data_field_photo ON node.vid = node_data_field_photo.vid LEFT JOIN content_type_testimonial node_data_field_position ON node.vid = node_data_field_position.vid LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid WHERE (node.status <> 0) AND (node.type in ('testimonial')) ORDER BY node_title ASC
The problem is here: DISTINCT(node.nid) AS DISTINCT(node.nid)
All my distinct views got this problem.
#1
Also experienced this problem. Everything works fine until I enable and rebuild the permissions for content access then i get the error;
* 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.created AS node_created FROM node node INNER JOIN' at line 1 query: SELECT COUNT(*) FROM (SELECT DISTINCT(node.nid) AS DISTINCT(node.nid), node.created AS node_created FROM node node INNER JOIN content_field_headline node_data_field_headline ON node.vid = node_data_field_headline.vid WHERE node_data_field_headline.field_headline_value = 'true' ORDER BY node_created DESC ) count_alias in /var/www/drupal/sites/all/modules/views/includes/view.inc on line 699.* 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.created AS node_created FROM node node INNER JOIN' at line 1 query: SELECT DISTINCT(node.nid) AS DISTINCT(node.nid), node.created AS node_created FROM node node INNER JOIN content_field_headline node_data_field_headline ON node.vid = node_data_field_headline.vid WHERE node_data_field_headline.field_headline_value = 'true' ORDER BY node_created DESC LIMIT 0, 10 in /var/www/drupal/sites/all/modules/views/includes/view.inc on line 725.
My thanks to the previous poster for making the connection to distinct queries
#2
check the views issue queue - as this is an issue of views once any node access related module is turned on - there is nothing content access can do about that, as drupal is modifying the queries of views for us.
So I set this issue to "by design".
#3
This is Drupal's fault. I see this in IRC. Someone there give me this link http://drupal.org/node/284392
#4
see this patch: http://drupal.org/node/284392#comment-1049183