如何获取至少包含所有标记的配置文件(has \u and \u beliens \u to \u 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, ...>]>

怎么可能？也许还有另一种方法来获取包含所有提供的标签的概要文件？
带中间表的db模式：

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

你就快到了：

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 只在某些条件下进行连接，有时只进行两个查询。使用 joins 当rails必须使数据库连接以使查询工作并使用 includes 当您想要修复n+1问题时（请参阅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)

如何获取至少包含所有标记的配置文件(has \u and \u beliens \u to \u many关系)

1条答案

相关问题

热门标签

最新问答