0

Abstract:

I have a SELECT query with a WHERE condition which I would like to have met. In some cases this WHERE condition will not be met and in such cases I would like to use a different WHERE condition. That is the abstract problem I'm facing. Here is a more specific example:

Example:

I have a tag table and a tag_l11n table. The tag table contains basic information about a tag and the tag_l11n table contains the localized name of the tag. In the following simplified SELECT I'm requesting the tag with an English name (tag_l11n_language = 'en')

Query:

SELECT 
    `tag_3`.`tag_id` as tag_id_3,
    `tag_l11n_2`.`tag_l11n_title` as tag_l11n_title_2
FROM 
    `tag` as tag_3 
LEFT JOIN 
    `tag_l11n` as tag_l11n_2 ON `tag_3`.`tag_id` = `tag_l11n_2`.`tag_l11n_tag` 
WHERE 
    `tag_l11n_2`.`tag_l11n_language` = 'en' 
ORDER BY 
    `tag_3`.`tag_id` ASC 
LIMIT 25;

Problem:

The problem starts if a tag doesn't have a certain translation. For example a tag may exist in English language but not in e.g. Italian language. However, the Italian guy would also accept the tags in English language (or any other language) IFF (if and only if) the Italian translation doesn't exist.

In the end I would prefer a solution where I could specify different priorities (1. localization of the user, 2. English, 3. any other language).

I'm a little bit at a loss here. While I could easily omit the condition (language = ??) and filter the result during the output/presentation, I don't think this is the way to go.

