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/ResultSource/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)
_______________________________________________
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
