[Dbix-class] Adding dynamic join conditions

[Renaud Drousies]

Hello,

I am sorry if this is a FAQ, I couldn't find help in the documentation nor
the  cookbook. I am trying to find information about how to add "dynamic"
conditions in the join conditions, here is my example:

Restaurants >---hasmany--- Categories
                               |
                            hasmany
                               |
                              /|\
                         Categories_trans

This means a restaurant belongs to 1 category (fast-food, greek, etc...)
and that category can be translated in multiple languages,
Categories_trans contains 3 fields: category, which links to Categories,
lang, which is a language code, and label, which is the translation in
that language.

Here are the relationships I have defined:

Categories:
__PACKAGE__->has_many(restaurants => 'DB::Resto::Restaurants' => 'category');
__PACKAGE__->has_many(trans => 'DB::Resto::CategoriesTrans' => 'category',
{join_type => 'left'});


Restaurants:
__PACKAGE__->belongs_to(category => 'DB::Resto::Categories');

CategoriesTrans:
__PACKAGE__->belongs_to(categories => 'DB::Resto::Categories' => 'category');



My problem is that I would like to add a dynamic left join condition
between Categories and CategoriesTrans based on the language, right now
what I have is:

    $c->stash->{by_category} = [$c->model('Resto::Restaurants')->search(
                {
                    'city.province' => $c->stash->{province},
                    'trans.lang' => $c->stash->{language}
                },
                {
                    join => ['city', { 'category' => 'trans' }],
                    select => [  'category.id', 'trans.label', { count =>
'me.id' } ],
                    as => [ 'id', 'name', 'count' ],
                    group_by => [qw/ category.id trans.label /] ,
                    order_by => [qw/ trans.label /]
                }
            ) ];


but this generates a query as:

SELECT category.id, trans.label, COUNT( me.id )
FROM Restaurants me
		JOIN Cities city ON ( city.id = me.city )
		JOIN Categories category ON ( category.id = me.category )
		LEFT JOIN Categories_trans trans ON ( trans.category = category.id )
WHERE ( city.province = 'qc' AND trans.lang = 'fr' )
GROUP BY category.id, trans.label
ORDER BY trans.label

As you see, the condition is "global" (I am not sure what the term is). I
would like to list all Categories and the count of restaurants that are in
them, but the problem with that condition is that it will not list the
categories that have no label in the current language, what Id like is to
have a row with a NULL label when this happens, so a query like :

SELECT category.id, trans.label, COUNT( me.id )
FROM Restaurants me
		JOIN Cities city ON ( city.id = me.city )
		JOIN Categories category ON ( category.id = me.category )
		LEFT JOIN Categories_trans trans ON ( trans.category = category.id AND
trans.lang='fr')
WHERE ( city.province = 'qc'  )
GROUP BY category.id, trans.label
ORDER BY trans.label


Is this possible with DBIx::Class?
Thanks!

Regards,
Renaud