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() ];