[Dbix-class] retrieve_from_sql

[Emanuele Zeppieri]

> -----Original Message-----
> From: dbix-class-bounces at lists.rawmode.org 
> [mailto:dbix-class-bounces at lists.rawmode.org] On Behalf Of 
> Emanuele Zeppieri
> Sent: Thursday, August 04, 2005 5:49 PM
> To: dbix-class at lists.rawmode.org
> Subject: Re: [Dbix-class] retrieve_from_sql
> 
> 
> > -----Original Message-----
> > From: dbix-class-bounces at lists.rawmode.org 
> > [mailto:dbix-class-bounces at lists.rawmode.org] On Behalf Of 
> > Krzysztof Krzyzaniak
> > Sent: Thursday, August 04, 2005 1:28 PM
> > To: dbix-class at lists.rawmode.org
> > Subject: Re: [Dbix-class] retrieve_from_sql
> > 
> > 
> > Fix me if am I wrong. I wonder if any of abstract sql 
> modules support 
> > functions (builded or defined by user) - I mean something 
> > like "SELECT 
> > function1(field), function2(field) WHERE function3(field) = NULL;
>  
> 
> Syntactically-wise, SQL::Abstract can handle such cases, since it does
> not perform any syntactic check on the field names, so (once fixed the
> missing FROM clause) your query becomes:
> 
> my @fields = ( 'function1(field)', 'function2(field)' );
> my %where = (
> 	'function3(field)' => 'NULL',
> );
> my($stmt, @bind) = $sql->select('t', \@fields, \%where);
> print "$stmt\n"; # if you wanna see it
> 
> (assuming that your NULL comparison is permitted by the DBMS).
> 
> Rather, SQL::Abstract can't handle much simpler cases, such 
> as when two
> (or more) fields are involved in the same subexpression, like this:
> 
> SELECT f1 FROM t WHERE t.f1 < t.f2
> 
> In such cases SQL::Abstract permits (and forces) you to resort to
> literal SQL in some parts of the search condition, thus 
> losing some info
> on the syntactic structure of the query.

Well, my example is the same than your example really.
Basically SQL::Abstract loses the ability to preserve all the info about
the syntactic structure of the query, when in your search condition
there is some comparison predicate where you can't isolate a single
field on the left side and a constant subexpression on the right side
(or vice versa ;-)
(This is not the only case where SQL::Abstract fails of course).

Neverthelsss, thanks to the fact that SQL::Abstract permits you to
resort to literal SQL and the fact that it is syntactically agnostic,
you can probably anyway express your query (or at least part of it,
since it can possibly be interspersed with literal SQL) with
SQL::Abstract.
Of course you also have no guarantee that your query is syntactically
correct, therefore your DBMS can spit it with an error.

Ciao,
Emanuele.