Hello,
A year ago, there was a pull request (https://github.com/rails/rails/pull/21438) to add a where on an association using the EXISTS clause of SQL. Something like: my_author.posts.where_exists(:comments, user_id: my_user.id)) my_author.posts.where_not_exists(:comments, user_id: my_user.id))
``
That feature was, sadly, refused. I’d like to ask if this is still the stance of the rails core team? Below, I argue a little in favor of it. I’ve personally monkey patched this feature in an app and the feedback from the other devs was 100% positive.
This feature solves a problem that happens quite often. my_author.books.reviewed_by(the_mean_reviewer)
``
The classical way of doing this scope is: my_author.books.joins(:reviewers).where(reviewers: {id: the_mean_reviewer})
``
However, this option has multiple issues. If the reviewer did more than one review for one of the books, then that book will be returned more than once. This can be fixed by using DISTINCT, but can lead to other issues, for example when you use a custom select or want to pluck a different column. Used in a scope, joins on a has_many and has_one, with or without uniq, makes the scope fragile and limits reusability. For belongs_to, i’m not really aware of problems.
In SQL, you can do an “EXISTS” in the where to do this scope. By avoiding the JOIN, this clause becomes a simple and explicit SQL directive. You are not joining, so you don’t need uniq, you just want records that match this condition. Everything just works better.
This feature allows replacing many trivial joins with something less bug-prone that allows better reusability of scopes. Joins remain necessary mostly for complex queries, queries on tables that have no association, and for ordering based on an association.
Here is a less trivial use case: my_author.posts.without_comments_from(my_user) my_author.posts.joins(“LEFT JOIN comments ON comments.post_id = posts.id AND comments.user_id = #{my_user.id}”).where(comments: {id: nil}) my_author.posts.where_not_exists(:comments, user_id: my_user.id))
``
This is trivial using where_not_exists. But you are stuck using a raw SQL join because you need to add conditions to the left join for it to behave the way we want. The interpolation in the join should also normally be sanitized. You then need a where. The where_not_exists is much easier to read. And with the joins, you still have problems related to duplicated rows and DISTINCT that were mentionned above.
As I said, I monkey patched this feature before being aware of the existing pull request. My version can also receive a relation as condition, which is merged in the exists. This allows for nesting of the feature and using scopes as part of the where_exists. Together, this really trivialize many kinds of scopes while also making them more robust.
Has my arguments convinced anyone of how helpful this feature could be? If so, I’d be happy to work on a pull request for this feature.
Regards, Maxime
