Forcing select to return records 1

[csteinhilber]

Okay... I'm trying to develop a localization system that will return localized strings for a web application.

Using MySQL 4.1 and ColdFusion.

My method accepts a string "key", and the desired language, then searches the localization table for the appropriate string in the specified language.

So the table is set up like:

 || id    | key    | language   | localizedValue  ||
 ===================================================
 || 1234  | Hello  | en         | Hello           ||
 || 1235  | Hello  | es         | Hola            ||
 || 1236  | Hello  | de         | Hallo           ||
 || 1237  | Hello  | fr         | Bonjour         ||
 || 1238  | Yes    | es         | Si              ||
 || 1239  | Yes    | fr         | Oui             ||

I'm not guaranteed that a key has entries for all languages, nor am I guaranteed that the key exists for English, nor am I guaranteed that a key will exist at all for any language.

Soooooooo... here's what I want to have happen:

- If the key exists for a given language, return that value
- If the key does not exist for a given language, and the key exists for English, return the English value (default)
- If the key doesn't exist at all, return the key

The query I have so far is:

SELECT
	IFNULL(
		localeValues.localizedValue,
		IFNULL(
			englishValues.localizedValue,
			'Default (key) value'
		)
	) as returnValue
FROM
    (
	    SELECT
		    localizedValue, key
	    FROM
	    	localLanguageStrings
	    WHERE 
	    	language = 'en'
    ) AS englishValues
LEFT JOIN
    (
	    SELECT
		    localizedValue, key
	    FROM
	    	localLanguageStrings
	    WHERE 
	    	language = 'de'
    ) AS localeValues
 ON localeValues.key = englishValues.key
 
 WHERE englishValues.key = 'Hello'

which works quite well, until one or the other subselects returns no rows (ie - no entry exists for that key in that language). Then, of course, the join can proceed and the whole thing returns no records.

I'm thinking I need some way to force the subselects to always return something, even if no rows actually match the WHERE (which is where the IFNULL would help me out... but doesn't). But I can't quite wrap my brain around how to do that.

Or maybe I'm looking at it completely wrong.

Anybody have any ideas?

Thanks in advance!
-Carl

 

[TonyGroves]

How about:
[tt]
SELECT 0 result,key,localizedvalue
FROM locallanguagestrings
WHERE key="$key" AND language="$language"
UNION
SELECT 1,key,localizedvalue
FROM locallanguagestrings
WHERE key="$key" AND language='en'
UNION
SELECT 2,"$key",NULL
ORDER BY result
LIMIT 1
[/tt]

 

[csteinhilber]

Glorious Tony!

The example fails on the ORDER BY (near as I can remember, you can't order on aliases), but works nicely without it. I'll have to test it out to see if there are any ill-effects to running it without the ORDER BY.

I figured somebody needed to hit me upside the head and show me a different way

Thanks again!
-Carl

 

[TonyGroves]

I think you would need an ORDER clause, as the order is undefined otherwise (as far as I know, UNION doesn't define order).
You could use instead:
[tt]
SELECT *
FROM
(
SELECT 0 result,key,localizedvalue
FROM locallanguagestrings
WHERE key="$key" AND language="$language"
UNION
SELECT 1,key,localizedvalue
FROM locallanguagestrings
WHERE key="$key" AND language='en'
UNION
SELECT 2,"$key",NULL
) s
ORDER BY result
LIMIT 1
[/tt]

 

[csteinhilber]

Yes, I was starting to notice that relying on the UNION to define the order was a bit hit-and-miss.

The additional subselect syntax works great, and I can now order by result.

Thanks much, Tony! Just what I needed!

-Carl