Reputation: 557
I need a little help with Rails. I am transferring a backend system of data look up services from PHP to RoR. Which I am finding to be surprisingly concise.
I need to be able to search a model on its relation with a tag model. Each model has been given the has_and_belongs_to_many association.
When the search is passed in with a comma separated list of values I need to perform an 'AND' search on the tags so I can find all resources that have all tags passed (i.e. London AND bridges).
Also it needs to be case insensitive. Currently I have only a single tag search being performed in the Resource model.
def self.search(search)
where('LOWER(tags.name) = ?', search.downcase).joins(:tags)
end
All my attempts at using an array using either where
, find
, or all
have not been successful.
The PHP way of doing an AND search on a join table seemed rather hacky, I am hoping Rails can provide a more elegant solution.
Any help would be greatly appreciated.
Upvotes: 2
Views: 592
Reputation: 211740
If you need an AND
, then you need to ensure that the number of matching tags is precisely the number you requested, so that involves a COUNT
as well. The query you're gunning for will involve IN
.
It would be more convenient if you could ensure that your tags are always stored in lower case. Doing operations like LOWER(...)
in a query is going to be trouble if you have a lot of data since it results in an automatic table scan and cannot be indexed.
Secondly, I'd avoid using has_and_belongs_to_many
as this is a relic from the Rails 1.0 days that isn't as flexible or reliable as the has_many :through
alternative. The only difference between the two approaches from a database perspective is that the :through
join table has a unique ID for each entry and is represented by a first-class model. The has_and_belongs_to_many
system is quirky to the point of uselessness a lot of the time.
What you're probably wanting is something that looks like this:
def self.with_all_tags(*tags)
tags = tags.flatten.collect(&:downcase)
where('tags.name IN (?)', tags).having('COUNT(tags.id)=?', tags.length).joins(:tags)
end
def self.with_any_tags(*tags)
tags = tags.flatten.collect(&:downcase)
where('tags.name IN (?)', tags).joins(:tags)
end
You may need to experiment to get the right behaviour. Your example wasn't that specific.
Upvotes: 3