[RJBS - Thu Nov 4 06:39:31 2004]:
Show quoted text> This is a big deal, because in its absence, I can see no other way to
> build this query without
> resorting to literal SQL.
I would like to echo this comment and elaborate.
The reason this lack is so serious is that several useful modules
assume that SQL::Abstract may be relied upon for building the where
clauses. Because there is _no_ possible way to say "( ( abc !
= 'foo' ) AND ( abc != 'bar' ) )" I can't use
SQL::Abstract::Limit
Class::DBI::Plugin::AbstractCount
Class::DBI::Plugin::Pager
Class::DBI::AbstractSearch
Class::DBI::Plugin::HTML
and a couple others I've gone through in the last five days or so.
I'm particular hacked because I can't find a portable LIMIT clause
module that doesn't rely on SQL::Abstract, excepting perhaps
DBIx::SearchBuilder.
There is simply no way to construct the multi-value negative
assertions (i.e. '!=' and 'NOT LIKE') which require 'AND' to make
sense.
Very frustrating, as everything else needed tested as possible using
SQL::Abstract. I very much like it, but can't use it!
I too have had to build a literal SQL where clause and will have to
repeat some of Class::DBI::Plugin::Pager and be non-portable in
addition.
Here are some notes from my trying combinations based on reading the
code:
{ 'k' => 'v' }
{ 'k' => { 'f' => 'x' }
The checks below made in this order (e.g. is-a-word before non-word
operator).
{ abc => undef }
{ abc => [1,2] }
becomes [ {abc=>1},{abc=>2} ]
{ abc => { word => [1,2] } }
becomes abc BETWEEN ? AND ? (if word eq 'between')
becomes abc WORD (?,?)
{ abc => { '<' => [1,2] } }
becomes [ {abc=>{'<'=>1}}, {abc=>{'<'=>2}} ]
{ abc => { '!=' } }
becomes abc IS NOT NULL
{ abc => { '=' } }
becomes abc IS NULL
{ abc => { '<' => 1 } }
becomes abc < ?
{ abc => \'xyz' }
becomes abc xyz
{ abc => 1 }
becomes abc = ?
Perhaps revising the documentation to include a more exhaustive
enumeration of possibilites would be "a good thing". (sorry, no
fixes, I'm already exhausted)
BTW: maybe the 'BETWEEN' check could ensure the array value has
exactly two values?
