"Choosing default language or custom language if available"-Query

Undrium

I have two tables:

"Object"

ID
BaseLanguage

"ObjectString"

ObjectID
Language
Name

The user can choose a language and look at objects in their languages if they exist. Problem is I do not know how to deal with this query as effective as possible and instead choose the default language.

$sql = "SELECT * FROM Object 
                            LEFT JOIN ObjectString ON ObjectString.ObjectID = ID
		            WHERE ID=20 AND Language='$preferedLanguage'";

I want this query to take into account if the preferedLanguage does not exist and choose the BaseLanguage (default language) instead. Also I want it to be in the same query, a new query would cost too much.

Can anyone help me?

bradgrafelman

One way I can think of doing it is this: change the language comparison being AND'ed with the ID comparison into a compound expression, one that matches the preferred language OR the Object.BaseLanguage value. Then, add an ORDER BY clause using a CASE statement to ensure that the preferred language appears first in the result sets, if it was found at all. End it with a "LIMIT 1" if you only care about the preferred language if it is available.

In other words, your WHERE clause would include something like:

WHERE
	ID = 123
	AND (
		Language = '$preferedLanguage'
		OR Language = Object.BaseLanguage
	)

and your ORDER BY would include something like:

CASE Language
	WHEN '$preferedLanguage' THEN 1
	ELSE 0
END CASE
DESC

(optionally followed by a 'LIMIT 1' as noted above).

Undrium

Thanks for the reply, I am however not entirely sure that I understand your CASE-solution. Let me just explain what I am looking after so I'm sure we are on the same frequency.

I have the following tables:

"Object"

ID
BaseLanguage

"ObjectString"

ObjectID
Language
Name

The tables contain following data:

"Object"

ID | BaseLanguage

1 | English
2 | English

"ObjectString"

ObjectID | Language | Name

1 | English | Cat
1 | Swedish | Katt
2 | English | Dog

ObjectID is a foreign key referencing Object's ID. ID is a primary key.

If a user is Swedish and has a chosen language of Swedish he will get "Katt" but not "dog" with my following query.

"SELECT * FROM Object
LEFT JOIN ObjectString ON ObjectString.ObjectID = ID
WHERE Language='$preferedLanguage'"

What I want is that he/she gets the English version of dog too because it is the default language. I am not sure how to further develop my query, though, to make this work.

I think you have understood my problem correct. Not entirely sure, though, how to use the CASE WHEN-methodology in my sql. :/

Once again, really appreciate your answer.

bradgrafelman

Undrium;10994270 wrote:
Not entirely sure, though, how to use the CASE WHEN-methodology in my sql. :/

My query will return both the BaseLanguage match and the preferedLanguage match (if such a match exists, of course). How will you know which one is returned first? The answer is, without an ORDER BY clause, you won't.

Thus, my ORDER BY clause simply examines the case where a row matches the preferedLanguage value or if it matches the BaseLanguage value; the former is given a value of 1 while the later is given a value of 0.

Note that I just noticed an error in my ORDER BY in that I forgot to specify DESCending order; the proposed 'LIMIT 1' would take the first row in the result set, and if we order the above CASE statement values in descending order, we'll see that 1 comes before 0 (thus you'd get the 'preferedLanguage' match instead of the BaseLanguage match).

Undrium

Thanks alot for your help, managed it to work with this:

SELECT *
FROM object
LEFT JOIN objectstring ON objectstring.objectID = object.ID
AND (
Language = 'swedish'
OR Language = object.BaseLanguage
)
ORDER BY (
CASE WHEN Language LIKE 'swedish'
THEN 1
ELSE 0
END
) DESC

😃