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
--
You received this message because you are subscribed to the Google Groups "Ruby
on Rails: Core" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/rubyonrails-core.
For more options, visit https://groups.google.com/d/optout.
