如何获取至少包含所有标记(has_and_belongs_to_many关系)的配置文件

Question

请帮我理解，这是怎么回事？我有与标签有has_and_belongs_to_many关系的个人资料。我希望能够过滤配置文件，其中至少包含所有标签，而不是任何一个标签。我该怎么做？

我试图这样做：

Profile.includes(:tags).where(tags: {id: array }).having('COUNT(tags) >= ?', array.count).group('profiles.id, tags.id')

但在这里我得到了非常奇怪的结果：

array = ['212', '213', '214']
=> ["212", "213", "214"]
profile.tag_ids
=> [212, 214, 213]
array = ['212', '214']
=> ["212", "214"]
irb(main):051:0> Profile.includes(:tags).where(tags: {id: array }).having('COUNT(tags) >= ?', array.count).group('profiles.id, tags.id')
SQL (0.7ms)  SELECT "profiles"."id" AS t0_r0, "profiles"."user_id" AS t0_r1, "profiles"."description" AS t0_r2, "profiles"."created_at" AS t0_r3, "profiles"."updated_at" AS t0_r4, "profiles"."first_name" AS t0_r5, "profiles"."last_name" AS t0_r6, "profiles"."date_of_birth" AS t0_r7, "profiles"."gender" AS t0_r8, "profiles"."short_time_price" AS t0_r9, "profiles"."long_time_price" AS t0_r10, "profiles"."city" AS t0_r11, "profiles"."line" AS t0_r12, "profiles"."instagram" AS t0_r13, "profiles"."facebook" AS t0_r14, "profiles"."whats_app" AS t0_r15, "profiles"."we_chat" AS t0_r16, "profiles"."other_contacts" AS t0_r17, "profiles"."geo_unit_id" AS t0_r18, "tags"."id" AS t1_r0, "tags"."body_en" AS t1_r1, "tags"."body_ru" AS t1_r2, "tags"."tags_group_id" AS t1_r3 FROM "profiles" LEFT OUTER JOIN "profiles_tags" ON "profiles_tags"."profile_id" = "profiles"."id" LEFT OUTER JOIN "tags" ON "tags"."id" = "profiles_tags"."tag_id" WHERE "tags"."id" IN ($1, $2) GROUP BY profiles.id, tags.id HAVING (COUNT(tags) >= 2) LIMIT $3  [["id", 212], ["id", 214], ["LIMIT", 11]]
=> #<ActiveRecord::Relation []>
array = ['212', '213', '214']
=> ["212", "213", "214"]
irb(main):049:0> Profile.includes(:tags).where(tags: {id: array }).having('COUNT(tags) >= ?', array.count).group('profiles.id, tags.id')
SQL (0.8ms)  SELECT "profiles"."id" AS t0_r0, "profiles"."user_id" AS t0_r1, "profiles"."description" AS t0_r2, "profiles"."created_at" AS t0_r3, "profiles"."updated_at" AS t0_r4, "profiles"."first_name" AS t0_r5, "profiles"."last_name" AS t0_r6, "profiles"."date_of_birth" AS t0_r7, "profiles"."gender" AS t0_r8, "profiles"."short_time_price" AS t0_r9, "profiles"."long_time_price" AS t0_r10, "profiles"."city" AS t0_r11, "profiles"."line" AS t0_r12, "profiles"."instagram" AS t0_r13, "profiles"."facebook" AS t0_r14, "profiles"."whats_app" AS t0_r15, "profiles"."we_chat" AS t0_r16, "profiles"."other_contacts" AS t0_r17, "profiles"."geo_unit_id" AS t0_r18, "tags"."id" AS t1_r0, "tags"."body_en" AS t1_r1, "tags"."body_ru" AS t1_r2, "tags"."tags_group_id" AS t1_r3 FROM "profiles" LEFT OUTER JOIN "profiles_tags" ON "profiles_tags"."profile_id" = "profiles"."id" LEFT OUTER JOIN "tags" ON "tags"."id" = "profiles_tags"."tag_id" WHERE "tags"."id" IN ($1, $2, $3) GROUP BY profiles.id, tags.id HAVING (COUNT(tags) >= 3) LIMIT $4  [["id", 212], ["id", 213], ["id", 214], ["LIMIT", 11]]
=> #<ActiveRecord::Relation [#<Profile id: 84, ...>]>
array = ['212', '213']
=> ["212", "213"]
irb(main):047:0> Profile.includes(:tags).where(tags: {id: array }).having('COUNT(tags) >= ?', array.count).group('profiles.id, tags.id')
=> #<ActiveRecord::Relation [#<Profile id: 84, ...>]>
array = ['214', '213']
=> ["214", "213"]
irb(main):039:0> Profile.includes(:tags).where(tags: {id: array }).having('COUNT(tags) >= ?', array.count).group('profiles.id, tags.id')
=> #<ActiveRecord::Relation [#<Profile id: 84, ...>]>

怎么可能？也许还有另一种获取配置文件的方法，其中包含所有提供的标签？

带有中间表的数据库架构在这里：

create_table "profiles_tags", force: :cascade do |t|
t.bigint "profile_id"
t.bigint "tag_id"
t.index ["profile_id"], name: "index_profiles_tags_on_profile_id"
t.index ["tag_id"], name: "index_profiles_tags_on_tag_id"
end

与关系相关的迁移在这里：

# frozen_string_literal: true
class CreateProfilesTags < ActiveRecord::Migration[6.0]
def change
create_table :profiles_tags do |t|
t.belongs_to :profile
t.belongs_to :tag
end
end
end

Answer 1

你快到了：

Profile.joins(:tags)                  # <= use `joins` instead of `inclused`
.where(tags: {id: array })
.having('COUNT(tags) >= ?', array.count)
.group('profiles.id')          # <= Do not use anything tags related here

重要的是要注意includes和joins之间的区别。虽然joins总是进行数据库联接included只在特定条件下进行联接，但有时它只进行两个查询。当 Rails 必须进行数据库联接以使查询正常工作时，请使用joins，当您想要修复 n+1 问题时，请使用includes(请参阅 Rails ：include vs. ：joins(。

当您需要两者(数据库联接和 n+1 问题的修复(时，请将联接查询作为子查询运行：

# a new scope in the mode
scope :when_tags, ->(tags) {
joins(:tags)
.where(tags: {id: tags })
.having('COUNT(tags) >= ?', tags.count)
.group('profiles.id')
}
# the actual nested query with includes:
Profile.where(id: Profile.with_tags(array)).include(:tags)