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.pab...@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. _______________________________________________ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk