CODEWITHSUNDEEP
rubypostgresqljoinsequel
Nomura Nori
Nomura Nori

Reputation: 5167

How to solve AmbiguousColumn error in postgresql with ruby project

I would like to join two query result. First one is

Gig.where(id:gigsInRadioIDS)
When I convert result to json I get this result
[{"id":1,"address":"test1"},{"id":2,"address":"test2"}

Second is.

Slot.where(status:"available").where(Sequel.lit('("from" > ?) AND ("to" < ?)', fromdate, todate))
when I convert result to json I get this result
[{"id":15,"status":"available","gig_id":1}]

What I want is to inner join to result so I hope to get this result(I abbreviated some non related snippet)

[{"id":15,"status":"available","gig_id":1,"id":1,"address":"test1"}]

What I have tried is

Gig.where(id:gigsInRadioIDS).as(:tb_gig).join(Slot.where(status:"available").where(Sequel.lit('("from" > ?) AND ("to" < ?)', fromdate, todate)), gig_id: :id)

And I got this error

Sequel::DatabaseError - PG::AmbiguousColumn: ERROR:  column reference "id" is ambiguous
LINE 1: ...) AS "t1" ON ("t1"."gig_id" = "gigs"."id") WHERE ("id" IN (2...

Thanks for reading this complex code. Any news will be big help. Thanks.

Upvotes: 2

Views: 1369

Answers (2)

mwp
mwp

Reputation: 8467

There are a bunch of ways to approach it. It looks like you are querying Slots with some Gigs data mixed in, so you may want to start with your Slot model. The regular join syntax is a little easier to understand and makes it easier to select columns from both tables (i.e. instead of WHERE fk IN (..)). As a bonus, you can mix your gig_id filter into the JOIN clause. Finally, you don't need to use Sequel.lit to do greater than / less than in your WHERE clause, and you should be explicit about the columns you want to select.

Here's how I would write it:

Slot.join(:gigs, [[:id, :gig_id], [:id, gigsInRadioIDS]])
  .where(status: 'available') { (from() > fromdate) & (to() < todate) }
  .select(Sequel[:slots][:id].as(:slot_id), :status, :gig_id, :address)

Getting .all of the records and outputting JSON should yield something like:

[{"slot_id":15,"status":"available","gig_id":1,"address":"test1"}]

Note the Sequel[:table_name][:column_name] syntax. This is the new style of fully-qualifying identifiers in Sequel 4.49+. (In past versions of Sequel, it would have been written like :table_name__column_name, but that syntax is now deprecated.)

Upvotes: 4

vladpisanov
vladpisanov

Reputation: 145

Your where(id:gigsInRadioIDS) translates into WHERE ("id" IN (...)), and since both gigs and slots have an "id" column, the database doesn't know which id you want.

You need to explicitly specify the table with where{{gigs[:id] => gigsInRadioIDS}}

Querying with Sequel

Upvotes: 0

Related Questions