EagerGroup

Fix n+1 aggregate sql functions for rails, like

SELECT "posts".* FROM "posts";
SELECT COUNT(*) FROM "comments" WHERE "comments"."post_id" = 1 AND "comments"."status" = 'approved'
SELECT COUNT(*) FROM "comments" WHERE "comments"."post_id" = 2 AND "comments"."status" = 'approved'
SELECT COUNT(*) FROM "comments" WHERE "comments"."post_id" = 3 AND "comments"."status" = 'approved'

SELECT "posts".* FROM "posts";
SELECT COUNT(*) AS count_all, post_id AS post_id FROM "comments" WHERE "comments"."post_id" IN (1, 2, 3) AND "comments"."status" = 'approved' GROUP BY post_id;

SELECT "posts".* FROM "posts";
SELECT AVG("comments"."rating") AS avg_id FROM "comments" WHERE "comments"."post_id" = 1;
SELECT AVG("comments"."rating") AS avg_id FROM "comments" WHERE "comments"."post_id" = 2;
SELECT AVG("comments"."rating") AS avg_id FROM "comments" WHERE "comments"."post_id" = 3;

SELECT "posts".* FROM "posts";
SELECT AVG("comments"."rating") AS average_comments_rating, post_id AS post_id FROM "comments" WHERE "comments"."post_id" IN (1, 2, 3) GROUP BY post_id;

It supports Rails 4.x, Rails 5.x and Rails 6.x

Installation

Add this line to your application's Gemfile:

gem 'eager_group'

And then execute:

$ bundle

Or install it yourself as:

$ gem install eager_group

Usage

First you need to define what aggregate function you want to eager load.

class Post < ActiveRecord::Base
  has_many :comments

  define_eager_group :comments_average_rating, :comments, :average, :rating
  define_eager_group :approved_comments_count, :comments, :count, :*, -> { approved }
end

class Comment < ActiveRecord::Base
  belongs_to :post

  scope :approved, -> { where(status: 'approved') }
end

The parameters for define_eager_group are as follows

definition_name, it's used to be a reference in eager_group query method, it also generates a method with the same name to fetch the result.
association, association name you want to aggregate.
aggregate_function, aggregate sql function, can be one of average, count, maximum, minimum, sum, I define 2 additional aggregate function first_object and last_object to eager load first and last association objects.
column_name, aggregate column name, it can be :* for count
scope, scope is optional, it's used to filter data for aggregation.

Then you can use eager_group to fix n+1 aggregate sql functions when querying

posts = Post.all.eager_group(:comments_average_rating, :approved_comments_count)
posts.each do |post|
  post.comments_average_rating
  post.approved_comments_count
end

EagerGroup will execute GROUP BY sqls for you then set the value of attributes.

define_eager_group will define a method in model. You can call the definition_name directly for convenience, but it would not help you to fix n+1 aggregate sql issue.

post = Post.first
post.commets_average_rating
post.approved_comments_count

Advanced

eager_group through association

User.limit(10).includes(:posts).eager_group(posts: [:comments_average_rating, :approved_comments_count])

pass parameter to scope

class Post < ActiveRecord::Base
  has_many :comments

  define_eager_group :comments_average_rating_by_author, :comments, :average, :rating, ->(author, ignore) { by_author(author, ignore) }
end

posts = Post.all.eager_group([:comments_average_rating_by_author, author, true])
posts.each { |post| post.comments_average_rating_by_author }

first_object and last_object aggregation to eager load first and last association objects.

class Post < ActiveRecord::Base
  has_many :comments

  define_eager_group :first_comment, :comments, :first_object, :id
  define_eager_group :last_comment, :comments, :last_object, :id
end

posts = Post.all.eager_group(:first_comment, :last_comment)
posts.each do |post|
  post.first_comment
  post.last_comment
end

Benchmark

I wrote a benchmark script here, it queries approved comments count and comments average rating for 20 posts, with eager group, it gets 10 times faster, WOW!

Contributing

Bug reports and pull requests are welcome on GitHub at https://github.com/flyerhzm/eager_group.