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

[Rob Kinyon]

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.

-- 
Thanks,
Rob Kinyon
http://streamlined-book.blogspot.com/