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

[Rob Kinyon]

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.

The reason for the lack of arithmetic is that the all the operators
SQL::Abstract is concerned with are comparisons. Arithmetic isn't a
comparison. :)

Rob

On Wed, Oct 16, 2013 at 7:20 PM, Karen Etheridge <perl at froods.org> wrote:
>
> I'm trying to figure out how to construct a WHERE clause [1] like:
>
> ... WHERE field_x - field_y = 1
>
> Is this possible?  The best guess I have is:
>
>     { '1' => [ '-' => { -ident => 'field_x' },
>                       { -ident => 'field_y' }  ]  }
>
> ...but this expands to:
>     ( 1 = ? OR 1 = me.field_x OR 1 = me.field_y )   with the bind variable being '-'.
>
> The only operators that are covered in the SQL::Abstract docs are >, <, =
> and !=, so are arithmetic operations even possible?
>
> Since this is a pretty gross query regardless, I am leaning towards simply
> adding a new column to this table, which is the value of field_x - field_y,
> so I can query on that directly.
>
>
> [1] I'm actually wanting to do this in a join relationship, so my real code
> looks like the below - but I hope I've simplified adequately...
>
> __PACKAGE__->has_many(cats_dogs => 'MyApp::Schema::Dogs' =>
>     sub {
>         my $args = shift;
>         return +{
>             # foreign.field_x - foreign.field_y = 1
>             '1' => [ '-' => { -ident => "$args->{foreign_alias}.field_x" },
>                             { -ident => "$args->{foreign_alias}.field_y" }
>         }
>     },
> );
>
>
> thanks,
> -ether at cpan.org
>
> _______________________________________________
> 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



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