2

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?

leppie
  • 115,091
  • 17
  • 196
  • 297
Varun
  • 675
  • 6
  • 16

2 Answers2

3

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.

mu is too short
  • 426,620
  • 70
  • 833
  • 800
  • but that will just sort by number of tags the objects have, I want to sort it by number of tags matched. so if obj1 is tagged with "hello, america, canada, iphone" and obj2 is tagged with "hello, world" and i do obj.tagged_with("hello, world") it should return (in this order) obj2, obj1 (since obj2 has highest number of tags matched) – Varun Jan 14 '11 at 05:24
  • 1
    Is there no way to do this on the SQL side? This method works but it's not very efficient – bodacious Jun 15 '11 at 09:31
  • @bodacious: AFAIK, If you want to do it in the database you'll have to go behind acts-as-taggable's back and start slinging SQL around. How good is your SQL? – mu is too short Jun 15 '11 at 09:52
1

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.

Noor
  • 13
  • 1
  • 4