NYCPHP Meetup

[Kenneth Dombrowski]

Hi John, 

On 08-04-22 13:31 -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)
> 
> I want to join the description table to the product table on a 1:0,1
> basis, and if the users language is something other than english, I
> want to use that language as the default but fall back on english.

probably there's a cleverer way, but i would probably go with something
like this over a subselect: 

SELECT 
        p.id , 
        p.price , 
        IF(d.description, d.description, en.description) AS description , 
        IF(d.lang_id, d.lang_id, en.lang_id) AS lang_id 
FROM product AS p 
INNER JOIN user AS u ON u.id = :1 
LEFT JOIN description AS d ON (
        d.product_id = p.id 
        AND d.lang_id = u.lang_id 
) 
LEFT JOIN description AS en ON ( 
        en.product_id = p.id 
        AND d.lang_id = 'en' 
)
WHERE p.category = :2 

hth, 
kenneth