Hi Maxime! I think this feature might be more useful as a gem people can install into their applications. Unfortunately, landing new features into Active Record (already at this point a very stable project) is a tough thing to do. :(
Jon Moss Rails Issues Team On Sunday, August 14, 2016 at 5:55:35 PM UTC-4, Maxime Handfield Lapointe wrote: > > 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.
