While trying to use some of the pager_query examples that show up on drupal.org, I ran into this problem with db_rewrite_sql.

I have a query with several subqueries, something like

SELECT a,b,c,
(SELECT d FROM other_table WHERE d.e=table.a LIMIT 1) as f,
(SELECT...) as g,
(SELECT...) as h,
(SELECT...) as i,
(SELECT...) as j
FROM table,table2
LEFT JOIN table3...
WHERE ...
ORDER BY ...

I am trying to use this as the SQL for a call to pager_query, and started out by calling pager_query(db_rewrite_sql($sql), 10, $countSQL, $printflikearguments). However, I got syntax errors, and the SQL that's eventually being passed to the database has this:

(n.language ='en' or n.language = '') AND

added to the where clause of each one of the subselects. The 'n' table doesn't show up in any of the subselects, so the SQL isn't valid and fails. After doing a 'grep' through the i18n code for places where it might be replacing WHERE with something else, I ran into i18n_db_node_rewrite, which looks like it might be the one doing this. I think it's safe to just get rid of the db_rewrite_sql() call in my code. I'm going to try that out presently, but I figured I should report the bug.

Comments

chx’s picture

chx commented
Status: Active » Closed (won't fix)

To be more exact, can't fix. Have you read http://drupaldocs.org/api/4.6/function/db_rewrite_sql ? You need to specificy db_rewrite_sql he primary table and the primary field. Typically this is n for node and nid, hence these are the defaults. For example, if other_table has nid, then db_rewrite_sql($sql, 'other_table'). Note: you should use {} for table names, but I guess you do.

Wesley Tanaka’s picture

Wesley Tanaka commented
Status: Closed (won't fix) » Active

The issue is with pager_query, not with db_rewrite_sql.

I'm paging data from tables that are completely outside of drupal (but are being joined with {users}. Due to a bug in the i18n module, db_rewrite_sql breaks my query, so I don't use it. It would be nice to be able to use drupal's paging UI to avoid using my own paging code (which I was using before I switched to drupal) in this situation. However switching has reduced the efficiency of that page because of the extra count query.

Wesley Tanaka’s picture

Wesley Tanaka commented

Oops. wrong bug.

The problem is occurring because the WHERE terms get applied to all subselects, but the LEFT JOIN only gets added to the main query. Couldn't this be made more consistent either by not adding the WHERE clause to subqueries, or making sure that the LEFT JOIN gets applied to all sub queries?

jose reyero’s picture

jose reyero commented
Status: Active » Closed (won't fix)

Please don't fill in 'critical' bugs for problems that don't even exist yet. I mean, this is for something not working with 'actual' Drupal or some other 'real' contrib modules.

Thanks,

Wesley Tanaka’s picture

Wesley Tanaka commented
Priority: Critical » Minor
Status: Closed (won't fix) » Active

It means that I can't use the pager API as described in the documentation (with db_rewrite_sql call).

There is a workaround (do not call db_rewrite_sql), so it makes sense that the priority shouldn't be high. Or are you saying that is the actual fix (e.g. am I not supposed to call db_rewrite_sql if I'm not part of 'actual' Drupal or some other 'real' contrib modules?)

jose reyero’s picture

jose reyero commented
Status: Active » Closed (won't fix)
Wesley Tanaka’s picture

Wesley Tanaka commented
Status: Closed (won't fix) » Active

?

chx’s picture

chx commented
Status: Active » Closed (won't fix)

Let's make this official: I will not fix this (and likely noone else will -- but I have written the db_rewrite_sql mechanism, so it'd be mostly my task) because currently subselects are not used in Drupal. We will cross that river when we are there. You are on your own if you use them. If you cook up a better regexp, then it is fine, but I do not deem this as a bug in Drupal.