CODEWITHSUNDEEP
mysqlmariadb
Cornwell
Cornwell

Reputation: 3410

Get data by "priority"

I have the following table for localization:

   Key     |         Value            |   lang
MainTitle  |  Welcome to my page      |    en
MainTitle  |  Bienvenue sur mon site  |    fr
....

Not all keys are localized yet, so the way it's currently working is.

  1. Get all English rows
  2. Get all localized rows (overwrite existing English ones)

This is the query I'm using:

SELECT * FROM local.strings s WHERE lang = 'en';

Is there a very efficient way of solving this without having to use multiple or inner queries?

Upvotes: 0

Views: 49

Answers (3)

Mijago
Mijago

Reputation: 1639

set @lang = "fr"; # You can write this DIRECTY in the query. This was just for testing
select * from (SELECT * FROM `test` ORDER BY CASE WHEN `lang` LIKE @lang THEN 1 ELSE 2 END) as tmp group by `key`;

The test-table:

CREATE TABLE `test` (
  `key` text,
  `value` text,
  `lang` text
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The test-data:

MainTitle   Welcome to my page  en
MainTitle   Bienvenue sur mon site  fr
SubTitle    Hello   en
SubTitle    123 fr
Text1   Cookie  en
Text1   Keks    de

Result:

MainTitle   Bienvenue sur mon site  fr
SubTitle    123 fr
Text1   Cookie  en

Upvotes: 0

Strawberry
Strawberry

Reputation: 33945

SELECT `key`
     , COALESCE(loc.value,en.value) value
     , COALESCE(loc.lang,en.lang) lang 
  FROM strings en 
  LEFT 
  JOIN strings loc 
    ON loc.key = en.key 
   AND loc.lang <> en.lang -- or maybe "AND loc.lang = 'fr'" 
 WHERE en.lang = 'en';

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270463

If you want to get all keys for a given language, then use:

SELECT *
FROM local.strings s
WHERE lang = 'fr'
UNION ALL
SELECT *
FROM local.strings s
WHERE lang = 'en' AND
      NOT EXISTS (SELECT 1 FROM local.strings s2 WHERE s2.key = s.key and s2.lang = 'fr');

This is actually the inverse of your logic:

  1. Get all localized rows.
  2. Add in the English rows that don't match.

Upvotes: 2

Related Questions