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
_______________________________________________
List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
Wiki: http://dbix-class.shadowcatsystems.co.uk/
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
Searchable Archive: http://www.mail-archive.com/[email protected]/
