CODEWITHSUNDEEP
sqlruby-on-railsruby-on-rails-3postgresqlactiverecord
Sasha
Sasha

Reputation: 6466

Rails HABTM query -- Article with ALL tags

I created two tables in my app (Rails 3):

def change
  create_table :articles do |t|
    t.string :name
    t.text :content
    t.timestamps
  end

  create_table :tags do |t|
    t.string :name
    t.timestamps
  end

  create_table :articles_tags do |t|
    t.belongs_to :article
    t.belongs_to :tag
  end

  add_index :articles_tags, :article_id
  add_index :articles_tags, :tag_id
end

I want to be able to search for articles based on tags in two ways:

  1. Articles with ANY of the given tags (union)
  2. Articles with ALL of the given tags (intersection)

So, in other words, something that allows me to do this this:

tag1 = Tag.create(name: 'tag1')
tag2 = Tag.create(name: 'tag2')

a = Article.create; a.tags << tag1
b = Article.create; b.tags += [tag1, tag2]

Article.tagged_with_any(['tag1', 'tag2'])
# => [a,b]

Article.tagged_with_all(['tag1', 'tag2'])
# => [b]

The first one was relatively easy. I just made this scope on Article:

scope :tagged_with_any, lambda { |tag_names|
  joins(:tags).where('tags.name IN (?)', tag_names)
}

The problem is the second. I have no idea how to do this, in ActiveRecord or SQL.

I figure that I might be able to do something icky like this:

scope :tagged_with_all, lambda { |tag_names|
  new_scope = self

  # Want to allow for single string query args
  Array(tag_names).each do |name|
    new_scope = new_scope.tagged_with_any(name)
  end
  new_scope
}

but I'm betting that's crazy inefficient, and it just smells. Any ideas about how to do this correctly?

Upvotes: 1

Views: 286

Answers (1)

carpamon
carpamon

Reputation: 6633

As you said, that scope is crazy inefficient (and ugly).

Try with something like this:

def self.tagged_with_all(tags)
  joins(:tags).where('tags.name IN (?)', tags).group('article_id').having('count(*)=?', tags.count).select('article_id')
end

The key is in the having clause. You may also want to have a look at the SQL division operation between tables.

Upvotes: 1

Related Questions