Database problem: Fetching a default translation from a table

Question

Good day,

I have a MySQL table descriptions, which holds fields such as: lang_id, label, short_description, long_description and is_default.

In my application, product descriptions are fetched from the database according to the current language. Everything works fine for now, however I'd like to add a default descriptions for each product so that whevener a description in the desired language isn't found, the default description will be fetched instead.

Right now, my requests look like this:

 SELECT 
    description.id AS record_id
    description.label,
    description.short_description,
    description.long_description      
 FROM
    products,
    description,
    languages
 WHERE
    products.id = '.$someProductID.' AND
    products.id = description.product_id AND
    languages.id = description.lang_id AND
    languages.code = "'.$someLang.'"

Does anyone have a solution for fetching the default description of a product when the desired translation doesn't exist ?

I thought of adding some IFNULL statements to my request, something like this:

IFNULL(description.label, (SELECT label FROM description WHERE product_id = '.$someProductID.' AND is_default = 1) ) AS label

But I'm not very familiar with such complex queries and I couldn't make it work.

I'm open to suggestions ;)

Thank you !

Answer 1

This one:

SELECT  p.*, COALESCE (dn.name, den.name) AS cname
FROM    products p
LEFT JOIN
        description dn
ON      dn.product_id = p.id
        AND dn.language =
        (
        SELECT  id
        FROM    language
        WHERE   code = 'your_language'
        )
LEFT JOIN
        description den
ON      den.product_id = p.id
        AND den.is_default
        )
WHERE   p.id = @my_product

, or this one:

SELECT  p.*,
        COALESCE (dn.name,
        (
        SELECT  den.name
        FROM    description den
        WHERE   den.product_id = p.id
                AND den.is_default
        )
        ) AS cname
FROM    products p
LEFT JOIN
        description dn
ON      dn.product_id = p.id
        AND dn.language =
        (
        SELECT  id
        FROM    language
        WHERE   code = 'your_language'
        )
WHERE   p.id = @my_product

In all databases but MySQL, the first one is more efficient when you have few translations for your language, the second one is more efficient when you have lots of translations.

In MySQL, the second query (with COALESCE) is always more efficient.

See this series of articles in my blog on this problem for performance details:

• Fallback language names: Oracle

, and navigate further for other RDBMS's

Answer 2

You could join the description table again under a different alias (e.g., defaults) where you're only pulling results from the default language. Not sure if that's more of a performance drag than your IFNULL idea.

Answer 3

You can fetch the language ids up front and use them to build the query correctly:

// this code is performed once and the results are stored for use in all description lookups
SELECT id INTO :def_lang_id FROM languages WHERE code = :default_lang_code;
SELECT id INTO :usr_lang_id FROM languages WHERE code = :user_lang_code;

// this is an example of using the above results to speed your search for the "correct" descriptions
if ($def_lang_id == $usr_lang_id) {
    $sql = "SELECT d.id, d.label, d.short_desc, d.long_desc
            FROM products p, description d
            WHERE p.id = :some_prod_id
              AND p.id = d.product_id
              AND d.lang_id = :usr_lang_id";
} else {
    $orderdirection = $def_lang_id < $usr_lang_id ? "DESC" : "ASC";

    $sql = "SELECT d.id, d.label, d.short_desc, d.long_desc
            FROM products p, description d
            WHERE p.id = :some_prod_id
              AND p.id = d.product_id
              AND d.lang_id in (:def_lang_id, :usr_lang_id)
            ORDER BY d.lang_id $langdirection";
}

Now if the user is using the default language, it only gets the one description. If they are using a different language, the alternate language's lang_id is sort before the default_id value in the query. So if there is an alternate description, it is returned in the first row of the query and if there is no alternate description, only the default description is returned.

The other obvious benefit to this code is we don't need to join the language table every time.

Answer 4

Thank you for your help guys, I'm approaching the solution !

@jmucchiello: I'm not looking for PHP side validations. I want MySQL to do all the job, since I'll be using it in many other SQL queries.

In other words, I want to make that request, store it in a view in order to have a simplified way to obtain translated descriptions of my products. Something like: descriptionView.

I would then use it this way:

SELECT * FROM descriptionView WHERE lang_code = "en" AND product_id = 80007

Right now this is what I have:

SELECT 
    descriptions.code,
    IFNULL(t1.label, t2.label) AS label,
    IFNULL(t1.short_description, t2.short_description) AS short_description,
    IFNULL(t1.long_description, t2.long_description) AS long_description
FROM
    descriptions
LEFT JOIN
    translations t1
ON
    t1.description_id = descriptions.id AND t1.lang_id = 
    (
     SELECT
      id
     FROM
      languages
     WHERE
      code = "fr"
    )
LEFT JOIN
    translations t2
ON
    t2.description_id = descriptions.id AND t2.is_default = 1

WHERE
    descriptions.id = 1

It's working fine, however I need to remove the hardcoded "1" and "fr", because I want to transform it in a view which will collect all entries, and then perform a select on that view.

The problem is, for some reason, I cannot change the first left join to:

LEFT JOIN
        translations t1
    ON
        t1.description_id = descriptions.id AND t1.lang_id = languages.id

and add languages in the FROM clause.

Finally, I'd remove the WHERE clause, since I want all the result.

Thus, my final query would look like this:

CREATE VIEW descriptionView AS
    SELECT 
        languages.code as lang,
        descriptions.code,
        IFNULL(t1.label, t2.label) AS label,
        IFNULL(t1.short_description, t2.short_description) AS short_description,
        IFNULL(t1.long_description, t2.long_description) AS long_description
    FROM
        descriptions,
        languages
    LEFT JOIN
        translations t1
    ON
        t1.description_id = descriptions.id AND t1.lang_id = languages.id
    LEFT JOIN
        translations t2
    ON
        t2.description_id = descriptions.id AND t2.is_default = 1

I could then query that view using:

SELECT * FROM descriptionView WHERE lang_code = "en" AND product_id = 80007