I've got a MySQL table explanations, which holds fields for example: lang_id, label, short_description, lengthy_description and it is_default.
During my application, product explanations are fetched in the database based on the current language. Everything works fine for the time being, however Let me give a default explanations for every product to ensure that whevener an account within the preferred language is not found, the default description is going to be fetched rather.
At this time, my demands seem 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 anybody possess a solution for fetching the default description of the product once the preferred translation does not exist ?
I figured of adding some IFNULL claims to my request, something similar to this:
IFNULL(description.label, (SELECT label FROM description WHERE product_id = '.$someProductID.' AND is_default = 1) ) AS label
But I am not so acquainted with such complex queries and that i could not have great results.
I am available to suggestions )
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 that 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 most databases but
MySQL, the first is much more efficient if you have couple of translations for the language, the 2nd the first is more effective if you have plenty of translations.
MySQL, the 2nd query (with
COALESCE) is definitely more effective.
Check this out number of articles during my blog about this problem for performance particulars:
, and navigate further for other
You can join the description table again within different alias (e.g., defaults) where you are only tugging is a result of the default language. Unsure if that is much more of a performance drag than your IFNULL idea.