ActiveRecord AND查询参数数组

Question

我有以下模型:

class Fruit < ApplicationRecord
has_many :taggings, as: :tagable, dependent: :destroy
has_many :tags, through: :taggings
class Tagging < ApplicationRecord
belongs_to :tag
belongs_to :tagable, polymorphic: true
class Tag < ApplicationRecord
has_many :taggings, dependent: :destroy
has_many :fruits,
through: :taggings,
source: :tagable,
source_type: 'Fruit'

假设我有一个水果"香蕉"，标签为"黄色"(id: 182)和"长"(id: 168)。我需要一个查询，会给我水果与'黄'和'长'标签。

Banana确实有两个标签。

Fruit.find(97).tags
Fruit Load (4.1ms)  SELECT "fruits".* FROM "fruits" WHERE "fruits"."id" = $1 ORDER BY name LIMIT $2  [["id", 97], ["LIMIT", 1]]
Tag Load (7.4ms)  SELECT "tags".* FROM "tags" INNER JOIN "taggings" ON "tags"."id" = "taggings"."tag_id" WHERE "taggings"."tagable_id" = $1 AND "taggings"."tagable_type" = $2  [["tagable_id", 97], ["tagable_type", "Fruit"]]

=比;【# & lt;标签:0 x000000010d50e9d0id: 168年,名称:"long",created_at: Wed, 22 Dec 2021 16:54:32.290923000 UTC +00:00，updated_at:星期三，2021年12月22日16:54:32.290923000 UTC +00:00># & lt; x000000010d50e868标签:0id: 182年,名称:"yellow",created_at: Mon, 18 Apr 2022 16:59:30.244851000 UTC +00:00，updated_at: Mon, 18 Apr 2022 16:59:30.244851000 UTC +00:00>]

这样问:

irb(main):063:0> Fruit.joins(:tags).where(tags: { id: [168,182]}).to_sql

会得到我

"SELECT "fruits".* FROM "fruits" INNER JOIN "taggings" ON "taggings"."tagable_type" = 'Fruit' AND "taggings"."tagable_id" = "fruits"."id" INNER JOIN "tags" ON "tags"."id" = "taggings"."tag_id" WHERE "tags"."id" IN (168, 182) ORDER BY name"

表示带有任意标签的所有水果。id 168 OR 182。所以不行。

我试过了

Fruit.joins(:tags).where(tags: { id: 168 }).and(where(tags: { id: 182 }))

将得到空结果。

Fruit Load (2.0ms)  SELECT "fruits".* FROM "fruits" INNER JOIN "taggings" ON "taggings"."tagable_type" = $1 AND "taggings"."tagable_id" = "fruits"."id" INNER JOIN "tags" ON "tags"."id" = "taggings"."tag_id" WHERE "tags"."id" = $2 AND "tags"."id" = $3 ORDER BY name  [["tagable_type", "Fruit"], ["id", 168], ["id", 182]]
=> []

空的。

Also I've try

f = Fruit.joins(:tags)
tags = [168,182]
tags.each do |tag|
f = f.where(tags: {id: tag})
end
Fruit Load (22.3ms)  SELECT "fruits".* FROM "fruits" INNER JOIN "taggings" ON "taggings"."tagable_type" = $1 AND "taggings"."tagable_id" = "fruits"."id" INNER JOIN "tags" ON "tags"."id" = "taggings"."tag_id" WHERE "tags"."id" = $2 AND "tags"."id" = $3 ORDER BY name  [["tagable_type", "Fruit"], ["id", 168], ["id", 182]]

再一次，悲伤的故事，空洞的结果。

请求一个标签。id工作，为一个或另一个工作，以及，但我找不到一种方法来要求两个标签在同一时间，并得到结果。

谢谢

Answer 1

您需要的是一个SQL GROUP BY，基本上选择所有具有任何一个标签的水果，然后按水果id对它们进行分组，并过滤所有出现多次的水果。据我所知，这不能用一个SQL查询完成，而是用两个(查询和子查询)，这可能会让ActiveRecord变得乏味。

用Ruby来完成大部分艰苦的工作怎么样?

fruit = Fruit.joins(:tags).where(tags: { id: [168,182]}).to_a # returns the fruit that have both 2 tags
both_tags_ids = fruit.group_by { |f| f.id }.select { |fruit_id, arr|
arr.count > 1
}.values.flatten