Rob Kinyon wrote: > On Wed, Jun 10, 2009 at 16:23, Daniel Ruoso<dan...@ruoso.com> wrote: >> Hi, >> >> For some reason this patch is sitting on my local git copy for a while, >> and now I'm not sure it was even sent at some point... /me-- >> >> So, here goes a patch against current 0.08 trunk to support arbitrary >> SQL in the join condition with an included test, which allow something >> like: >> >> __PACKAGE__->has_many( >> cds_90s => 'DBICTest::Schema::CD', >> { 'foreign.artist' => 'self.artistid', >> 'foreign.year' => \"LIKE '19%'" } >> ); > > I've been thinking about this for a while, then I forgot to reply > until ribasushi poked me about it. > > I understand the desire to build something like this. Everyone wants > everything to be accessible from everywhere. Here's my problem: > > A relationship is nothing more than the following: > * An installed method that generates a resultset > * a piece of metadata used by search() and update() to do automagical > things > > Your cds_90s example could be better written as follows: > > sub cds_decade { > my $self = shift; > my ($decade) = @_; > > return $self->cds({ > year => { like => "19$decade" }, > }); > } > > sub cds_90s { shift->cds_decade( '90' ) } > > Unless, of course, you actually want to join on cds_90s, in which case > you might be better served to use a subquery in your join clause. > > Now, I'm not arguing that it shouldn't be done. However, I'm still > trying to understand the usecase-space. APIs are forever - while we > have workarounds, it behooves us to think things through. >
A classical use-case is "right side condition on a left-join". There is no way to emulate those appropriately with WHERE - the condition has to reside in the join spec itself: i.e. you need to get ALL artists, and all cds issued in a *specific year*. If you do a regular join - you get only artists with CDs. If you do a (standard for has_many) left join - you populate the right side of the join with CDs you don't want, and there is no way to WHERE them out. There are more contrived examples involving the "last" join hack. e.g. the following will get me a resultset of the *last* row for every specific order. SELECT _last.* FROM orders _last LEFT JOIN orders _next ON _last.order_number = _next.order_number AND _last.seen_time < _next.seen_time Granted all of this can be done with subqueries, but 1) joins are much easier on the database and 2) subqueries are not *that* flexible, and are out-right unusable on multi-column PKs (we don't have sane SQLA multi-column IN support, so hacks like [1] are necessary) So there. [1] http://dev.catalyst.perl.org/svnweb/bast/checkout/DBIx-Class/0.08/trunk/lib/DBIx/Class/Storage/DBI/MultiColumnIn.pm _______________________________________________ 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