Combining scopes that contain joins and groupings

user_13511856 0
Viewed 77

I'm looking for a DRY solution to the problem of combining two or more scopes in Rails which contain different joins and groupings. I have a comments model with a ::with_authors scope (to prevent an N+1 when referencing the author's name), as follows:

# comment.rb
scope :with_authors, -> do
  select("comments.*, users.username AS author_name")
  .joins(:author)
end

And I have a 'votable' concern for a polymorphic association which allows comments and other models to receive votes from users. This includes a ::with_votes scope as follows:

# votable.rb
scope :with_votes, -> do
  select( "#{self.table_name}.*, COALESCE(SUM(votes.value), 0) AS vote_sum" )
  .left_joins(:votes)
  .group("#{self.table_name}.id")
end

Both scopes are needed to show a list of comments with their author's name and the received vote count. But combining the two, post.comments.with_authors.with_votes, causes the familiar error:

> PG::GroupingError: ERROR:  column "users.username" must appear in the
> GROUP BY clause or be used in an aggregate function

I realise that I could write a single scope which performs both joins and change the grouping accordingly, but this would not be transferable to other models that support votes, and violates the principal that each method or scope should do one thing. I also tried using merge - post.comments.with_authors.merge( post.comments.with_votes ) - but this gives the same error. Is there a way to write one or both scopes such that they work together, but so that they can also be used separately, whilst ensuring ::with_votes works for other models using the votable association? Many thanks in anticipation!

group-by postgresql ruby-on-rails ruby
2 Answers

One way do it would be to use a sub-query instead of applying grouping to the base query. For example:

scope :with_votes, -> do
  select( <<~SQL )
    (
      SELECT COUNT(*)
      FROM votes
      WHERE votable_type = '#{self.class.name}' AND 
            votable_id = '#{self.table_name}.id'
    ) AS votes
  SQL
end
user_8942090

I discovered that in order to prevent the group_by error, you can simply chain an additional group statement containing the required additional fields to a pre-defined scope or query. In my case, this meant less duplication of code, and I ended up defining a comment scope as follows, ensuring that the additional group statement was encapsulated:

scope :with_votes_and_authors, -> do
  with_authors.with_votes.group("users.username")
end
user_135118560
Related