Rails: Group projects by user then status with a count-CodePudding

I am trying to get a list of users, and a count of the projects they have in the different statuses.

I have a user table, and the following for my projects table.

create_table :projects do |t|
  t.string      :slug, null: false
  t.belongs_to  :user
  t.string      :title
  t.text        :description
  t.integer     :is_status
end

In my user model I have the association needed to projects.

class User < ApplicationRecord
  has_many :projects

And the project model I have added the association to the user

class Project < ApplicationRecord
  belongs_to        :user

Now I have tried this..

Project.includes(:user).group(:user_id, :is_status)
.select(: user_id, :is_status, "SUM(is_status) as is_status_count").where.not
(user_id: 1).where.not(is_status: nil).order("user_id DESC").collect{ |a
| [a.user.name, a.is_status, a.is_status_count] }

But this does not return something which is easily read. I would ideally like something more like the following, which owl help when I build the front end.

{
  "user_1": {
    "status_1": 1,
    "status_2": 4,
    "status_3": 10
  }
}

Any help is much appreciated

CodePudding user response：

Let's start with the query you already have

relation = Project.includes(:user).group(:user_id, :is_status)
  .select(:user_id, :is_status, "SUM(is_status) as is_status_count").where.not
  (user_id: 1).where.not(is_status: nil).order("user_id DESC")

From there, instead of collecting them into an array, we want to end up with a hash with a single key for each user_id

hash = relation.group_by { |project| project.user.name} # { "user_1": [<#Project>, <#Project>] }

Now we want to turn the array of projects into a hash with one value from each

hash.transform_values do |projects|
  projects.map do |project|
    [project.is_status, project.is_status_count]
  end.to_h
end