CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecord
mayorsanmayor
mayorsanmayor

Reputation: 2988

How to group with has_many through in Rails 5

I have the following associations: 

class Certificate < ApplicationRecord
  has_many :certificates_users
  has_many :users, :through => :certificates_users
end

class CertificatesUser < ApplicationRecord
  belongs_to :user
  belongs_to :certificate
end

class User < ApplicationRecord
  has_many :certificates_users, :dependent => :delete_all
  has_many :certificates, :through => :certificates_users
end

I am trying to get a list of Certificates and then group it by the user. Basically, list out all users and show a sublist of their certificates under them. Here's how the UI looks like Just click on 'Mayor' on the table.

Here's the structure of the CertificatesUser table

=> CertificatesUser(certificate_id: integer, user_id: integer, expiry_date: date, certificate_number: string, renewal_date: date, ispublic: integer)

Following this other answer, I was able to produce these possible solutions, 

Certificate.joins(:users).group('users.id').select('users.id')

User.joins(:certificates).group('certificates.id').select('certificates.id, certificates.name')

The problem here is that most of the fields I need are on the certificates_users not on the certificates table.

This is another attempt at trying to get that to work, but did not work - CertificatesUser.group(:user_id).select('user.id')

What are other possible solutions to this?

Desired Output

enter image description here

Upvotes: 0

Views: 409

Answers (3)

mayorsanmayor
mayorsanmayor

Reputation: 2988

I was able to get this to work by changing my approach and doing it the simplest way possible.

In the controller, I have this

@users_and_certificates = CertificatesUser.all.each_with_object({}) { |item, hash| hash[item.user_id] = item }

And then in the template, I query like this: 

.card-body.table-responsive
  %table.table.table-hover.table-valign-middle
    %thead
      %tr
        %th Name
        %th E-mail
    %tbody
      - @users_and_certificates.each do |user_id, certificate_object|
         %tr{"data-target" => "#certificate_#{certificate_object.certificate_id}", "data-toggle" => "collapse"}
           - user = User.find(user_id)
           %td
             = user.full_name
           %td
             = user.email
         %tr
           %td.card-body.table-responsive.collapse{'id' => "certificate_#{certificate_object.certificate_id}"}
             %table.table.table-hover.table-valign-middle
               %thead
                 %tr
                   %th Certificate
                   %th Certificate No.
                   %th Expiry Date
                   %th Certificate
               %tbody
                 %tr
                   - certificate = Certificate.find(certificate_object.certificate_id)
                   %td
                     = certificate.name
                   %td
                     = certificate_object.certificate_number
                   %td
                     = certificate_object.expiry_date
                   %td Show

This works perfectly well for now. If anyone has a better approach, please don't hesitate to leave a comment or post an answer.

Thanks.

Upvotes: 0

Shiko
Shiko

Reputation: 2624

Based on relation define in your models, below should work: 

CertificatesUser.joins(:user, :certificate)        
    .select('user_id, users.name, users.email, certificates.name')
    .group_by(&:user_id)

You use .group_by(&:user_id) to group the fetched data based on user_id in array of json like below:

Sample of result: 

[1, [{ user_id: 1, certificate_id: 29, ....},{ user_id: 1, certificate_id: 22, ....}] ,
2, [{ user_id: 2, certificate_id: 34, ....},{ user_id: 2, certificate_id: 56, ....}] ,
3, [{ user_id: 3, certificate_id: 29, ....},{ user_id: 3, certificate_id: 12, ....}] ]

NB:
You can add fields from users or certificates as you like but make sure you use them in plural and not in the singular.

Upvotes: 0

PGill
PGill

Reputation: 3521

you can do 

@users = User.includes(certificates_users: [:certificate])

then you can loop through them

@users.each do |u|
  u.certificates_users.each do |cu|
   cu.certificate_number
   cu.certificate.some_column
   ...
  end
end

to only get users with certificates

@users = User.includes(certificates_users: [:certificate]).where("certificates_users.user_id = users.id")

Upvotes: 3

Related Questions