Discussion:
has_many LEFT JOIN with parameter
(too old to reply)
Ton Voon
2011-05-06 17:50:18 UTC
Permalink
Hi!

Is it possible to have a has_many relationship where the LEFT JOIN ON relationship contains a parameter? For instance, something like in User.pm:

__PACKAGE__->has_many( "useraccounts", "UserAccounts", { "foreign.userid" => "self.id", "foreign.accountid" => $value }, { join_type => "left" } );

Where $value can be specified as part of the search for the ON condition.

Is this possible?

Ton
David Ihnen
2011-05-06 18:08:13 UTC
Permalink
It wasn't in the code version they had when I was encountering that issue...
we used a workaround where we defined queries manually and called them to
fulfill that purpose... I think it might be related to the ability of
SQL::abstract to work with that kind of expression.

Newer code versions might allow it... but I think it was more fundamental
than something that could be fixed with a minor revision.
Post by Ton Voon
Hi!
Is it possible to have a has_many relationship where the LEFT JOIN ON
__PACKAGE__->has_many( "useraccounts", "UserAccounts", { "foreign.userid"
=> "self.id", "foreign.accountid" => $value }, { join_type => "left" } );
Where $value can be specified as part of the search for the ON condition.
Is this possible?
Ton
_______________________________________________
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/
--
David Ihnen
Voice contact (562) 743-1807
Ton Voon
2011-05-09 07:54:26 UTC
Permalink
It wasn't in the code version they had when I was encountering that issue... we used a workaround where we defined queries manually and called them to fulfill that purpose... I think it might be related to the ability of SQL::abstract to work with that kind of expression.
Thanks. I want to avoid manual queries, as there are dynamic joins that could occur, so I want to leave DBIC to work those out.

Ton
Justin Hunter
2011-05-06 18:10:59 UTC
Permalink
Per
http://search.cpan.org/dist/DBIx-Class/lib/DBIx/Class/Relationship/Base.pm#condition,
under "To specify joins which describe more than a simple equality of column
values, the custom join condition coderef syntax can be used. For example:"

__PACKAGE__->has_many(
"useraccounts", "UserAccounts"
sub {
my $args = shift;

return {
"$args->{foreign_alias}.userid" => { -ident =>
"$args->{self_alias}.id" },
"$args->{foreign_alias}.accountid" => { '=', $value },
};
}
);

not sure where the join_type will go, but that should be the gist.

Justin
Post by Ton Voon
Hi!
Is it possible to have a has_many relationship where the LEFT JOIN ON
__PACKAGE__->has_many( "useraccounts", "UserAccounts", { "foreign.userid"
=> "self.id", "foreign.accountid" => $value }, { join_type => "left" } );
Where $value can be specified as part of the search for the ON condition.
Is this possible?
Ton
_______________________________________________
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/
Ton Voon
2011-05-09 08:04:52 UTC
Permalink
Per http://search.cpan.org/dist/DBIx-Class/lib/DBIx/Class/Relationship/Base.pm#condition, under "To specify joins which describe more than a simple equality of column values, the custom join condition coderef syntax can be used. For example:"
__PACKAGE__->has_many(
"useraccounts", "UserAccounts"
sub {
my $args = shift;
return {
"$args->{foreign_alias}.userid" => { -ident => "$args->{self_alias}.id" },
"$args->{foreign_alias}.accountid" => { '=', $value },
};
}
);
not sure where the join_type will go, but that should be the gist.
Thanks Justin. I wasn't aware of this functionality.

It is close, but not quite there. I think I want the example of:

My::Schema::Artist->has_many(
cds_80s => 'My::Schema::CD',
sub {
my $args = shift;
return {
"$args->{foreign_alias}.artist" => { -ident => "$args->{self_alias}.artistid" },
"$args->{foreign_alias}.year" => { '>', "1979", '<', "1990" },
};

}
);

... but where the 80s is dynamically entered at search time. Maybe something like:

$schema->resultset("Artist")->search( { name => "Elton John" }, { join => { "cds_decade" => { "-params" => [ "1979", "1990" ] } } })

Is this a useful feature? If so, I'm happy to have a go at this if someone can point where this change should be.

Ton
Peter Rabbitson
2011-05-09 14:08:22 UTC
Permalink
Post by Ton Voon
Per http://search.cpan.org/dist/DBIx-Class/lib/DBIx/Class/Relationship/Base.pm#condition, under "To specify joins which describe more than a simple equality of column values, the custom join condition coderef syntax can be used. For example:"
__PACKAGE__->has_many(
"useraccounts", "UserAccounts"
sub {
my $args = shift;
return {
"$args->{foreign_alias}.userid" => { -ident => "$args->{self_alias}.id" },
"$args->{foreign_alias}.accountid" => { '=', $value },
};
}
);
not sure where the join_type will go, but that should be the gist.
Thanks Justin. I wasn't aware of this functionality.
My::Schema::Artist->has_many(
cds_80s => 'My::Schema::CD',
sub {
my $args = shift;
return {
"$args->{foreign_alias}.artist" => { -ident => "$args->{self_alias}.artistid" },
"$args->{foreign_alias}.year" => { '>', "1979", '<', "1990" },
};
}
);
$schema->resultset("Artist")->search( { name => "Elton John" }, { join => { "cds_decade" => { "-params" => [ "1979", "1990" ] } } })
Is this a useful feature? If so, I'm happy to have a go at this if someone can point where this change should be.
This is a useful feature, the lack of which affected the decision to not ship
0.08200 (but instead stick to the 1xx series a bit longer). Also it's a very
complex task in its early design stages. The problem is that the surface of
the API is getting quite crowded, and we can easily paint ourselves into a
corner if we are not extremely careful. For this particular feature there
are the following obstacles to consider (all of them have seen some but not
enough progress)

- what happens to create_related and friends
- the current join/prefetch spec merging mechanism does not have a concept
of options
- more stuff I have not yet thought of :)

That said you are welcome to experiment/sketch things, all I am trying to say
is that this is not a 15 minute task, and to make things worse there's a lot
of pressure to get things just right before we reach the physical limits of
DBIC as conceived back in 2005.

Also as a quick and dirty hack you can very well define your relatinship as
sub { .... foo => $some_class_global ... }
and simply ensure that you have it set during operations (perhaps by executing
some sanity-checks in the very same relcond sub).

I am on hiatus this month, so won't be able to offer much guidance/advice on
potential improvements, but feel free to poke the rest of the wonderful
folk in #dbix-class

Cheers!

Loading...