Re^2: joining tables with SQL::Abstract

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

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

Replies are listed 'Best First'.
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]

Replies are listed 'Best First'.

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]