NYCPHP Meetup

[Daniel Convissor]

Hi John:

On Tue, Apr 22, 2008 at 01:31:52PM -0400, John Campbell wrote:
> 
> I have a products table with a standard auto number primary key, and a
> descriptions table that is keyed off the product id and a language id
> ('en','es','zh_cn', etc)
...
> SELECT product.id, product.price, (SELECT d.description FROM
> descriptions d WHERE d.product_id=product.id AND d.lang_id IN
> (:1,'en') ORDER BY d.lang_id!='en' DESC LIMIT 0,1) as description
> FROM product
> WHERE product.category=:2

Don't use sub selects unless really necessary.  They kill performance.

Also, it's very helpful to use the same column names for the same thing 
throughout the database.  For example, use "product_id" in both the 
product and descriptions tables.  Makes things clearer and allows use of 
"USING".

Also also, use a consistent naming convention.  You've got plural 
descriptions and singular product.

Personally, I dislike aliasing tables (unless you're using the same table 
twice, of course) because it obfuscates the query.

Anyway, all that aside, except the first point, here's how I'd do it:


SELECT product.id, product.price,
COALESCE(user_lang.description, default_lang.description) AS description,
COALESCE(user_lang.lang_id, default_lang.lang_id) AS lang_id

FROM product
LEFT JOIN descriptions AS user_lang
  ON (
    user_lang.product_id = product.id
    AND lang_id = :1
  )
LEFT JOIN descriptions AS default_lang
  ON (
    default_lang.product_id = product.id
    AND lang_id = 'en'
  )

WHERE product.category = :2


--Dan

-- 
 T H E   A N A L Y S I S   A N D   S O L U T I O N S   C O M P A N Y
            data intensive web and database programming
                http://www.AnalysisAndSolutions.com/
 4015 7th Ave #4, Brooklyn NY 11232  v: 718-854-0335 f: 718-854-0409