Need help using will_paginate with tagging

Rails
1

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

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:

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