CODEWITHSUNDEEP
ruby-on-railsrubyactiverecord
Jeremie Ges
Jeremie Ges

Reputation: 2743

How to use an ActiveRecord query to find records based on association tags

I have a database with these current values:

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

And the associated models:

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

I want to find all the "brunette" users who are "not polite", basically, "Samantha". I tried the following without success:

# 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'"

What am I doing wrong?

Upvotes: 1

Views: 116

Answers (1)

max
max

Reputation: 102423

If you really want to build a tag system and not just a EAV monstrosity this is how you do it.

Start by creating a normalised tags table and a user_tags join table:

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

Then setup the associations:

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

To query a user with multiple tags you can then do:

User.joins(:tags)
    .where(tags: { name: ['Brunette', 'Impolite'] } )
    .group('users.id')
    .having('count(*) = 2')

You could also just roll this into a class method:

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

Upvotes: 1

Related Questions