Converting a left join query with empty right side to Views 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 &gt; 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 can use this function

function MY_MODULE_views_query_alter(&amp;$view, &amp;$query) {
    if($view-&gt;name == 'view_name'){
       }
}


https://api.drupal.org/api/views/views.api.php/function/hook_views_query_alter/7