Invalid queries with modr8 module from hook_db_rewrite_sql
webchick - April 28, 2008 - 22:51
| Project: | modr8 |
| Version: | 6.x-1.0-beta2 |
| Component: | Code |
| Category: | bug report |
| Priority: | critical |
| Assigned: | Unassigned |
| Status: | active |
Description
Step 1: Create a View. Let's say "blogs" which displays a list of 'blog' types in descending order, such as the one attached.
Step 2: Log in as a non-administrator user.
Step 3: Access the path /blogs
Step 4: Gasp in horror at the big ugly error!
user warning: Unknown column 'node.nid' in 'on clause' query: SELECT COUNT(*) FROM (SELECT node.nid AS nid, node.created AS node_created FROM node node WHERE node.type in ('blog') ) count_alias LEFT JOIN node n ON node.nid = n.nid WHERE ((n.moderate != 1)) in /home/codename/public_html/sites/all/modules/contrib/views/includes/view.inc on line 605.| Attachment | Size |
|---|---|
| blog.view_.txt | 2.69 KB |
#1
This looks like an error in the use of hook_rewrite_sql or db_rewrite_sql - so either mine or Views.
Did the API changed for db_rewrite_sql()?
#2
try this patch, but I think Views might be violating the normal usage...
#3
Looks like the latest copy of Views fixed this problem. Hooray!
#4
ok - though this patch might still be a good idea.
#5
Hi,
I am getting a similar error with Views 2.2 and modr8 1.0:
user warning: Column 'nid' in field list is ambiguous query: SELECT COUNT(*) FROM (SELECT nid FROM node node LEFT JOIN node n ON node.nid = n.nid WHERE ((n.moderate != 1 OR n.uid = 5)) AND ( (node.status <> 0 OR node.uid = 5 or 0 = 1) AND (node.type in ('video')) )) count_alias in /var/www/drupal-6.9/sites/all/modules/views/includes/view.inc on line 699.
user warning: Column 'nid' in field list is ambiguous query: SELECT nid, node.title AS node_title FROM node node LEFT JOIN node n ON node.nid = n.nid WHERE ((n.moderate != 1 OR n.uid = 5)) AND ( (node.status <> 0 OR node.uid = 5 or 0 = 1) AND (node.type in ('video')) )ORDER BY node_title ASC LIMIT 0, 10 in /var/www/drupal-6.9/sites/all/modules/views/includes/view.inc on line 725.
This is due to the SQL code that modr8 adds in modr8_db_rewrite_sql(). It introduces a second nid field and Views only SELECTs "nid" and not "node.nid".
Should this be reported to the Views module team?
#6
Looks again like this is a views problem.
#7
That JOIN is both not right and not Views.
I do note that I remember something about some field added by some module that added a 'nid' without a table, but Views itself doesn't do this, that I know of. If you've got an unadulterated query where Views does this I'll happily fix it, but I'd need to see a view.
#8
@merlinofchaos - the error is being cause by this part of the query:
SELECT nid FROM {node} nodewhich it seems should beSELECT node.nid FROM {node} nodebut perhaps the bigger problem is that views is using 'node' as the alias for {node} when db_rewrite_sql() expects 'n' as the alias afaik.
#9
I can confirm this is still happening and I don't think it's Views, it modr8 doing something in hook_db_rewrite_sql()
For me, the broken query is:
Message 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 'GROUP BY n.nid HAVING count(n.nid)>='1') ORDER BY n.title' at line 1 query: SELECT DISTINCT n.title, n.nid FROM drupal_node n WHERE ((n.moderate != 1)) AND ( n.type='book' AND n.status = 1 AND n.nid != '228' AND n.nid IN (select n.nid FROM drupal_node n INNER JOIN drupal_term_node tn ON n.vid = tn.vid WHERE tn.tid IN (8) )GROUP BY n.nid HAVING count(n.nid)>='1') ORDER BY n.title in /etc/drupal/all/themes/pixture_reloaded/node-book.tpl.php on line 220.The ((n.moderate != 1)) part is being injected.
Please review, it's clobbering my logs with errors so I need to disable the module. Raising to critical.
Cheers,
Miles
#10
Renaming title.