数据库问题:从表中获取默认翻译。

Good day,

美好的一天,

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

我有一个MySQL表描述，它包含如下字段:lang_id、label、short_description、long_description和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语句，如下所示:

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 !

谢谢你!

4 个解决方案

#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.

在除MySQL之外的所有数据库中，当你的语言只有很少的翻译时，第一个会更有效，当你有很多翻译时，第二个会更有效。

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

在MySQL中，第二个查询(使用联合)总是更有效。

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

关于这个问题的性能细节，请参阅我的博客中的这一系列文章:

• Fallback language names: Oracle
• 回退语言名称:Oracle

, and navigate further for other RDBMS's

，并导航到其他RDBMS

#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.

您可以再次使用不同的别名(例如，默认值)加入description表，在这里您只能从默认语言中提取结果。不确定这是否比IFNULL的想法更会影响性能。

#3

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

您可以预先获取语言id并使用它们来正确构建查询:

// 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.

现在，如果用户使用的是默认语言，它只会得到一个描述。如果它们使用的是另一种语言，则替代语言的lang_id在查询中的default_id值之前排序。因此，如果有一个替代描述，它将返回到查询的第一行，如果没有替代描述，则只返回默认描述。

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

这段代码的另一个明显好处是，我们不需要每次都加入语言表。

#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.

@jmucchiello:我不是在寻找PHP方面的验证。我希望MySQL完成所有的工作，因为我将在许多其他SQL查询中使用它。

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.

换句话说，我想提出这个请求，将它存储在一个视图中，以便以一种简化的方式获得对我的产品的翻译描述。喜欢的东西: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.

它工作得很好，但是我需要删除硬编码的“1”和“fr”，因为我想要在收集所有条目的视图中对其进行转换，然后在该视图上执行select。

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.

并在FROM子句中添加语言。

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

最后，我将删除WHERE子句，因为我想要所有的结果。

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