CODEWITHSUNDEEP
sqlruby-on-railspostgresql
everyday_potato
everyday_potato

Reputation: 113

Filter users with a join table

I have tables for users, tags, and join table taggings.

I'm currently using scope to filter users through different variables, for example:

scope = scope.where('users.created_at > :created_after', created_after: params[:created_after])

I am trying to filter users by tags assigned to them (through a has many relationship).

In user model:

has_many :taggings, as: :taggable
has_many :tags, through: :taggings

User table:

╔════╦══════════════╦
║ id ║  name        ║
╠════╬══════════════╬
║  1 ║ Picard       ║ 
║  2 ║ Kirk         ║
║  3 ║ Sisko        ║
║  4 ║ Janeway      ║
╚════╩══════════════╩

Tags table:

╔════╦══════════════╦
║ id ║  Tag         ║ 
╠════╬══════════════╬
║  1 ║ TOS          ║  
║  2 ║ TNG          ║ 
║  3 ║ DS9          ║ 
║  4 ║ VOY          ║ 
╚════╩══════════════╩

Taggins table:

╔════╦═══════════╦═══════════════╦═══════════════════════╦
║ id ║  tag_id   ║ taggable_id   ║ taggable_type         ║ 
╠════╬═══════════╬═══════════════╬═══════════════════════╣
║  1 ║ 1         ║    1          ║  User                 ║
║  2 ║ 1         ║    4          ║  Organization         ║
║  3 ║ 2         ║    2          ║  User                 ║
║  4 ║ 1         ║    3          ║  User                 ║
╚════╩═══════════╩═══════════════╩═══════════════════════╝

I've tried something like:

SELECT * FROM users u
  JOIN taggings j, tags t
  WHERE t.tag = <tag>
    AND t.id = j.tag_id
    AND j.taggable_type = 'User'
    AND u.id = j.taggable_id

Upvotes: 0

Views: 74

Answers (1)

max
max

Reputation: 102055

You can get a count and use having to stipulate that the user must have one or more tags:

tags = ["TOS", "TNG"]
User.joins(:tags)
    .where(tags: { tag: tags })
    .group(:id)
    .having(
      # Arel for "COUNT(tags.*) >= ?"
      Tag.arel_table[Arel.star].count.gte(tags.length)
    )

Since you're using a polymorpic assocation you probaly want to bake this into a module so that you can reuse it:

module Taggable
  extend ActiveSupport::Concern
  included do
    has_many :taggings, as: :taggable
    has_many :tags, through: :taggings
  end

  class_methods do
    def with_tags(*tags) 
      joins(:tags)
        .where(tags: { tag: tags })
        .group(:id)
        .having(  
          Tag.arel_table[Arel.star]
             .count.gteq(tags.length)
        )
    end
  end
end

class User < ApplicationRecord
  include Taggable
end

Upvotes: 1

Related Questions