The "content selection" option is quite limited, since it doesn't take into account the translations of the same node.
For instance, suppose I have a list of news items, some in Italian, some in English, and some in both languages, and that the default language of my site is English.
If I set the "content selection mode" to "Only current and default languages and no language" an italian user browsing the list of news will find the same item A twice, once in English and once in Italian (if both translations of A exist). I would expect him to see only the italian version.
The default version should only be used as a fallback, in case the same content does not exist in the preferred version.

I looked at the drupal code, and it seems to me that such an option is quite hard to implement. But if someone has any hints, I'm willing to try working on it.


daneel’s picture

I have precisely the same need... but no idea how to implement it!

mardy78’s picture

Status: Active » Needs work
1.89 KB

Hello, at last I found a solution (please see the attached patch).

Using a subquery, we can exclude duplicate translation nodes. The attached patch works for me; I'm not sure how taxonomy should be handled, though.

After applying the patch, you should go to admin/settings/i18n and enable the option "All content, but remove duplicate translations favouring current language, then default language" from the advanced settings.

Feedback welcome!

daneel’s picture

Could you please provide your modified i18n.module and i18n.inc files? Because there seems to be a problem with your patch file: I copied it over to the modules/i18n directory and ran "patch -p0

patch: **** malformed patch at line 18: @@ -80,6 +116,10 @@

Bodo Maass’s picture

Title: Display content in default language only if it is not available in preferred language » Table prefix

Awesome! This is exactly what I needed. Good work.
Just a small point: The query needs to enclose the table name in curly braces to support table prefixes, so the affected line should be changed from this:

SELECT * FROM `i18n_node`

into this:

SELECT * FROM {i18n_node}

Thanks for sharing.

Bodo Maass’s picture

Title: Table prefix » List translated nodes without duplicates

I realized that this patch is not quite complete. Let's say I have three languages, en, de and cz. If I view the list in en (my default language) and there is content that is translated in de and cz, but not in en, it will not be shown.
This can be generalized into saying that the query will completely skip nodes that have a translation, but not in the preferred or default language. I'm just working on a fix for that, but I'm afraid it will make the query a bit more complicated. How often does this get executed?

Bodo Maass’s picture

Status: Needs work » Needs review
14.67 KB

Ok, I changed the query to include translated items whose translations are neither in the current nor in the default language, without including duplicates. Translations of the current item are searched in the following order: current language, default language, and then in alphabetical order of the language prefixes. I guess eventually there could be a per user setting for the order of preferred languages.

