Why does group or group_by not work on this version of rails table?

Question

I have this rails migration with the following code

class CreateCertificatesUsers < ActiveRecord::Migration[5.2]
  def change
    create_table :certificates_users do |t|
      t.references :user, foreign_key: true
      t.references :certificate, foreign_key: true
    end
  end
end

In Certificate model and User model, I have has_many :certificates_users

This allows me to be able to add a certificate to a user's profile. Currently, I have 2 users, and I have added Certificate for both of them.

2.5.3 :010 > CertificatesUser.all
  CertificatesUser Load (0.6ms)  SELECT  "certificates_users".* FROM "certificates_users" LIMIT $1  [["LIMIT", 11]]
 => #<ActiveRecord::Relation [#<CertificatesUser id: 8, user_id: 1, certificate_id: 1, expiry_date: "2020-01-14", certificate_number: "1122", renewal_date: "2019-01-14", ispublic: 1>, #<CertificatesUser id: 9, user_id: 2, certificate_id: 1, expiry_date: "2020-01-16", certificate_number: "123", renewal_date: "2019-01-16", ispublic: 1>, #<CertificatesUser id: 10, user_id: 2, certificate_id: 2, expiry_date: "2019-02-28", certificate_number: "123", renewal_date: "2019-01-16", ispublic: 1>]>  

Here is the certificates_users table for clarity.

id | user_id | certificate_id | ...
---+---------+----------------+----
 8 |       1 |              1 |
 9 |       2 |              1 |
10 |       2 |              2 |

One User has 2 certificates and the other has one.

My goal is to be able to list out Users who have a certain type of certificate.

1. List out users who have a certificate with the id of 1 (should list out both users). - Dropdown 1
2. List out only users who have both certificates with the id of 1 and certificate with the id of 2 (should list out only 1 user). - Dropdown 2
3. When any of this user is selected from their separate dropdowns list out their certificate in another dropdown.

I have tried to use group and group_by to achieve this result but it did not work. The idea is that I am trying to group the CertificatesUser by user_id but that didn't work. It returned this error:

2.5.3 :011 > CertificatesUser.group_by(&:user_id)
Traceback (most recent call last):
        1: from (irb):11
NoMethodError (undefined method `group_by' for #<Class:0x00007fa8dda7c668>)
Did you mean?  group

Then I used group method and got this:

2.5.3 :012 > CertificatesUser.group('users.id')
  CertificatesUser Load (7.2ms)  SELECT  "certificates_users".* FROM "certificates_users" GROUP BY users.id LIMIT $1  [["LIMIT", 11]]
Traceback (most recent call last):
ActiveRecord::StatementInvalid (PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "users")
LINE 1: ...cates_users".* FROM "certificates_users" GROUP BY users.id L...
                                                             ^
: SELECT  "certificates_users".* FROM "certificates_users" GROUP BY users.id LIMIT $1

Any idea how I can make this work?

Answer 1

Short Answer

I fail to see why you need to group anything at all. I would solve this issue with the use of sub-queries.

certificate_1_user_ids = CertificatesUser.select(:user_id).where(certificate_id: 1)
certificate_2_user_ids = CertificatesUser.select(:user_id).where(certificate_id: 2)

# List out users who have a certificate with the id of 1.
users_with_certificate_1 = User.where(id: certificate_1_user_ids)

# List out only users who have both certificates with the
#   id of 1 and certificate with the id of 2.
users_with_certificate_1_and_2 = User.where(id: certificate_1_user_ids)
                                     .where(id: certificate_2_user_ids)

The last question might be worth a separate question. However I will mention that you can do the following.

# with the following associations present in app/models/user.rb
has_many :certificates_users
has_many :certificates, through: :certificates_users

# you can eager load associations to avoid the 1+N problem
users.includes(:certificates).each do |user|
  # do stuff with user

  user.certificates.each do |certificate|
    # do stuff with certificate
  end
end

Dynamic Certificate IDs

In the comments you asked how to make this answer work for dynamic certificate IDs. For the answer given above this can be done in the following way.

certificate_ids = [1, 2]

users = certificate_ids.reduce(User) do |scope, certificate_id|
  user_ids = CertificatesUser.select(:user_id).where(certificate_id: certificate_id)
  scope.where(id: user_ids)
end

The resulting query should look something like this (for MySQL).

SELECT `users`.*
FROM `users`
WHERE
  `users`.`id` IN (
    SELECT `certificates_users`.`user_id`
    FROM `certificates_users`
    WHERE `certificates_users`.`certificate_id` = 1
  )
  AND `users`.`id` IN (
    SELECT `certificates_users`.`user_id`
    FROM `certificates_users`
    WHERE `certificates_users`.`certificate_id` = 2
  )

Optimizing the Query

Although the above example shows us how to make the whole thing dynamic. Doing this for larger amounts of certificate IDs doesn't produce the most efficient query.

Another way of approaching this does involve grouping and is a slightly more difficult to execute. This involves searching through the certificates_users table for records that match one of the given IDs. Then count the amount of records for each user. If the amount of records equals the amount of certificate IDs given it means that an user has all those certificates.

Prerequisites

This method does however come with prerequisites.

• The combination of user_id and certificate_id must be unique in the certificates_users table. You can make sure that this is the case by placing an unique constraint over those columns.

  add_index :certificates_users, %i[user_id certificate_id], unique: true
  

• Another optional recommendation is that the columns user_id and certificate_id in the certificates_users table cannot be NULL. You can make sure this is the case by creating the columns using

  t.references :user, foreign_key: true, null: false
  

  or by changing the column using

  change_column_null :certificates_users, :user_id, false
  

  Of course the above example must be repeated for the certificate_id column as well.

Optimization

With the prerequisites out of the way, lets have a look at this solution.

certificates_users = CertificatesUser.arel_table
certificate_ids = [1, 2]

users = User.joins(:certificates_users)
            .where(certificate_users: { certificate_id: certificate_ids })
            .group(:id)
            .having(certificates_users[:id].count.eq(certificate_ids.size))

# Nested sections in a where always require the table name, not the
#   association name. Here is an example that makes it more clear.
#
#     Post.joins(:user).where(users: { is_admin: true })
#                  ^           ^- table name
#                  +-- association name

The above example should produce the following query (for MySQL).

SELECT `users`.*
FROM `users`
  INNER JOIN `certificates_users`
    ON `users`.`id` = `certificates_users`.`user_id`
WHERE `certificates_users`.`certificate_id` IN (1, 2)
GROUP BY `users`.`id`
HAVING COUNT(`certificates_users`.`id`) = 2

When increasing the certificate_ids array the query stays the same, other than the following two parts.

• IN (1, 2) becomes IN (1, 2, 4, 7, 8)
• COUNT(...) = 2 becomes COUNT(...) = 5

References

Most of the code used speaks for itself. If you're not yet familiar with reduce I recommend taking a look at the documentation. Since this method is present in a lot of other programming languages. It's a great tool to have in your arsenal.

The arel gem API isn't really meant for use outside of the internals of Rails. For this reason it's documentation is pretty bad. However you can find most of the methods used here.