Hi -
I am trying to join 3 models together while using :conditions , and have hit a snag.
Basically, I'm trying to limit the returned Items to those with a specific zipcode (zipcode is a field of a Location, each Item can have many Locations), AND those WITHOUT a specific Tag.name, if the Tag was created by the User doing the search (Tags belong to Users and Items and have user_id and item_id).
Here are the snags: if I use :joins, then I only get back Items that have a Tag, when I really want any Item, just exclude those with a particular Tag.name.
If I use :include to include the Tags, then Tags are LEFT OUTER JOINed on the item.id. While I can filter out those where the tag=hidden and userid=[someid], the LEFT OUTER JOIN isn't quite right b/c if multiple users have tagged the item, I will likely not join in the row from the user doing the search. That is, if 10 people tagged the item as hidden, then the LEFT OUTER JOIN may bring in the tag for user #4, not user #8 who is doing the search. In that case, filtering by userid/tag won't work.
I got the behavior I wanted by writing the JOINs myself like so for the Items.paginate:
INNER JOIN items_locations ON items_locations.item_id = items.id
INNER JOIN locations ON locations.id = items_locations.location_id
LEFT OUTER JOIN
(Select * from tags where tags.name = 'hidden' AND tags.user_id=[theuserID]) AS t
ON t.item_id = items.id
The I use that for the :joins parameter, and have part of the condition as
(t.name IS NULL)
so that I LEFT OUTER JOIN in only the tag=hidden, for the current user, then filter on the tag.name being empty.
This feels wrong for some reason, or at least seems like there may be a way to do this without forcing the JOIN in that particular fashion so rails could break it up and optimize.
Has anyone come across this issue and solved this in a different way?