On a multilingual site, I'm trying to create a view on nodes where the content should be either in the current language, or in English if no translation exists in the current language. After trying to solve this with the Views web interface for a while, I figure that what is provided for filtering is simply not sufficient.

So I'm thinking of adding module code, to extend Views to support this particular use case.

Starting from then end result, this is what the constructed query should look like:

select node.* from {node} node  -- provided by Views
-- join: 
left join {node} tnode on node.tnid = tnode.tnid and tnode.tnid > 0 and tnode.language = ***CURRENT_LANGUAGE***
-- condition:
where (node.language = ***CURRENT_LANGUAGE*** or node.language='en' and tnode.nid is null)

I'm assuming that ***CURRENT_LANGUAGE*** is a macro that will be replaced by the actual value of the current language; 'en' might be an argument, chosen in the admin interface, but that is not really important.

But now I'm stuck on how to actually code this up for Views.

Any help, including alternative approaches, welcome.

  • You know your qyery would NOT do what your title says? If a node have both current language and english one, both translations will be returned.
    – Mołot
    Oct 21 '13 at 12:32
  • No, because if the local translation exists, then tnode.nid will not be null, and the 'en' version will be suppressed.
    – bart
    Oct 22 '13 at 12:59
  • My title, which may not be the clearest title imaginable, originates from the frustration that Views together with Trnalsation provides 2 means to only select nodes with translations, either by producing an inner join, or by requiring in a filter that the joined row is not empty, but no means to filter that it must be empty. In addition, query conditions in Views are limited to 2 levels, and I need 3. Lots of frustrations with this fancy but extremely limited interface.
    – bart
    Oct 22 '13 at 13:05

You can use this function

function MY_MODULE_views_query_alter(&$view, &$query) {
    if($view->name == 'view_name'){


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.