A noob with a HABTM doubt

rubyonrails-talk
1

If I have the following tables:

newsletters - with id(int), title(string), content(string), created_at(date) tags - id(int), name(string), kind(int) newsletters_tags - newsletter_id(ind), tag_id(int)

And also the models Newsletter and Tag, each in a HABTM relationship with each other.

Ok, so I want to find all newsletters that share a collection of tags, using the tag ids. I tried to make the following query but it didn't work:

Newsletter.find(:all, :conditions => ["tag_id IN(?)", "12, 16"])

I would like to know whats going wrong and how could I do it.

Can you help me?

Thank you

2

The best bet would be to find the tag, then bring up the related newsletters...

@newsletters = Tag.find(params[:tag_id]).newsletters

3

Even thought I apreciate your will to help, I must say that I already did what you said and the result was different than what I really need.

Let me put it in a better way

what I really need is to retrieve the newsletters that has BOTH tags, not all newsletters that has one or another tag.

How would I do something like this?

Thank you.

4

Hmmm... not sure... could do something like

newsletters = Tag.find(params[:tag_id]).newsletters newsletters = newsletters.find_all_by_tag_id( tag_id )

That should work?

5

Well, efficiency aside, here's the logic I'd use (at least initially)

Let's say you have three tags with IDs 1,2 and 3. And you wanted to find all newsletters that had all three of those:

newsletters_one = Tag.find(1).newsletters newsletters_two = Tag.find(2).newsletters newsletters_three = Tag.find(3).newsletters

So now you have three arrays of newsletters that have each of the IDs. Then, run a set intersection on them:

has_all_three = newsletters_one & newsletters_two & newsletters_three

That will return an array of newsletters that are in all three.

Of course, that's just to be sure the logic is right. Then, you can refactor it for efficiency. For example, ditching the intermediary variables.

I hope that gets you going.

-Danimal

6

The reason your first try didn't work is you're passing a string that isn't going to be parsed by #find, when what you need is an array. This is closer to the correct syntax:

Newsletter.find(:all, :conditions => ["tag_id IN(?)", [12, 16]])

7

Danimal wrote:

So now you have three arrays of newsletters that have each of the IDs. Then, run a set intersection on them:

That is the logic I'd normally start with for something like this.

Fortunately, there are some useful plugins which can solve this sort of problem for you. One is squirrel:

To do the intersection in SQL, you need to use a HAVING clause to match the number of entries with the number of items checked against. In this case, using squirrel:

required_tags = [12, 16] amount = required_tags.length items = Newsletter.find(:all,                  :group => "newsletters.id HAVING count(*) = #{amount}") do   tags.id === required_tags end