CODEWITHSUNDEEP
rubyacts-as-taggable
Varun
Varun

Reputation: 665

how to find number of tag matches in acts as taggable on

I have two entries in my database

Obj1 is tagged with "hello, world, planet" Obj2 is tagged with "hello"

if I do modelName.tagged_with(["hello", "world", "planet", "earth"], :any=>true)

I want to sort the returned objects in order of highest to lowest number of tags matched. so in this case i'd like the order to be Obj1, Obj2

how can I do this? is there a way to get number of tags matched for each of the returned results?

Upvotes: 2

Views: 516

Answers (2)

Noor
Noor

Reputation: 13

Posting this here if someone else is looking for a way to query a model by tags and order by the number of matches. This solution also allows for the usage of any "equality" operator like the % from pg_trgm.

query = <<-SQL
SELECT users.*, COUNT(DISTINCT taggings.id) AS ct
FROM users
INNER JOIN taggings ON taggings.taggable_type = 'User'
AND taggings.context = 'skills'
AND taggings.taggable_id = users.id
AND taggings.tag_id IN 
(SELECT tags.id FROM tags
WHERE (LOWER(tags.name) % 'ruby'
OR LOWER(tags.name) % 'java'
OR LOWER(tags.name) % 'sa-c'
OR LOWER(tags.name) % 'c--'
OR LOWER(tags.name) % 'gnu e'
OR LOWER(tags.name) % 'lite-c'
))
GROUP BY users.id
ORDER BY ct DESC;
SQL

User.find_by_sql(query)

Note that the code above will only work if you have pg_trgm enabled. You can also simply replace % with ILIKE.

EDIT: With ActiveRecord and eager loading:

This could be in a scope or class method and can be chained with other ActiveRecord methods.

ActiveRecord::Base.connection
  .execute('SET pg_trgm.similarity_threshold = 0.5')

matches = skills.map do
  'LOWER(tags.name) % ?'
end.join(' OR ')

select('users.*, COUNT(DISTINCT taggings.id) AS ct')
  .joins(sanitize_sql_array(["INNER JOIN taggings
    ON taggings.taggable_type = 'User'
    AND taggings.context = 'skills'
    AND taggings.taggable_id = users.id
    AND taggings.tag_id IN
      (SELECT tags.id FROM tags WHERE (#{matches}))", *skills]))
    .group('users.id')
    .order('ct DESC')
    .includes(:skills)

Override skill_list from acts-as-taggable-on in the model:

def skill_list
  skills.collect(&:name)
end

and proceed normally.

Upvotes: 1

mu is too short
mu is too short

Reputation: 434585

You can call tag_list on the objects and use that to figure out how many tags there are:

tags = %w{hello world planet earth}
objs = ModelName.taggedWith(tags, :any => true)
objs.sort_by! { |o| -(tags & o.tag_list).length }

The tags & o.tag_list yields the intersection of the tags you're looking for and the tags found, then we negate the size of the intersection to tell sort_by (which sorts in ascending order) to put larger intersections at the front, negating the result is an easy way to reverse the usual sort order.

Upvotes: 3

Related Questions