szabgab has asked for the wisdom of the Perl Monks concerning the following question:

I have some more questions regarding SQL::Abstract.

print $sql->where({
    sale => {'==', 1},
    rent => {'==', 1},
})

[download]

gives

sale == ? AND rent == ?

and

print $sql->where([
    sale => {'==', 1},
    rent => {'==', 1},
])

[download]

gives

sale == 1 OR rent == 1

so far it is good. How can I get

city == ? AND (sale == 1 OR rent == 1)

Update

A workaround based on a suggestion of merlyn looks like this with a very strange hash key. 

print $sql->where({
    "(sale==1 OR rent==1) AND city" => {'==', 'Jerusalem'},
}), "\n";

[download]

Anyone a cleaner solution ?

Replies are listed 'Best First'.
Re: more of SQL::Abstract
by CountZero (Bishop) on Jan 08, 2005 at 13:36 UTC
    Taking a peek at the SQL::Abstract shows that:
    • If you test for equality, you do not have to provide the comparison operator, which BTW is '=' and not '==' in MySQL
    • The main logic of this module is that things in arrays are OR'ed, and things in hashes are AND'ed.
    • So: 
      use SQL::Abstract;

my $sql = SQL::Abstract->new;

my @where =   (
        {city   => 'Jerusalem',
         sale =>'1'
        },
        {city   => 'Jerusalem',
         rent =>'1'
        }
    );

my($stmt, @bind)  = $sql->where(\@where);

print "$stmt BOUND WITH @bind\n";

      [download]
    • Gives as output:
      WHERE ( ( city = ? AND sale = ? ) OR ( city = ? AND rent = ? ) )
BOUND WITH Jerusalem 1 Jerusalem 1

      [download]

    CountZero

    "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

[reply]
[d/l]
[select]
switching from SQL::Abstract to SQL::Interpolate
by markjugg (Curate) on Jan 09, 2005 at 03:25 UTC
    As a long time user of SQL::Abstract, I have a suggestion for a cleaner solution: SQL::Interpolate. (Coming soon to a CPAN near you, but ready to use now). 
    use SQL::Interpolate (qw/sql_interp/);

my ($sql,@bind) = sql_interp(
    " city == ",\$city," AND (sale == 1 OR rent == 1)"
);

    [download]
    It provides a very natural and flexible way to mix Perl with SQL, handling bind variables for you along the way. It does handle some special cases to make INSERTS, UPDATES and "IN" clauses especially easy (like SQL::Abstract does).

    After using SQL::Abstract for a long time, I'm now cheerleading SQL::Interpolate.

    Like you, I hit a wall with SQL::Abstract when trying to use nested and grouped "AND and "OR" clauses. At best it was hard to read. At worst it was impossible.

    Mark Stosberg

[reply]
[d/l]
[reply]
[reply]

Back to Seekers of Perl Wisdom