To update the query, replace the query in i18n.inc with the following:

    case 'mixed_single':
      $pref_lang = i18n_get_lang();
      $def_lang = i18n_default_language();
      $exclude = array_unique(array($pref_lang, $def_lang));
      $languages = array_diff( array_keys(i18n_supported_languages()), $exclude );

      $query = "$alias.trid = 0
      OR $alias.trid IS NULL
      OR $alias.language ='$pref_lang'";

      if ($pref_lang != $def_lang)
       $query .= " OR ($alias.language = '$def_lang' AND NOT EXISTS (SELECT * FROM {i18n_node} as i18nrw WHERE i18nrw.trid = $alias.trid AND i18nrw.language='$pref_lang'))";

      $last = end($exclude);
      while (($lang = array_shift($languages)) && $languages) {
       $condition = "";

       if ($exclude) {
         foreach($exclude as $lang2) {
          $condition .= "i18nrw.language='$lang2'" .
             ($lang2!=$last ? " OR " : '');
       $exclude[] = $lang;
       $last = $lang;

       $query .= "OR ($alias.language = '$lang'" . ($condition ? " AND NOT EXISTS (SELECT * FROM {i18n_node} as i18nrw WHERE i18nrw.trid = $alias.trid AND ($condition)))" : ")");
      return $query;
    case 'strict':

Here is an example of the kind of queries this generates. In this example there are four languages: ar, cz, de, and en. en is the default, and de is the current language:

FROM     node n
         LEFT JOIN i18n_node i18n
           ON n.nid = i18n.nid
WHERE    (i18n.trid = 0
           OR i18n.trid IS NULL 
           OR i18n.LANGUAGE = 'de'
           OR (i18n.LANGUAGE = 'en'
               AND NOT EXISTS (SELECT *
                               FROM   i18n_node AS i18nrw
                               WHERE  i18nrw.trid = i18n.trid
                                      AND i18nrw.LANGUAGE = 'de'))
           OR (i18n.LANGUAGE = 'ar'
               AND NOT EXISTS (SELECT *
                               FROM   i18n_node AS i18nrw
                               WHERE  i18nrw.trid = i18n.trid
                                      AND (i18nrw.LANGUAGE = 'de'
                                            OR i18nrw.LANGUAGE = 'en'))))
         AND n.promote = 1
         AND n.status = 1
ORDER BY n.sticky DESC,
         n.created DESC
trogie’s picture

This works for me with 3 languages!

hedac’s picture

It's great! this works for me in the main page

but not when listing taxonomy terms.. for example...
I have term 21: website updates (setup as english in the term)
and term 22: actualizaciones web (spanish term)
I create the translation relations of terms in the categories settings terms translations.

if I go to /es/taxonomy/term/22 is ok, spanish nodes listing teaser views,
then I click english language switch... and it goes to /en/taxonomy/term/21... ok.. but only english nodes are listed. I wanted to list them nodes without translations also as it happens in the home page listings latest nodes by default

any idea?

PD: I think many problems of i18n module come from the concept of duplicating nodes for each translation... maybe it would be better to add the language variations in the same node as a extended data row or something... in that way.. it is more logical... it is only one node.. the language is only a variation of the same node... the same reference for all the database... just render in different languages...

another problem I see.. is that if you add comments to a node... you don't have those comments in the translated version.. again. because they are different nodes...

also there are problems of duplicated entries with Views module... because views module doesn't know that relations of nodes... if there were only 1 node for each node.... wouldn't be easier for everybody?

hedac’s picture

Well... have it working now... seeing all languages without duplicates choosing the current language first if available. Great thank you.

But now in taxonomy_menu module shows duplicates of terms in every language. (only if current language is different from default site language)... maybe should post an issue in taxonomy_menu module issues... but since it has to be a patch due to a patch here... not sure..

hedac’s picture

I don't know why but taxonomy_menu works fine now.. I don't remember changing anything...

pdb’s picture

I applied the patch and started getting this error:

pdb’s picture

I don't know where the error text went on the previous post....

user warning: Unknown column 'v.trid' in 'where clause' query: SELECT v.* FROM vocabulary v INNER JOIN vocabulary_node_types n ON v.vid = n.vid WHERE (v.trid = 0 OR v.trid IS NULL OR v.language ='pt' OR (v.language = 'en' AND NOT EXISTS (SELECT * FROM `i18n_node` as i18nrw WHERE i18nrw.trid = v.trid AND i18nrw.language='pt') )) AND n.type = 'story' ORDER BY v.weight, v.name in /var/www/html/drupal47/includes/database.mysql.inc on line 120.
Bodo Maass’s picture

You are right, this doesn't seem to work with taxonomy terms. Someone needs to look at the final sql query after it has been rewritten by i18n to find out what exactly is wrong. I might take a look, but it's unlikely to happen very soon, as I'm not using taxonomy on the site for which I wrote this patch.

Moxide’s picture

Hi !

Correct me if I 'm wrong, but some parts of your query (the 'AND NOT EXISTS' parts) shouldn't be generated if $alias is a vocabulary table, since vocabulary table doesn't have a 'trid' column (the reason of the SQL error).

Your patch should work with terms, though.

Jose Reyero’s picture

Version: 4.7.x-1.x-dev » master
Status: Needs review » Needs work

I like the idea and the approach.

But the patch needs to take care of taxonomy rewrites so it needs some polishing. Also the description on the settings page is not clear enough IMHO.

Another issue may be some mysql compatibiity, as older versions don't support subqueries.

Willing to include this feature in the next version though

mardy78’s picture

I refactored bodomaass's code in a IMHO simpler and faster way. This assumes that the default language is the first element of the array of the supported languages returned by i18n_supported_languages(). I still have to look into the taxonomy issue.

    case 'mixed_single':
      $pref_lang = i18n_get_lang();
      $supported = array_diff(array_keys(i18n_supported_languages()), array($pref_lang));

      $query = "$alias.trid = 0 OR $alias.trid IS NULL OR $alias.language ='$pref_lang'";
      $exclude = array("'$pref_lang'");
      foreach ($supported as $lang) {
        $query .= " OR ($alias.language = '$lang' AND NOT EXISTS (SELECT * FROM `i18n_node` as i18nrw WHERE i18nrw.trid = $alias.trid AND i18nrw.language IN (" . implode(",", $exclude) . ")))";
        $exclude[] = "'$lang'";
      return $query;

This code leads exactly to the same query, except that instead of using the "a = b OR a = c OR a = d" construct it's using "a in (b,c,d)", which is cleaner and hopefully more efficient.

pdb’s picture

Status: Needs work » Fixed

The original issue is resolved in the current release of the module; the option for "Only current and default languages and no language" appears to work correctly.

Anonymous’s picture

Status: Fixed » Closed (fixed)
mardy78’s picture

Status: Closed (fixed) » Needs review

Reopening, this is not fixed at all. Current code is:

    case 'mixed':
      return "$alias.language ='".i18n_get_lang()."' OR $alias.language ='".i18n_default_language()."' OR $alias.language ='' OR $alias.language IS NULL" ;

But if there are more versions of the node, one with language set to i18n_get_lang() and one to i18n_default_language(), they will both be returned. Please consider the code listed in comment #16.

planctus’s picture

Hello, i'm just getting a little bit confused:
I'm trying to find an easy and logical way to filter nodes in views in bilingual system to avoid duplication of the translated node, but showing one content even if not in the current linguistic environment.

So i went into this after a long search. I fond this patch but then i saw that the problem was solved by the i18n.module

I tried to use the suggested option in the multilingual admin but i still see duplicated nodes.

So, the last posts reopened the issue and suggested the code at #16.
But then, where to apply that code?
Is it a snippet belonging to the patch downlodable or is it a patch in itself?

And more, i couldn't find an i18n.inc (i'm using drupal 5.3 and the last version of the module i mean) so i'm really unable to find a solution for this issue.

Does anyone know how to rationalize this issue giving me and other users a clarification about how to solve the problem?

mr.j’s picture

Another vote for this fix. This is still a problem release 5.x-2.2

The option really should be:
"Only current OR default language and no language"

instead of:
"Only current and default language and no language"

meible’s picture

Hi Mr. J.

I continue here. I feel very much like planctus. What exactly is the fix now and where do we have to plug it in.
Thanks a lot.

geohelper’s picture

This is a critical flaw! After building out a new site and ready to launch, we realized that this makes our translation feature useless.

I think #21 is correct in saying it needs to be "Only current OR default language and no language"

Jose Reyero’s picture

Version: master » 5.x-2.x-dev
Status: Needs review » Needs work

- see my comments in #16 which haven't been addressed
- post actual patches which are easier -at least for me- to review and apply
- besides working, needs to take care of the options page, with the right wording

fmosca’s picture

My attempt:
This patch adds the "mixed single" mode as previous did, but this actually works for me on node listings. It doesn't take care of taxonomies, that will keep working as in "mixed" mode.
In brief:
- If current language == default language: every node in current language and nodes with no language
- If current language != default language: every node in current language, nodes in def. lang. not available in current language, nodes with no language.
I'm testing it right now, but more eyeballs are welcome. I've no idea if it will work with postgresql, as it exploits mysql NULL values handling.
It should be way faster than using subqueries. Adding indexes for i18n_node.language and i18n_node.trid helps a lot.

dagomar’s picture

Hi fmosca,

thanks for this patch; it works for me so far!

Junesun’s picture

For those who would like to list all content regardless of language but show the best possible match (either current language or default language) if there is one, here is the key:

// If language conditions already there, get out
if (preg_match("/i18n/", $query)) return;
if ($mode == "conditionalall") {
$cur_lang = i18n_get_lang();
$def_lang = i18n_default_language();
$alias = 'i18n';
$result['join'] = "LEFT JOIN {i18n_node} i18n ON $primary_table.nid = i18n.nid";
$result['where'] = "($alias.language ='".$cur_lang."')";
$result['where'] .= " OR ($alias.language ='".$def_lang."' AND NOT EXISTS (SELECT * FROM `i18n_node` as i18nrw WHERE i18nrw.trid = $alias.trid AND i18nrw.language = '$cur_lang'))";
$result['where'] .= " OR (1 AND NOT EXISTS (SELECT * FROM `i18n_node` as i18nrw WHERE i18nrw.trid = $alias.trid AND (i18nrw.language = '$cur_lang' OR i18nrw.language = '$def_lang')))";
} else {
$result['join'] = "LEFT JOIN {i18n_node} i18n ON $primary_table.nid = i18n.nid";
$result['where'] = i18n_db_rewrite_where('i18n', $mode);
return $result;

Again, this does not apply to taxonomies, and I'd be really grateful if somebody could show me how to have native-language terms (if available through translation) show up for a foreign-language node.

sdecabooter’s picture

I installed #25 by fmosca and seems to be working like a charm (on Drupal 5.7)
The patch no longer seems to apply, but i did the changes manually and encountered no problems so far.

ekes’s picture

I too have used #25 patch; generally happy although I have had to remove it from some views on a site with 30,000 nodes as these queries were on average 10x slower than the equivalent queries with the standard 'own, default, no' query.

ekes’s picture

Further to 29 alter table i18n_node add index nid_trid_lang(language, trid, nid) significantly improved performance here, but in some cases the queries created are a block to it working practically with 10,000+ nodes.

Jose Reyero’s picture

Status: Needs work » Fixed

Implemented into 5.x-3- version, see http://drupal.org/node/203798

Anonymous’s picture

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for two weeks with no activity.

ao2’s picture


a port of the patch in #2 to i18n-6.x-1.0 can be found in #429996: Content selection: request about mixed mode..