[Dbix-class] additional condition in LEFT JOIN in DBIx::Class

[Paweł Pabian]

On Thursday, 17 of September 2009 16:24:26 Peter Rabbitson wrote:
> Eden Cardim wrote:
> > On Thu, Sep 17, 2009 at 9:40 AM, Paweł Pabian <pawel.pabian at implix.com> 
wrote:
> >> Hi
> >>
> >> I have some users
> >>
> >> package Schema::User;
> >> __PACKAGE__->table('users');
> >> __PACKAGE__->add_columns('id', 'login');
> >> __PACKAGE__->set_primary_key('id');
> >> __PACKAGE__->has_many('attributes' => 'Schema::Attribute', 'users_id');
> >>
> >> and they have many attributes
> >>
> >> package Schema::Atrribute;
> >> __PACKAGE__->table('attributes');
> >> __PACKAGE__->add_columns('users_id', 'name', 'value');
> >> __PACKAGE__->set_primary_key('users_id', 'name');
> >> __PACKAGE__->belongs_to('user' => 'Schema::User', 'users_id');
> >>
> >> Now i want to find Users that don't have Attribute of given name.
> >> In raw SQL it needs name condition to be placed in LEFT JOIN:
> >>
> >> SELECT *
> >> FROM users AS u
> >> LEFT JOIN attributes AS a
> >>    ON u.id=a.users_id
> >>    AND a.name="car"
> >> WHERE a.users_id IS NULL
> >>
> >> How to force DBIx::Class to add this
> >> ----
> >> AND a.name="car"
> >> ----
> >> part to join condition?
> >
> > DBIC doesn't support variable join conditions, but you can add the AND
> > a.name = 'car' condition to your where clause to the same effect.
>
> He can not. A left with right-side condition join is not the same as
> left join + where condition.

Indeed. In this case it will make impossible condition.

> The only way to do this currently is with a virtual view:
> http://search.cpan.org/~ribasushi/DBIx-Class-0.08111/lib/DBIx/Class/ResultS
>ource/View.pm

Thanks for linking this View module. It makes things complicated (for 
performance reasons i won't explain here) but possible.