Ambiguous 'id' reference

[awei82]

Hi, I'm running into the following issue:

I have a many-to-many relationship between two tables ("Teams" and "Members"), and I have a join table called "Connections".
Connections has a primary key named "id", and the "id" column is causing a conflict when attempting a query like the following:
Team[1].members_dataset.exclude(id: [5,6])
Below is the SQL generated from the statement above:

"SELECT \"members\".* 
FROM \"members\" 
INNER JOIN \"connections\" ON (\"connections\".\"member_id\" = \"member\".\"id\") 
WHERE ((\"connections\".\"team_id\" = 1) AND (\"id\" NOT IN (5, 6)))"

Basically, I want to get the members of Team[1], excluding those with member ID's 5 and 6. However, the query can't differentiate between members.id and connections.id. Is there any good way around this?

The error I'm receiving, for reference:

PG::AmbiguousColumn: ERROR:  column reference "id" is ambiguous
LINE 1: ... AND ("connections"."team_id" = 1) AND ("id" NOT IN (5, 6)))

[jeremyevans]

The reference id needs to be qualified, because it is ambiguous. Assuming you are referring to members.id and not connections.id, you should switch to: Team[1].members_dataset.exclude{{members[:id] => [5,6]}}

[awei82]

Thank you! I was unfamiliar with that syntax. It works!

[tycooon]

You can also use Team[1].members_dataset.exclude(Sequel[:members][:id] =~ [5,6]) in case you don't like/want virtual blocks.

[awei82]

Thanks!