How to find Records where associations' associations are only in a certain status

user_13482752 0
Viewed 50

So I've got a somewhat complicated architecture in a new microservice, and I'm having trouble figuring out how we can get this query done in Rails 6 ActiveRecord syntax, and not pulling the models into Ruby.

Find Authors where associated Posts' associated Comments are all status: 'moderated'

Authors have many Posts
Posts are Commentable (polymorphic join)
(Authors are also Commentable (polymorphic join))

Closest I can get is 

Author.joins(posts: :comments)
      .where.not(comments: { status: :needs_moderated })
      .where(comments: { status: :moderated })
      .group('authors.id')

Which gets me all Authors with posts, but I want Authors where ALL of their Posts' Comments are ONLY status: :moderated

Is this possible ?

ruby-on-rails-6 postgresql activerecord ruby-on-rails ruby
1 Answers

The following query gives you the authors with at least a comment that is not moderated:

bad_authors = Author.joins(posts: :comments)
                    .where.not(comments: { status: :moderated })
                    .select(:id)

Then the following query gives you the authors for which associated Posts' associated Comments are all status moderated:

Author.where.not(id: bad_authors)

Note that this returns also the authors without comments (as all their comments are moderated, empty verifies everything). The query can be easily modified to remove the authors without comments if you need to.

Note also that this raises a single query (with a subquery) even if you write it in two pieces. You can use pluck instead of select in the first query to generate two queries. What is more efficient depend son the database you are using and the indexes you have.

user_62453370
Related