我有一个包含这些当前值的数据库:
User
id | name
1 | Sara
2 | Alice
3 | Samantha
UserTag
id | user_id | label | value
1 | 1 | hair_color | blonde
2 | 1 | polite | no
3 | 2 | hair_color | brunette
4 | 2 | polite | yes
5 | 3 | hair_color | brunette
6 | 3 | polite | no
以及相关模型:
class User < ApplicationRecord
has_many :tags,
class_name: 'UserTag',
foreign_key: :user_id,
dependent: :destroy,
inverse_of: :user
end
class UserTag < ApplicationRecord
belongs_to :user, inverse_of: :tags, touch: true
end
我想找到所有"不礼貌"的"黑发"用户,基本上是"萨曼莎"。 我尝试了以下方法,但没有成功:
# Returns 0
User.joins(:tags)
.where(user_tags: { label: 'hair_color', value: 'brunette' })
.where(user_tags: { label: 'polite', value: 'no' })
.count
# .to_sql
"SELECT "users".* FROM "users" INNER JOIN "user_tags" ON "user_tags"."user_id" = "users"."id" WHERE "user_tags"."label" = 'hair_color' AND "user_tags"."value" = 'brunette' AND "user_tags"."label" = 'polite' AND "user_tags"."value" = 'no'"
我做错了什么?
如果你真的想建立一个标签系统,而不仅仅是一个EAV怪物,这就是你的做法。
首先创建一个规范化的标签表和一个user_tags连接表:
class CreateTags < ActiveRecord::Migration[6.0]
def change
create_table :tags do |t|
t.string :name, unique: true
t.timestamps
end
end
end
class CreateUserTags < ActiveRecord::Migration[6.0]
def change
create_table :user_tags do |t|
t.references :user, null: false, foreign_key: true
t.references :tag, null: false, foreign_key: true
t.timestamps
end
add_index [:user_id, :tag_id], unique: true
end
end
然后设置关联:
class Tag < ApplicationRecord
has_many :user_tags
has_many :users, through: :user_tags
validates_uniqueness_of :name
end
class UserTag < ApplicationRecord
belongs_to :user
belongs_to :tag
validates_uniqueness_of :tag_id, scope: :user_id
end
class User < ApplicationRecord
has_many :user_tags
has_many :tags, through: :user_tags
end
要查询具有多个标签的用户,您可以执行以下操作:
User.joins(:tags)
.where(tags: { name: ['Brunette', 'Impolite'] } )
.group('users.id')
.having('count(*) = 2')
您也可以将其滚动到类方法中:
class User < ApplicationRecord
has_many :user_tags
has_many :tags, through: :user_tags
def self.with_tags(*tags)
raise ArgumentError, 'must pass more than one tag' if tags.none?
self.joins(:tags)
.where(tags: { name: tags } )
.group('users.id')
.having('count(*) = ?', tags.length)
end
end