Re: joining tables with SQL::Abstract

I don't know if this is documented, but I just changed it into a string reference (e.g. \" = drivers.userid" and it gave what I think you want:

my($stmt2, @bind2) =
  $sql->select("users, drivers",
               ["users.username"],
               { "drivers.name" => "John",
                 "users.userid" => \"= drivers.userid"}
   );
print Dumper(\$stmt2, \@bind2);
# produces:
$VAR1 = \'SELECT users.username FROM users, drivers WHERE ( drivers.na
+me = ? AND users.userid = drivers.userid )';
$VAR2 = [
          'John'
        ];
[download]

Update: It is documented, but it should be given it's own section and title so that it stands out more.

Finally, sometimes only literal SQL will do. If you want to include literal
SQL verbatim, you can specify it as a scalar reference, namely:

    my $inn = 'is Not Null';
    my %where = (
        priority => { '<', 2 },
        requestor => \$inn
    );

Comment on Re: joining tables with SQL::Abstract Select or Download Code

Replies are listed 'Best First'.
Re^2: joining tables with SQL::Abstract by citromatik (Curate) on Jun 25, 2008 at 11:27 UTC
I just changed it into a string reference (e.g. \" = drivers.userid") But this strategy fails if you want to look for a key field. For example, to obtain the following query: `SELECT drivers.username FROM users, drivers WHERE users.userid=drivers +.userid AND users.userid=1000` [download] You should do something like: `my($stmt2, @bind2) = $sql->select("users, drivers", ["drivers.username"], { "users.userid" => 1000, "users.userid" => \"= drivers.userid"} # <- Overrides + previous "users.userid" key );` [download] But you end up with 2 identical keys in the anonymous hash and the second will override the first. Yes, one solution is to swap the (key,value) pair in last hash entry to `drivers.userid => \"= users.userid"`, but for doing that automatically (that's the point for using SQL::Abstract after all) I must check all possible keys in the WHERE clause. Is there a way to overcome this? Thanks in advance citromatik	[reply] [d/l] [select]
Re^3: joining tables with SQL::Abstract by larryl (Monk) on Nov 19, 2008 at 17:32 UTC
It's pretty easy in SQL::Abstract to force an AND situation when you have two (or more) conditions on the same column, just use an arrayref where the first element is '-and'. E.g.: `{ create_date => [-and => {'>=' => $start_date}, {'<=' => $end_date}] }` [download] which produces: `WHERE ( ( ( create_date >= ? ) AND ( create_date <= ? ) ) )` [download] For your case you would use: `{ 'users.userid' => [-and => {'=' => 1000}, \'= drivers.userid'] }` [download] which produces: `WHERE ( ( ( users.userid = ? ) AND ( users.userid = drivers.userid ) + ) )` [download] HTH, Larry	[reply] [d/l] [select]