Posted by mrjavum on May 5, 2009 at 11:45am
4 followers
Jump to:
| Project: | Activity |
| Version: | 6.x-2.x-dev |
| Component: | Miscellaneous |
| Category: | task |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | active |
Issue Summary
I tried to add filter
Activity Access: flag_friend Access
Specific Relationships: Friend
to default activity view and got an 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 ') AND ((activity_access.realm = 'flag_friend' AND activity_access.value IN ' at line 10 query: SELECT COUNT(*) FROM (SELECT DISTINCT(activity.aid) AS aid, COALESCE(activity_personal_messages.message, activity_messages.message) AS activity_messages_message, activity.created AS activity_created FROM activity activity INNER JOIN activity_access activity_access ON activity.aid = activity_access.aid INNER JOIN activity_targets activity_targets ON activity.aid = activity_targets.aid AND activity_targets.uid = 0 INNER JOIN activity_messages activity_messages ON activity_targets.amid = activity_messages.amid LEFT JOIN activity_targets activity_personal_targets ON activity.aid = activity_personal_targets.aid AND activity_personal_targets.uid = 1 LEFT JOIN activity_messages activity_personal_messages ON activity_personal_targets.amid = activity_personal_messages.amid WHERE () AND ((activity_access.realm = 'flag_friend' AND activity_access.value IN (3))) ORDER BY activity_created DESC ) count_alias in Y:\home\jjj\www\sites\all\modules\views\includes\view.inc on line 705.
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 ') AND ((activity_access.realm = 'flag_friend' AND activity_access.value IN ' at line 10 query: SELECT DISTINCT(activity.aid) AS aid, COALESCE(activity_personal_messages.message, activity_messages.message) AS activity_messages_message, activity.created AS activity_created FROM activity activity INNER JOIN activity_access activity_access ON activity.aid = activity_access.aid INNER JOIN activity_targets activity_targets ON activity.aid = activity_targets.aid AND activity_targets.uid = 0 INNER JOIN activity_messages activity_messages ON activity_targets.amid = activity_messages.amid LEFT JOIN activity_targets activity_personal_targets ON activity.aid = activity_personal_targets.aid AND activity_personal_targets.uid = 1 LEFT JOIN activity_messages activity_personal_messages ON activity_personal_targets.amid = activity_personal_messages.amid WHERE () AND ((activity_access.realm = 'flag_friend' AND activity_access.value IN (3))) ORDER BY activity_created DESC LIMIT 0, 25 in Y:\home\jjj\www\sites\all\modules\views\includes\view.inc on line 731.and view displays nothing.
What is my mistake?
Comments
#1
ahh im sorry, i keep forgetting we have to patch Views. #436120: add_where_group() assumes that there already exists a $where in the query object I need to find a better way todo this. Maybe just artificially add a where 1=1 until this gets into Views
#2
Should be fixed here I hope. http://drupal.org/cvs?commit=207182
#3
Big thanks - it works!!!
But only for admin... For others I've got such errors:
* 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(activity.aid), COALESCE(activity_personal_messages.message, activity' at line 1 query: SELECT COUNT(*) FROM (SELECT DISTINCT(activity.aid) AS DISTINCT(activity.aid), COALESCE(activity_personal_messages.message, activity_messages.message) AS activity_messages_message, users.picture AS users_picture, users.uid AS users_uid, users.name AS users_name, activity.created AS activity_created FROM activity activity LEFT JOIN users users ON activity.uid = users.uid LEFT JOIN flag_content flag_content_users ON users.uid = flag_content_users.uid AND (flag_content_users.fid = 2 AND flag_content_users.uid = 3) INNER JOIN activity_access activity_access ON activity.aid = activity_access.aid INNER JOIN activity_targets activity_targets ON activity.aid = activity_targets.aid AND activity_targets.uid = 0 INNER JOIN activity_messages activity_messages ON activity_targets.amid = activity_messages.amid LEFT JOIN activity_targets activity_personal_targets ON activity.aid = activity_personal_targets.aid AND activity_personal_targets.uid = 3 LEFT JOIN activity_messages activity_personal_messages ON activity_personal_targets.amid = activity_personal_messages.amid LEFT JOIN node_access na ON na.nid = activity.nid AND (activity.type = 'node' OR activity.type = 'comment') WHERE ((activity.nid IS NULL OR (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 1 AND na.realm = 'flag_friend'))))) AND ( (activity_access.realm = 'flag_friend' AND activity_access.value IN (1)) )ORDER BY activity_created DESC ) count_alias in Z:\home\JJJ\www\sites\all\modules\views\includes\view.inc on line 705.* 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(activity.aid), COALESCE(activity_personal_messages.message, activity' at line 1 query: SELECT DISTINCT(activity.aid) AS DISTINCT(activity.aid), COALESCE(activity_personal_messages.message, activity_messages.message) AS activity_messages_message, users.picture AS users_picture, users.uid AS users_uid, users.name AS users_name, activity.created AS activity_created FROM activity activity LEFT JOIN users users ON activity.uid = users.uid LEFT JOIN flag_content flag_content_users ON users.uid = flag_content_users.uid AND (flag_content_users.fid = 2 AND flag_content_users.uid = 3) INNER JOIN activity_access activity_access ON activity.aid = activity_access.aid INNER JOIN activity_targets activity_targets ON activity.aid = activity_targets.aid AND activity_targets.uid = 0 INNER JOIN activity_messages activity_messages ON activity_targets.amid = activity_messages.amid LEFT JOIN activity_targets activity_personal_targets ON activity.aid = activity_personal_targets.aid AND activity_personal_targets.uid = 3 LEFT JOIN activity_messages activity_personal_messages ON activity_personal_targets.amid = activity_personal_messages.amid LEFT JOIN node_access na ON na.nid = activity.nid AND (activity.type = 'node' OR activity.type = 'comment') WHERE ((activity.nid IS NULL OR (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 1 AND na.realm = 'flag_friend'))))) AND ( (activity_access.realm = 'flag_friend' AND activity_access.value IN (1)) )ORDER BY activity_created DESC LIMIT 0, 25 in Z:\home\JJJ\www\sites\all\modules\views\includes\view.inc on line 731.
But I fixed it with this patch:
http://drupal.org/node/284392#comment-1049183
Now it fully works!!!
#4
I guess we need to find a way to work around this then... hrm.
#5
bumping with a workaround plan
use the views_handler_filter_node_access handler for this. Just need to describe the join from node_access to the activity table.
#6
Per comment #3, here are a few updates in the 2+ years since this was opened.
#436120: add_where_group() assumes that there already exists a $where in the query object has been fixed in #419270: set_where_group fails when no where arguments added.
#284392: db_rewrite_sql causing issues with DISTINCT, specifically the latest comment, has forked into #735114: Fix mistake in database.inc comment which was fixed and #735120: Fix changes to db_distinct_field() in D6.16 / PostgreSQL which is still open. The latter issue appears to apply only to postgresql.
In all cases, it seems like this issue at this point in 2011 is going to be reliant upon one of those upstream fixes, which I would suggest is not terribly likely to make it into core given the momentum of Drupal 7. Questions:
1) Is this still a problem for anyone, with the latest version of Activity, views, and Drupal 6?
2) If not, can we close this as 'won't fix' unless someone else starts to see this and re-opens it with more information?