[Dbix-class] PATCH: Support for arbitrary SQL in relationship definition

[Peter Rabbitson]

Rob Kinyon wrote:
> On Wed, Jun 10, 2009 at 16:23, Daniel Ruoso<daniel at 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