CODEWITHSUNDEEP
ruby-on-railsgroup-byrails-activerecord
simonlehmann
simonlehmann

Reputation: 882

Code suggestion for nested grouping ActiveRecord | Rails 5.2

I'm working on a project that needs to do some basic statistic reporting. For example, the model Post has a foreign key field category_id referring to a record in the Category model, and boolean field published which has a default value of false (along with title, body, author - but they're not relevant to this question).

I want to set up and nested grouping to iterate through the Post model and group the Post records by their Category, and then within each category, further group by the state of the published field, to give a count for each grouping, to render an outcome similar to below:

Categories
Tutorial
  Published: 14 posts
  Draft:     3 posts
Q & A
  Published: 14 posts
  Draft:     3 posts
Letter
  Published: 14 posts
  Draft:     3 posts

Below is the non-functional code I've started with:

<% @posts.group(:category).each do |category| %>
  <% category.label %>

  <% category.each.group(:published).count.each do |published_status, count| %>
    <%= published_status %>: <%= pluralize(count, "post") %>
  <% end %>
<% end %>

Any feedback or suggestions on how to modify the code above would be very much appreciated. Thanks

Upvotes: 0

Views: 46

Answers (1)

Vishal
Vishal

Reputation: 7361

Please try below query it is not tested but i think it should work

NOT TESTED

categories = Category.left_outer_joins(:posts)
                                .select("
                                       Categories.label, 
                                       (SELECT COUNT(posts.id) from posts where posts.published=true) as published_count, 
                                       (SELECT COUNT(posts.id) from posts where posts.published=false) as draft_count
                                       ")
                                .group('categories.id')

In View

<% categories.each do |category| %>
  <%= category.label %>
  <%= category.published_count %>
  <%= category.draft_count %>
<% end %>

Upvotes: 1

Related Questions