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

[Peter Rabbitson]

Paweł Pabian wrote:
> On Thursday, 17 of September 2009 16:24:26 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.
> 
> Indeed. In this case it will make impossible 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/ResultS
>> ource/View.pm
> 
> Thanks for linking this View module. It makes things complicated (for 
> performance reasons i won't explain here) but possible.
> 
Note the *virtual* part of the view module. What it in essence does is take
a piece of SQL and execute it (exactly what you want). The database does not
know about any views at all.