Tuesday, March 23, 2010

COALESCE

Since I've got back to SQL recently it's nice to learn new tricks. COALESCE is great for defaulting to a value after for example a left join.

SQL

SELECT bindings.*, COALESCE( ft.binding, bindings.binding ) AS sortable_binding
FROM bindings
LEFT OUTER JOIN ( SELECT binding, binding_id FROM bindings_translated WHERE language = ? ) AS ft ON ( ft.binding_id = bindings.id )


DBIx:Class

$c->stash->{collections} = [ $product->collections(
{
language => $c->stash->{language}
},
{
'+select' => \'COALESCE(translations.collection, me.collection) AS translated_collection',
join => [ 'translations' ],
order_by => [ 'translated_collection' ],
}
)->all() ];

No comments: