[Proposal] ActiveRecord support for exists clause

Besides the exists? It would be great support for something like:

Post.exists(User.active.where("users.id = posts.user_id"))
# SELECT posts.* from posts WHERE EXISTS (SELECT 1 FROM users WHERE users.active = true AND users.id = posts.user_id)

Instead of:

Post.where(User.active.where("users.id = posts.user_id").select(1).arel.exists)

Why not just:

User.joins(:posts).exists?

or if you have a specific User ID in mind:

User.joins(:posts).where(id: user_id).exists?

I want a list a of posts for the active users… Not a boolean. It would be an alternative to Posts.where(user: User.active), that depending the case is less performant than EXISTS. (IN vs EXISTS)

Maybe merge is what you want? According to the guide you might be able to do something like this:

Post.joins(:user).merge(User.active)

I haven’t tested this to confirm, but I think it would build the query that you want.

Yeah it could do the thing… But there are many others situations that an exists clause would be the best option. And rails doesn’t support it out of the box.

I see what you mean. But maybe @Lorin_Thwaits’s comment kind of proves why the language might be confusing. exists? is pretty prevalent and to add a similar method that gives very different results could be confusing. I suspect that it would also be opening up a can of worms (multiple exists, nested exists) that might be more than you’re bargaining for. I’d prefer to hear the opinion of someone who’s actually contributed to this part of Rails, though.

Ah! Now I more clearly understand your goal, and realise that #active is likely a scope you’ve defined in the User model. If you’re okay to have the same kind of logic from that scope be instead in a .where then I can recommend:

Post.joins(:user).where(active: true).select('posts.*')

FWIW, we’ve been using EXISTS a lot. Some references

1 Like

New idea guys…

Post.where.associated(Author.where("authors.id = posts.author_id"))
# SELECT "posts".* FROM "posts"
# WHERE EXISTS (SELECT 1 FROM "authors" WHERE (authors.id = posts.author_id))

Post.where.missing(Author.where("authors.id = posts.author_id"))
# SELECT "posts".* FROM "posts"
# WHERE NOT EXISTS (SELECT 1 FROM "authors" WHERE (authors.id = posts.author_id))