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

Peter Rabbitson rabbit+dbic at rabbit.us
Thu Sep 17 14:24:26 GMT 2009


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



More information about the DBIx-Class mailing list