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

[Paweł Pabian]

On Thursday, 17 of September 2009 18:18:44 Emanuele Zeppieri wrote:
> 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.
> >
> > The only way to do this currently is with a virtual view:
> > http://search.cpan.org/~ribasushi/DBIx-Class-0.08111/lib/DBIx/Class/Resul
> >tSource/View.pm
>
> Wouldn't a subquery like this work with the latest DBIC?
>
> my $users_wo_car = $schema->resultset('User')->search({
>      id => {
>          'not in' => $schema->resultset('Attribute')->search({
>              name => 'car'
>          })->get_column('users_id')->as_query
>      }
> });
>
> (Untested, just noted that there are similar subqueries in from_subquery.t)

Yes.

It gives correct result but In my case it's ~900 times slower than JOIN due to 
DEPENDENT SUBQUERY optimizer bug in MySQL 5.x. That's why i asked question 
about JOIN version.

Thanks.