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

[Alexander Hartmaier]

I would also need those kind of joins.
I've worked around it by creating result view classes which restrict the
resultset when joined:

package NAC::Model::DBIC::Table::Device;

__PACKAGE__->resultset_class('NAC::Model::DBIC::ResultSet::Device');

__PACKAGE__->has_many( view_currently_in_maintenance    =>
'NAC::Model::DBIC::Table::View_Device_Currently_In_Maintenance'      =>
'fk_device' );

package NAC::Model::DBIC::Table::View_Device_Currently_In_Maintenance;

use strict;
use warnings;
use base 'NAC::Model::DBIC::Table::Parent::View';

__PACKAGE__->table(__PACKAGE__->schemaname .
'view_device_currently_in_maintenance');
__PACKAGE__->result_source_instance->is_virtual(1);
__PACKAGE__->result_source_instance->view_definition('
    SELECT fk_device, fk_maintenance, name, description, datetime_start,
datetime_end
    FROM (
...

package NAC::Model::DBIC::ResultSet::Device;

sub search_currently_in_maintenance {
    my $self = shift;

    return $self->search_rs(undef,
        {
            prefetch    => 'view_currently_in_maintenance',
        },
    );
}

Am Freitag, den 18.09.2009, 12:29 +0200 schrieb Paweł Pabian:
> On Thursday, 17 of September 2009 18:18:44 Emanuele Zeppieri wrote:
> > 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/Resul
> > >tSource/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)
>
> Yes.
>
> It gives correct result but In my case it's ~900 times slower than JOIN due to
> DEPENDENT SUBQUERY optimizer bug in MySQL 5.x. That's why i asked question
> about JOIN version.
>
> Thanks.
>
> _______________________________________________
> 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
--
LG Alex


*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
T-Systems Austria GesmbH   Rennweg 97-99, 1030 Wien
Handelsgericht Wien, FN 79340b
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
Notice: This e-mail contains information that is confidential and may be privileged.
If you are not the intended recipient, please notify the sender and then
delete this e-mail immediately.
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*