Using subqueries for locale fallback in MySQL

I have a hosted online store product that supports custom fields for various data types. Cutting to the chase, anyone can add extra key/value pairs to any record by adding to a table that is defined as:

`storeId` varchar(20) NOT NULL,
`locale` varchar(100) NOT NULL,
`table` varchar(30) NOT NULL,
`id` varchar(50) NOT NULL,
`key` varchar(30) NOT NULL,
`value` mediumblob NOT NULL,
PRIMARY KEY (`storeId`, `locale`, `table`, `id`, `key`)

In Europe, lots of people want to support multiple languages, so each key can have multiple values for different locales. Each store will have a default locale (designated by the store owner) and an active locale (chosen by the customer).

The query I'm trying to put together behaves like so:

• The storeId, table and id are all known, as are the active locale and default locale.
• If a value for a key is not available for the active locale, the value for the default locale is returned instead.
• If a value for the default locale is also not available, return the value for any other locale.

I've come up with a pretty awkward query that achieves the desired result. Let's say the store ID is 1, the table is Products, and the ID is gadget1; the default locale is en_AU and the active locale is de_DE. Here's what we'd query:

SELECT * FROM 
    (SELECT `key`, `value` 
    FROM nf_CustomFields 
    WHERE `storeId` = 1
        AND `table` = 'Products'
        AND `id` = 'gadget1'
    ORDER BY `locale`='de_DE' DESC, 
        `locale`='en_AU' DESC
    ) 
AS a 
GROUP BY `key`;

This works, but it's ugly and seems inefficient. I know there's a bett开发者_如何转开发er way but I can't put my finger on it.

Can anybody suggest something better?

Many thanks!

You could try LEFT JOINing nf_CustomFields to itself and then use COALESCE to ignore the NULLs that the LEFT JOIN will produce if there are no native language rows. Something like this (untested and possibly not working) query:

select coalesce(nl.`key`,   def.`key`),
       coalesce(nl.`value`, def.`value`)
from nf_CustomFields nl
left outer join nfCustomFields def
     on nl.`store`  = def.`store`
    and nl.`table`  = def.`table`
    and nl.`id`     = def.`id`
    and nl.`locale` = 'de_DE'
 where def.`locale` = 'en_AU'
   and def.`id`     = KNOWN
   and def.`store`  = KNOWN
   and def.`table`  = KNOWN

The KNOWN would, of course, be replaced with your known values and you'd use real dynamic values in place of 'de_DE' and 'en_AU' of course.