ruby-on-rails - 为什么 group 或 group_by 不适用于此版本的 rails 表？

Question

我使用以下代码进行了 Rails 迁移

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

在证书模型和用户模型中，我有has_many :certificates_users

这使我能够将证书添加到用户的配置文件中。目前，我有 2 个用户，我为他们都添加了证书。

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>]>  

为清楚起见，这里是certificates_users 表。

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

一个用户有 2 个证书，另一个用户有一个。

我的目标是能够列出拥有某种证书类型的用户。

1. 列出拥有 ID 为 1 的证书的用户(应该列出这两个用户)。 - 下拉列表 1
2. 仅列出拥有 ID 为 1 的证书和 ID 为 2 的证书的用户(应该只列出 1 个用户)。 - 下拉菜单 2
3. 当从他们单独的下拉列表中选择该用户中的任何一个时，在另一个下拉列表中列出他们的证书。

我曾尝试使用 group 和 group_by 来实现此结果，但没有成功。我的想法是，我正在尝试按 user_id 对 CertificatesUser 进行分组，但这没有用。它返回了这个错误:

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

然后我使用 group 方法得到了这个:

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

知道如何让这项工作成功吗？

Answer 1

简答

我不明白为什么你需要对任何东西进行分组。我会使用子查询来解决这个问题。

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)

最后一个问题可能值得一个单独的问题。不过，我会提到您可以执行以下操作。

# 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

动态证书 ID

在评论中，您询问了如何使此答案适用于动态证书 ID。对于上面给出的答案，这可以通过以下方式完成。

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

结果查询应该看起来像这样(对于 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
  )

优化查询

虽然上面的例子向我们展示了如何让整个事情变得动态。对大量证书 ID 执行此操作不会产生最有效的查询。

解决此问题的另一种方法确实涉及分组，并且执行起来稍微困难一些。这包括在 certificates_users 表中搜索与给定 ID 之一匹配的记录。然后统计每个用户的记录量。如果记录的数量等于给定的证书 ID 的数量，则表示用户拥有所有这些证书。

先决条件

但是，此方法有先决条件。

• user_id 和 certificate_id 的组合在 certificates_users 表中必须是唯一的。您可以通过对这些列设置唯一约束来确保是这种情况。

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

• 另一个可选建议是 certificates_users 表中的列 user_id 和 certificate_id 不能为 NULL。您可以通过使用

  创建列来确保是这种情况

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

  或通过更改列使用

  change_column_null :certificates_users, :user_id, false
  

  当然，对于 certificate_id 列也必须重复上述示例。

优化

先决条件已准备就绪，让我们来看看这个解决方案。

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

以上示例应生成以下查询(对于 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

当增加 certificate_ids 数组时，查询保持不变，除了以下两部分。

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

引用资料

所使用的大部分代码不言而喻。如果您还不熟悉 reduce，我建议您看一下 documentation .由于此方法存在于许多其他编程语言中。这是您的武器库中的一个很好的工具。

arel gem API 并不是真的要在 Rails 内部之外使用。由于这个原因，它的文档非常糟糕。但是，您可以找到大多数使用的方法 here .