Improve this question
4
  • provide sample data and expected output in tabular form. Commented Jun 7, 2020 at 8:36
  • In other words - if the above query with the condition tag_l11n_2`.`tag_l11n_language` = 'en' return no rows, then do another - almost the same query but with condition tag_l11n_2.tag_l11n_language <> 'en'``, is that correct ? Commented Jun 7, 2020 at 8:37
  • See meta.stackoverflow.com/questions/333952/… Commented Jun 7, 2020 at 8:38
  • @krokodilko correct Commented Jun 7, 2020 at 8:40

4 Answers 4

Reset to default
4

You could add a clause to your WHERE which will return a translation in English if the Italian translation didn't exist, using a NOT EXISTS clause:

WHERE 
    `tag_l11n_2`.`tag_l11n_language` = 'it' 
OR
    `tag_l11n_2`.`tag_l11n_language` = 'en'
    AND NOT EXISTS (SELECT *
                    FROM tag_l11n t3 
                    WHERE t3.tag_l11n_tag = tag_3.tag_id 
                      AND t3.tag_l11n_language = 'it')

Another (possibly more performant given it will not have an OR condition) solution would be to LEFT JOIN to tag_l11n_2 twice, once for the desired language, and once for the backup, and using COALESCE to prioritise the desired languages result:

SELECT 
    `tag_3`.`tag_id` as tag_id_3,
    COALESCE(`tag_l11n_2`.`tag_l11n_title`, `tag_l11n_3`.`tag_l11n_title`) as tag_l11n_title_2
FROM 
    `tag` as tag_3 
LEFT JOIN 
    `tag_l11n` as tag_l11n_2 ON `tag_3`.`tag_id` = `tag_l11n_2`.`tag_l11n_tag` 
                            AND `tag_l11n_2`.`tag_l11n_language` = 'it' 
LEFT JOIN 
    `tag_l11n` as tag_l11n_3 ON `tag_3`.`tag_id` = `tag_l11n_3`.`tag_l11n_tag` 
                            AND `tag_l11n_3`.`tag_l11n_language` = 'en' 
ORDER BY 
    `tag_3`.`tag_id` ASC 
LIMIT 25;

Note this can be expanded to as many backup languages as are desired by adding LEFT JOINs and the appropriate columns to the COALESCE.

Demo (of both queries) on dbfiddle

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I didn't downvote, but ored join-conditions usually result in a bad plan. Additionally this logic is hard to extend if you want to add another language, e.g. this any other language
@dnoeth you raise a good point; I've edited the answer with an alternate query that would likely be more performant. Your approach using aggregation is also a good one.
@DennisEichhorn please note I've edited my answer with a query that may by more performant than the original. Also I've added a small demo of both queries.
@Nick Thanks. I also modified a version where I could have any other language if none of the previous ones matched. dbfiddle.uk/…
1

If you are running MySQL 8.0, I would recommend row_number() to handle the prioritization:

SELECT tag_id_3, tag_l11n_title_2
FROM (
    SELECT 
        t.`tag_id` as tag_id_3,
        tl.`tag_l11n_title` as tag_l11n_title_2,
        ROW_NUMBER() OVER(PARTITION BY t.`tag_id` ORDER BY
            (tl.`tag_l11n_language` = 'it') desc,
            (tl.`tag_l11n_language` = 'en') desc,
            tl.`tag_l11n_language`
        ) rn
    FROM `tag` as t
    LEFT JOIN `tag_l11n` as tl ON t.`tag_id` = tl.`tag_l11n_tag` 
) t
WHERE rn = 1
ORDER BY t.`tag_id`
LIMIT 25;

With this solution, you can manage as many levels of prioritization as wanted, by adding more conditions to the ORDER BY clause of ROW_NUMBER(). Currently this puts the Italian translation first, followed by the English translation, followed by any other available translation (alphabetically-wise).

Improve this answer

1 Comment

Good to know, I don't have MySQL 8 but I will keep it in mind for the future since it seems really powerfull.
1

Your WHERE-condition changes the LEFT to an INNER join, resulting in no row returned when there's no no row for en.

The classic solution utilizes one left join perm language and then COALESCE to get the first non-NULL value:

SELECT 
    `tag_3`.`tag_id` as tag_id_3,
    coalesce(`tag_l11n_it`.`tag_l11n_title`  -- same order as join order
            ,`tag_l11n_en`.`tag_l11n_title`
            ,`tag_l11n_2`.`tag_l11n_title`) as tag_l11n_title_2
FROM 
    `tag` as tag_3 
LEFT JOIN 
    `tag_l11n` as tag_l11n_it
ON `tag_3`.`tag_id` = `tag_l11n_it`.`tag_l11n_tag` 
AND -- WHERE changes the LEFT join to INNER 
    `tag_l11n_it`.`tag_l11n_language` = 'it'  -- main language

LEFT JOIN 
    `tag_l11n` as tag_l11n_en 
ON `tag_3`.`tag_id` = `tag_l11n_en`.`tag_l11n_tag` 
AND
    `tag_l11n_en`.`tag_l11n_language` = 'en'  -- 2nd language

LEFT JOIN 
    `tag_l11n` as tag_l11n_2
ON `tag_3`.`tag_id` = `tag_l11n_2`.`tag_l11n_tag` 
AND
    `tag_l11n_2`.`tag_l11n_language` = 'de'   -- default language
ORDER BY 
    `tag_3`.`tag_id` ASC 
LIMIT 25;

While GMB's solution works without default language, this solution requires a default (the language with no missing translation), in your case probably de:-)

Maybe GMB's query canbe improved by applying ROW_NUMBER before the join:

SELECT tag_id_3, tag_l11n_title_2
FROM `tag` as t
LEFT JOIN
 (
    SELECT 
        `tag_l11n_tag` as tag_id_3,
        `tag_l11n_title` as tag_l11n_title_2,
        ROW_NUMBER()
        OVER(PARTITION BY t.`tag_id`
             ORDER BY
               CASE WHEN `tag_l11n_language` = 'it' THEN 1
                  WHEN `tag_l11n_language` = 'en' THEN 2
                  ELSE 3
               END
            ,tl.`tag_l11n_language`) AS rn
    FROM `tag_l11n`
  ) AS t1
ON t.`tag_id` = tl.`tag_l11n_tag` 
WHERE t1.rn = 1
ORDER BY t.`tag_id`
LIMIT 25;

And this priorization logic logic can also be used with aggregation:

SELECT tag_id_3, tag_l11n_title_2
FROM `tag` as t
LEFT JOIN -- INNER JOIN should be possible
 (
    SELECT 
        `tag_l11n_tag` as tag_id_3,
        COALESCE(MAX(CASE WHEN `tag_l11n_language` = 'it' THEN `tag_l11n_title` END)
                ,MAX(CASE WHEN `tag_l11n_language` = 'en' THEN `tag_l11n_title` END)
                ,MAX(CASE WHEN `tag_l11n_language` = 'de' THEN `tag_l11n_title` END)
                -- if there's no default you can get a random value using ,MAX(`tag_l11n_title`)
                ) AS tag_l11n_title_2
    FROM `tag_l11n`
    GROUP BY `tag_l11n_tag`
  ) AS t1
ON t.`tag_id` = tl.`tag_l11n_tag` 
ORDER BY t.`tag_id`
LIMIT 25; -- might be possible to move into Derived Table
Improve this answer

Comments

0

Thanks to @Nick answer I came up with new idea to use function FIELD() which would cover all possible languages without joining separate table for each language but with one subselect. I am not sure about performance comparing to the other answers, but it would be faster if the languages are indexed by numerical values than strings (en,it etc.).

Of course translations with NULL should be excluded in subselect.

SELECT 
    `tag`.`tag_id` as tag_id,
    COALESCE(`tag_l11n`.`tag_l11n_title`,"No translation") as tag_l11n_title
FROM 
    `tag` as tag
LEFT JOIN (
      SELECT
          `tag_l11n_tag` as tag_id,
          `tag_l11n_title` as tag_l11n_title,
      FROM 
          `tag_l11n`
      WHERE 
          `tag_l11n_title` IS NOT NULL,
      ORDER BY
          FIELD(`tag_l11n_language`,"it","en","es")
      LIMIT 1
      ) as tag_l11n ON `tag_l11n`.`tag_id` = `tag`.`tag_id`
ORDER BY 
    `tag`.`tag_id` ASC
Improve this answer

1 Comment

Apart from the typos, this will only give a result for the first value in the tag table: dbfiddle.uk/…

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.