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

Emanuele Zeppieri emazep at gmail.com
Thu Sep 17 16:18:44 GMT 2009


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



More information about the DBIx-Class mailing list