I have a simple tagging system:
places(id, title) taggings(id, place_id, tag_id) tags(id, title)
I can do the following sql to find places that are tagged as 'cheap' AND tagged as 'sushi':
select * from places inner join taggings on taggings.place_id = places.id and taggings.tag_id in (select id from tags where title = 'cheap' or title = 'sushi') group by places.id having count(*) = 2
I'm trying to do the same or similar query using will_paginate - can someone help? This sort of works:
Place.paginate(:per_page => 10, :page => 1, :include => [:taggings, :tags], :conditions => "taggings.tag_id = tags.id and taggings.tag_id in (select id from tags where title = 'cheap' or title = 'sushi')", :group => "places.id having count(*) = 2")
The problem with this query is that if a certain tag does not exist I get a Mysql error. For example if someone searches with:
Place.paginate(:per_page => 10, :page => 1, :include => [:taggings, :tags], :conditions => "taggings.tag_id = tags.id and taggings.tag_id in (select id from tags where title = 'cheap' or title = 'asdfsadf')", :group => "places.id having count(*) = 2")
It gives, ActiveRecord::StatementInvalid: Mysql::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'having count(*) = 2