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

[Eden Cardim]

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.

-- 
   Eden Cardim       Need help with your Catalyst or DBIx::Class project?
  Code Monkey                    http://www.shadowcat.co.uk/catalyst/
 Shadowcat Systems Ltd.  Want a managed development or deployment platform?
http://edenc.vox.com/            http://www.shadowcat.co.uk/servers/