need help using will_paginate with tagging

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

Vince,

I think you want to do a join like this

Articles.find(:all, :conditions => ...,     :joins => "LEFT JOIN authors ON articles.author_id=authors.id")

thank you can you can use those table(s) in your :conditions

Arshak

vince wrote: