Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /share/CACHEDEV1_DATA/Web/www/libraries/UBBcode/text_parser.class.php on line 228
Selecting on preference in MySQL

Comments Blog About Development Research Sites

Selecting on preference in MySQL

Nov 23, 2009
There is almost nothing you cannot do using three very simple query commands and some groundwork with PHP. Using nothing more than SELECT, INSERT and DELETE one is, in theory, able to do everything possible on an SQL database that normally requires such intrinsicaties as JOIN, GROUP, ORDER, LIMIT, etcetera. And of course, as should be clear to anyone, doing it all in PHP is horribly slow.

Thus follows the paradigm that, in order to become a good PHP programmer, it is necessary to know when not to use PHP. Recently I came across a situation many programmers sooner or later encounter: to select a specific data field, but only if that data field was available. Otherwise, a generic value should be selected instead.

These kind of queries generally arise in one form or another in many applications. For example, take the case of a multi-language website where the title can be translated, but no translations for all possible languages have been entered yet. You would want to select the field in the users language if available, and if not fall back to a generic value - like the english title for example. One is inclined to perform first a query for the optimal language and if no result is returned, perform a second query for the generic language.

While not wildly inefficient, with many such fields on a website and a low availability of translations this easily amounts to a dozen extra queries per pageview (except of course if one were to use Memcache, but that is another story altogether). So, how would you go about retrieving this in a single request? Easy - by creative use of MySQL's ORDER BY clause!

Code (php) (nieuw venster):
1
2
3
4
5
6
SELECT     `content`
FROM       `translations`
WHERE      `field`     = 'title'
ORDER BY   `language`  = 'Dutch'   DESC,
           `language`  = 'English' DESC
LIMIT      1


This will sort on those translations with 'Dutch' translations first, and if none are evidently better than the others (ie, there are no 'Dutch' translations) it will sort by 'English' translations next. If those too are unavailable any other field will be returned if available, so a particular field with only a 'German' translation for example will fall back to this 'German' translation, but only if no prefered language is available!

For increased speed, one will of course want to add indexes on both the language column as well as the field column. Additionally, the `language` column should of course not be a string - a SMALLINT languageId is much more efficient here (albeit a micro-optimalisation). The example becomes a lot more clear this way though.

FragFrog out!

New comment

Your name:
Comment: