EXISTS operator for assciations.

[davekaro]

I'm coming from using ActiveRecord for which only supports filtering on associations if you JOIN the associated table. I've used this gem https://github.com/MaxLap/activerecord_where_assoc which enables filtering on associations, and it uses the EXISTS operator.

I found the documentation at http://sequel.jeremyevans.net/rdoc/files/doc/association_basics_rdoc.html#label-Filtering+By+Associations to offer similar functionality, but instead of using EXISTS, Sequel uses IN.

My understanding after reading https://www.percona.com/blog/2020/04/16/sql-optimizations-in-postgresql-in-vs-exists-vs-any-all-vs-join/ is you'll often get the same execution plan regardless. However, I thought it was worth discussing whether IN was chosen intentionally over EXISTS, or are there other differences I'm not thinking of?

[jeremyevans]

Sequel uses IN for where(expr => array_or_dataset) (which is what filtering by associations ultimately maps to). It's been that way since before I took over maintenance. You can use EXISTS using where(dataset.exists). Depending on the query and the database, either IN or EXISTS can be faster. In general, the IN implementation is simpler, so I would stick with Sequel's default behavior of IN, and only switch to a manual EXISTS call if it becomes a bottleneck and you have benchmarked both cases and EXISTS is faster.

[davekaro]

Thanks Jeremy, makes perfect sense and clarifies my understanding.

As a follow up, given the structure below:

class Post < Sequel::Model
  one_to_many :comments
end

class Comments < Sequel::Model
  many_to_one :post
end

How could you query for "posts that have a comment"? I've come up with Post.where(comments: Comment.dataset) and Post.where(Comment.select(1).where{{post_id: posts[id]}}.exists) which works great, but if the association had some options set like conditions, they would need to be repeated here. Is there a way in Sequel to use an association dataset in the where(dataset.exists)?

[jeremyevans]

If the association has conditions, the filtering by association should respect those conditions. You would have to build your own dataset to use where(dataset.exists), Sequel doesn't have code that will do that automatically.