我有一个具有这些当前值的数据库:
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
我想找到所有“不礼貌”的用户,基本上就是“ Samantha”。我尝试了以下操作,但未成功:
# 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
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句