[Dbix-class] constructing a query where fields are compared to each other

[Peter Rabbitson]

On Wed, Oct 16, 2013 at 09:04:54PM -0400, Rob Kinyon wrote:
> On Wed, Oct 16, 2013 at 8:39 PM, Karen Etheridge <perl at froods.org> wrote:
> > On Wed, Oct 16, 2013 at 08:13:34PM -0400, Rob Kinyon wrote:
> >> It's good you showed all the code because the extended comparison in a
> >> join relationship isn't possible. (It's one of ribasushi's many
> >> in-progress branches. Patches most definitely welcome here.)
> >>
> >> However, this is definitely possible in a search. You have to use the trapdoor.
> >>
> >>     ->search({ 1 => \"me.field_x - me.field_y" });
> >>
> >> That is a reference to a string.
> >
> > A ha, so we drop down to literal SQL (via the scalar reference), but we
> > can't do that in a join because we can't jam the $args->{foreign_alias}
> > bits into the reference - it's then no longer literal SQL.
> 
> Not quite. The relationship join syntax is a very limited subset of
> the full search syntax. For example,
> 
>   __PACKAGE__->has_many(foo => 'My::Schema::Result::Bar' => {
>       'foreign.foo_id' => 'self.id',
>       'foreign.some_column' => 'broken',
>   });
> 
>   __PACKAGE__->has_many(ranged_bar => 'My::Schema::Result::Bar' => {
>       'foreign.bar_id' => 'self.id',
>       'foreign.lower_bound' => { '>=' => '10' },
>       'foreign.upper_bound' => { '<=' => '20' },
>  });
> 
> both won't work. You also can't put in subqueries, functions, or
> anything else "fancy". (The second thing is one way of implementing
> the "nested shells" relational-tree storage algorithm, though you'd
> really have to be able to pass in bind parameters to the relationship
> traversal for it to truly work.)
> 
> As I said earlier, this use-case has been on ribasushi's radar for some time.
> 

For future archive-lurkers: the above is not entirely correct. While one 
indeed can not do things using the syntax above, the coderef-based 
relationship specification deals with this just fine. Even insanity like 
this is possible (this is obviously a live working test:

https://github.com/dbsrgits/dbix-class/blob/f3b1224/t/lib/DBICTest/Schema/CD.pm#L108