Database problem: selecting default translation from table

Question

Good day,

I have a MySQL description table that contains fields such as: lang_id, label, short_description, long_description and is_default.

In my application, product descriptions are displayed from the database according to the current language. At the moment, everything works fine, but I would like to add default descriptions for each product, so as not to find a description in the desired language, the default description will not be found.

My queries now 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 to get the product description by default when the desired translation does not exist?

I thought about adding some IFNULL statements to my query, something like this:

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

, .

;)

!

Answer 1

:

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

:

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

, MySQL, , , - , .

MySQL ( COALESCE) .

:

• : Oracle

RDBMS

Answer 2

(, ), . , , IFNULL.

Answer 3

:

// 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";
}

, , . , lang_id default_id . , , , , .

, .

Answer 4

, , !

@jmucchiello: PHP. , MySQL , SQL-.

, , , . - : descriptionView.

:

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

, :

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

, "1" "fr", , , .

, - :

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

FROM.

, WHERE, .

So my last request 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

Then I could request this view using:

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