Select with fallback and default?

Question

Can the following code be converted to database queries?

In our application we have a dictionary, where each value is given by in a table like:

CREATE TABLE dictionary
(
    id serial NOT NULL,
    key text NOT NULL,
    language text NOT NULL,
    value text,
    PRIMARY KEY (id)
)
    

Insertion is like:

INSERT INTO public.dictionary VALUES
    ('yes_button', 'en-us', 'yes'), ('yes_button', 'nl-nl', 'ja')

Now to get a translation one would do like

SELECT key, "en-us"
FROM dictionary
WHERE key in ('yes_button', ...)

Now the problem we have is that we wish to have "paths" to get to the default value, so if the value doesn't exist (or is NULL) it should follow a given path to lookup and get the nearest ancestor. Our current solution is to do this calculation on the backend, but this basically means that we get the full translation table at once, or at least the full path to the root tree.

IE paths would be:

nl-be -> nl-nl -> en-us
en-gb -> en-us
fr-be -> fr-fr -> en-us

With the above insertion if I select nl-be as locale it would hence find "nl-nl", and thus return "ja". While if I select fr-be it would collapse to "yes".

Is it possible to provide such lookups given the above table directly? Or will this always be a task of the backend code and I should just get all translations for a given path?

Answer 1

Well this was fun. Usually I control language strings in the application layer rather than the database, but this was a good opportunity to learn how to use WITH RECURSIVE.

First let's define the tables that will be used: locale and dictionary:

CREATE TABLE locale (
    code           varchar(6)     NOT NULL    ,
    description    varchar(64)    NOT NULL    ,
    fallback       varchar(6)         NULL    ,
    PRIMARY KEY (code)
);


CREATE TABLE dictionary (
    id             serial         NOT NULL    ,
    key            varchar(64)    NOT NULL    ,
    value          varchar(128)   NOT NULL    ,
    locale_code    varchar(6)     NOT NULL    ,
    PRIMARY KEY (id)
);

There should be no surprises here. Locale's code can be 6 characters in length, though it will generally be either 2 or 5. If the fallback is NULL, then the record is considered "top level". This will allow you to have multiple top levels if you so choose, as you will see in the examples below.

Next, let's populate these tables:

INSERT INTO locale ("code", "description", "fallback")
VALUES ('en-us', 'English (US)', NULL), ('en-gb', 'English (UK)', 'en-us'), ('en-ca', 'English (Canadian)', 'en-gb'),
       ('fr-fr', 'French (France)', 'en-us'), ('fr-be', 'French (Belgium)', 'fr-fr'), ('fr-ca', 'French (Quebec)', 'fr-fr'),
       ('nl-nl', 'Dutch', 'en-us'), ('nl-be', 'Dutch (Belgium)', 'nl-nl'), ('ja-jp', 'Japanese', 'en-us'),
       ('es-es', 'Spanish', NULL), ('es-ar', 'Spanish (Argentina)', 'es-es'), ('es-bo', 'Spanish (Bolivia)', 'es-es'),
       ('es-cl', 'Spanish (Chile)', 'es-bo'), ('es-sv', 'Spanish (El Salvador)', 'es-cl'), ('es-mx', 'Spanish (Mexico)', 'es-cl');

INSERT INTO dictionary (key, value, locale_code)
VALUES ('yes_button', 'Yes', 'en-us'), ('yes_button', 'Very Well', 'en-gb'), ('yes_button', 'Sorry, Yes', 'en-ca'),
       ('yes_button', 'Oui', 'fr-fr'), ('yes_button', 'C''est exact', 'fr-ca'), 
       ('yes_button', 'Ja', 'nl-nl'), ('yes_button', 'はい', 'ja-jp'),
       ('yes_button', 'Sí', 'es-es'), ('yes_button', 'Absolutamente', 'es-bo'), ('yes_button', 'Por favor', 'es-sv');

_{Note:
These translations are not at all accurate, but instead intended to show how the final query works and how you might tweak it.}

And now the SQL Query:

WITH RECURSIVE c AS (SELECT 1 as level, code, fallback FROM locale WHERE code = 'nl-be'
                      UNION ALL 
                     SELECT c.level + 1 as level, lo.code, lo.fallback
                       FROM locale lo JOIN c ON c.fallback = lo.code)
SELECT c.level, c.code, dic.key, dic.value
  FROM c LEFT OUTER JOIN dictionary dic ON c.code = dic.locale_code WHERE dic.key = 'yes_button'
 ORDER BY c.level;

The target language you seek is in WHERE code = 'nl-be', and the dictionary key is within the OUTER JOIN with WHERE dic.key = 'yes_button'.

If you run this, you will receive the following results:

level | code  | key        | value
----- | ----- | ---------- | -----
2     | nl-nl | yes_button | Ja
3     | en-us | yes_button | Yes

Because there is no value for nl-be, the next value is returned. If there is no value for that one, en-us will be returned.

You can see this in action with Spanish as well. Change the language to es-sv and results will come back for three options.

level | code  | key        | value
----- | ----- | ---------- | -----
1     | es-sv | yes_button | Por favor
3     | es-bo | yes_button | Absolutamente
4     | es-es | yes_button | Sí

Note that there is no en-us in the output, nor is there any record for "level" 2. This is because es-es does not have a fallback, and there is no matching dictionary entry for es-cl, so it is excluded.

If this works for you, remember to set LIMIT 1 at the end of the SQL query so that you are given just the most relevant value.

Big hat-tip to Laurenz Albe for his many, many examples of how to write RECURSIVE queries across Stack Exchange. I learned something new and (hopefully) solved a problem today.