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 <
[hidden email]>
> 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@...--
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.
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
_______________________________________________
List:
http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-classIRC: irc.perl.org#dbix-class
SVN:
http://dev.catalyst.perl.org/repos/bast/DBIx-Class/Searchable Archive:
http://www.grokbase.com/group/dbix-class@...
