Views query not compatible with i18n db_rewrite_sql()

This is i18n's rewrite not being prepared for Views' use of SELECT from (SELECT) which is a brand new feature. Your message indicates you posted in the i18n queue as well; you should reflect that information there so they can adjust their query rewriting to deal with this.

I suspect as Drupal 6 matures, they're going to see a lot more selects with subselects, so they need to be able to cope.

What should i18n be doing to fix this? There's been no progress on that. Also, as the post of this there was only a comment for an issue, not an issue of it's own, I move that this be reopened and moved to that project.

Moving to i18n. Currently produces an error and renders all views unsuable on a site with i18n running.

Problem appears to be i18n_db_rewrite_where(), but I have no idea how to fix it.

more accurate title

Ok, so this is the offending query (From views view.inc, line 500)

SELECT COUNT(*) FROM (SELECT node.nid AS nid, node.sticky AS node_sticky, node.created AS node_created FROM {node} node WHERE ((node.promote <> 0) AND (node.status <> 0))) count_alias

For now I'm just added a preg_match so these queries are not rewritten. It doesn't throw an error anymore, so changed priority to normal, but also language conditions are not applied to these queries so this is still an issue.

However, I see no way to rewrite such a query with db_rewrite_sql and IMHO this needs to be fixed on views module side. This would be the right rewriting code for views.inc. Otherwise I'm afraid all the rewriting modules out there will fail.

$subquery = db_rewrite_sql('SELECT node.nid AS nid, node.sticky AS node_sticky, node.created AS node_created FROM {node} node WHERE ((node.promote <> 0) AND (node.status <> 0))', 'node', 'nid');

$result = db_query('SELECT COUNT(*) FROM ($subquery));

Earl, I'm not switching this to back to views module, I don't want this to look like issue ping-pong :-), but I'd appreciate if you could follow up here at least till we decide on which side we need to patch. Thanks.

core's db_rewrite_sql handles sub-selects, so you may be able to utilize something there to handle it.

My belief is that this is not going to be isolated. Sub-selects are going to get more common, and they will need to be dealt with.

I can't find any place where core handles that.

The fact that views queries work with node_db_rewrite_sql is just because it's not returning any parameter other than 'distinct'.

So far I haven't been able to find any working example of db_rewrite_sql really adding query conditions and subselects. If someone could please point me to one of that...

Core has an utterly complex and ugly regexp in db_rewrite_sql (database.inc) which handles subselects.

subscribing.

@chx, thanks for the info.

@merlinofchaos, as content language is now a core concept, any plan to add it as a filter provided by views module?

I've been looking at the core part and that code is definitely nothing I'd like to get into :-(. Whatever it seems to be buggy as it is not working for this specific case, unless I'm missing some special rule for these subquery rewriting cases.

About i18n/views, the thing is that we don't really need to rewrite such queries at all, as it should be up to the view definition to set up a language filter or not...

Still unsure whether I should file a core bug for these query rewriting cases not working. Whatever, about i18n, this is fixed, such queries just won't be rewritten (unless we really need it for some other query and someone can tell me how to do it)

Thanks

I agree, a view filter for determining language is very much the better choice. I'll need to study the data to see where it is (or if you're familiar with it, a quick primer would make my job easier).

I see a 'language' field, I assume that can easily be correllated to global $language->language, so a "filter to current user's language" filter is easy. What is the translate field? Is that a nid? That may not be something I need to worry about right away.

Sir, here you are the patch for language in views

http://drupal.org/node/238252

(The new version of views looks great btw, I love the whole OO thing :-) )

Question: Do we really have to check for the 'translation' module specifically? It's a core field, I feel uncomfortable even making that check. I don't see any other code that's actually specific to that, and it prevents other modules from utilizing the fields in some manner.

Shouldn't there also be a filter option for "user's current language"?

Otherwise, this is looking like an excellent start. A couple of the function comments show cut & paste issues though =)

That comment is on the wrong issueb. Sorry

I don't think this is actually fixed. With HEAD of both i18n and views current error is:

user warning: Unknown column 'node.language' in 'where clause' query: SELECT COUNT(*) FROM (SELECT node.nid AS nid, node.title AS node_title FROM node node WHERE ((node.type in ('tech'))) ) count_alias WHERE (node.language ='en' OR node.language ='' OR node.language IS NULL) in httpdocs/beijing/sites/default/modules/views/includes/view.inc on line 555.

The non-count query is:

SELECT node.nid AS nid, node.title AS node_title FROM node node WHERE ((node.type in ('tech'))) ORDER BY node_title ASC

On IRC Earl pointed out that views now has a language filter but the problem is that it requires that you explicitly specify a language or expose the filter to the user to select the language. It doesn't honor the user's language or the language specified in the URL.

So it would seem that either:
* views needs to put some kind of marker into the query that i18n can use to know to ignore the query and i18n needs to provide a "smart" language filter that selects the language automagically.
* i18n needs to be smart enough to rewrite the default views queries.

the hackish work around for this is to just to remove the node limit.

sub

i think this is working after all, it turned out that development on i18n was happening in DRUPAL-6 and I was using HEAD.