Mysql – join two tables and limit results by an ordered list

MySQL

problem:

given is a table with language support (translations)

a user can configure a list of languages (order is priority)

now i have to get all rows which are part of app and the locale is in the list of preconfigured languages for a user;

only ONE result per property shall be returned

so for example the user configured de-AT, de-DE, de, en as preferred user settings
NOTE: not all values need to be defined!

the user requests all data for app='test'
the expected result is

 property, value
('father', 'Oida'),
('lastName', 'Nachname'),

Table for Multilanguage Data

CREATE TABLE IF NOT EXISTS `i18n` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `app` VARCHAR(20) NULL,
    `locale` VARCHAR(10) NULL,
    `property` VARCHAR(20) NULL,
    `value` VARCHAR(1000) NULL,
    PRIMARY KEY (`id`)
);

example values in i18n table

INSERT INTO `i18n` (app, locale, property, value)
VALUES 
('test', 'de-AT', 'father', 'Oida'),
('test', 'de-DE', 'father', 'Papa'),
('test', 'de', 'father', 'Vater'),
('test', 'en', 'father', 'father'),
('test', 'fr', 'father', 'père'),
('test', 'en','lastName', 'last name'),
('test', 'de','lastName', 'Nachname'),
('test', 'fr','lastName', 'nom de famille'),

my idea was to put user config into its own table (if its possible without this it would be even better)

Table for User Config

CREATE TABLE IF NOT EXISTS `i18n_cfg` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `user` VARCHAR(20) NULL,
    `locale` VARCHAR(10) NULL,
    `priority` INT NULL,
    PRIMARY KEY (`id`)
);

example values in i18n_cfg table

INSERT INTO `i18n_cfg` (locale, user, priority)
VALUES 
('de-AT', 'lm', 1),
('de-DE', 'lm', 2),
('de', 'lm', 3),
('en', 'lm', 4),
('de', 'hr', 1),
('en','hr', 2),

i'm not really getting to a point
i get only one result or way too much

NOTE using mysql 5.7 (if it would work on sql server too – with minor changes – it would be perfect)

Best Answer

you need to join table with priority and use ROW_NUMBER function to sort rows for each property, then you just need to leave rows with row number = 1:

WITH CTE AS (
SELECT t1.property
,t1.value
,ROW_NUMBER() OVER (PARTITION BY t1.property ORDER BY t2.priority) AS rn
FROM i18n t1
JOIN i18n_cfg t2 ON t1.locale = t2.locale
WHERE t1.app = 'test' 
AND t2.user = 'lm'
)
SELECT property, value
FROM CTE 
WHERE rn = 1

solution without window functions and variables:

SELECT t1.property 
,(SELECT t2.value
  FROM i18n AS t2
  JOIN i18n_cfg AS t3 ON t2.locale = t3.locale
  WHERE t2.property = t1.property
  AND t2.app = 'test'
  AND t3.user = 'lm'
  ORDER BY t3.priority
  LIMIT 1) AS value
FROM
(
SELECT DISTINCT property
FROM i18n
WHERE app = 'test'
) AS t1

link to dbfiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=cf1d3f4757c3d84d3f5ad2bea0966a3c

Related Question