Improve query

Hi,

I tested it with MySql only, but I read on the web that it should work with PostGre too.
Anyway it would be better to test that :)

In the proposed query the original translation case is not handled but it shouldn't be too difficult to add it.

Regards,
Antonio

Hi Anselm,

if you haven't looked at this yet I could try to do it myself and send a patch.

Regards,
Antonio

Ok,

if I understood the drupal scheme right and the original version comes always first we could handle the original language case implicitly in the ELSE part of the CASE, something like this (in MySQL):

set @current_language='it';
set @default_language='en';

set @curr_lang_priority = 1;
set @def_lang_priority = 2;
set @orig_lang_priority = 3;

SELECT nid,tnid, language, title
  FROM node AS n1
  WHERE (n1.promote <> 0) AND (n1.status <> 0) AND (
    tnid IS NULL OR tnid=0 OR nid=(
      SELECT nid FROM node AS n2
        WHERE n2.tnid=n1.tnid
        ORDER BY
          CASE n2.language
            WHEN @current_language THEN @curr_lang_priority
            WHEN @default_language THEN @def_lang_priority
            ELSE @orig_lang_priority
          END
        LIMIT 1
    )
  )
  ORDER BY n1.sticky DESC, n1.created DESC;

Patch attached which implements this idea in Select Translation.

Needs work and testing :)

Regards,
Antonio

The patch exposes this bug #369629: db_rewrite_sql breaks on sql having "order by" clause so it is not fully usable for now.

The current version of select_translation uses uncorrelated subqueries, but using the CASE syntax could still be prettier if it's comparable in terms of performance.

For now I am postponing this issue, but if someone has a test case with a lot of nodes please get in touch.

Thanks,
Antonio