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

Hi all

I am trying to build an SQL query with SQL::Abstract but I can't find how to join tables. For example, suppose that I want to get the following SQL query:

SELECT users.username FROM users, drivers WHERE users.userid=drivers.u
+serid AND drivers.name="John"

[download]

This is my SQL::Abstract code:

my $sql = SQL::Abstract->new;
my($stmt2, @bind2) = $sql->select("users, drivers", ["users.username"]
+, {"drivers.name" => "John"});

[download]

This, generates the following:

$stmt2 = "SELECT users.username FROM users, drivers WHERE ( drivers.na
+me = ? )"
@bind2 = ("John")

[download]

My question is, ¿How can I include the join users.userid=drivers.userid inside the WHERE clause?. This doesn't work:

my($stmt2, @bind2) = $sql->select("users, drivers", ["users.username"]
+, {"drivers.name" => "John","users.userid" => "drivers.userid"});

[download]

Because SQL::Abstract treats drivers.userid as a quoted string.

In DBIx::Abstract you can do this by playing with the join field:

$dbh->select({
    fields=>'*',
    table=>'foo,bar',
    join=>'foo.id=bar.foo_id',  ## <--- HERE
    where=>{'foo.dollars',['>',30]}
    });

[download]

But I can't find its equivalent in SQL::Abstract

Does anybody know how to do that?

Thank you very much in advance,

citromatik

Replies are listed 'Best First'.
Re: joining tables with SQL::Abstract
by pc88mxer (Vicar) on Jun 11, 2008 at 18:04 UTC
    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
    );
[reply]
[d/l]
[select]
      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]

        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]
Re: joining tables with SQL::Abstract
by perrin (Chancellor) on Jun 11, 2008 at 19:33 UTC
    Last time I needed one of these, I just used Rose::DB::Object::QueryBuilder because I couldn't see how to make SQL::Abstract do joins. If you look at the source for QueryBuilder, you'll find pretty solid support for joins with specific types and criteria (explained in a block comment), but unfortunately this isn't in the POD yet.
[reply]
Re: joining tables with SQL::Abstract
by dragonchild (Archbishop) on Jun 11, 2008 at 19:17 UTC
    SQL::Abstract is used in DBIx::Class. See what they do.
    My criteria for good software:
    1. Does it work?
    2. Can someone else come in, make a change, and be reasonably certain no bugs were introduced?
[reply]
Re: joining tables with SQL::Abstract
by Herkum (Parson) on Jun 11, 2008 at 18:36 UTC

    I don't like SQL::Abstract, I find that it abstracts too much. It is OK, for a simple SQL query, but once it starts to get complicated, so does the data structure for SQL::Abstract. While it might be more portable than written database specific SQL, I find that idea offers very little value considering how rarely the situation crops up.

[reply]

Back to Seekers of Perl Wisdom