A noob with a HABTM doubt

Rails
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