List translated nodes without duplicates

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

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 @@

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.

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?

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)))" : ")");
      }
      //drupal_set_message($query);
      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:

SELECT   DISTINCT (n.nid),
                  n.sticky,
                  n.created
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

This works for me with 3 languages!

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?

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..

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

I applied the patch and started getting this error:

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.

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.

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.

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

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.

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?
Thanks,
Da.

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"

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.

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"

Please:
- 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

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.

Hi fmosca,

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

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.

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.

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.

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.

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

Hi,

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

Regards,
Antonio