Rails double match from has_and_belongs_to_many

Question

Say that I have a has_and_belongs_to_many relationship where I have posts and categories. It is simple to find all the posts in a category, or all the categories that a particular post is a member of. However, what if I want to find a list of posts that belong to multiple categories? For example, a list of posts that are on the topic of security in Rails, I might want the posts that belong to the categories "Security" and "Rails".

Is it possible to do this with the finder methods build into ActiveRecord, or will I need to use SQL? Can someone please explain how?

This should help you out: http://stackoverflow.com/questions/3639656/activerecord-or-query — Benjamin Tan Wei Hao, Jul 22 '12 at 09:03
possible duplicate of [Rails: join with multiple conditions](http://stackoverflow.com/questions/5376869/rails-join-with-multiple-conditions) — Thilo, Oct 19 '12 at 19:34

score 0 · Answer 1 · edited Jul 22 '12 at 09:17

0

You can use includes or joins, like:

@result = Post.includes(:categories).where("categories.name = 'Security' OR categories.name = 'Rails'")

or

@result = Post.joins(:categories).where("categories.name = 'Security' OR categories.name = 'Rails'")

I also suggest to check this railscast to understand the difference between joins and includes, so you can decide what is better in your case.

edited Jul 22 '12 at 09:17

Baldrick

23,882
6
74
79

answered Jul 22 '12 at 09:03

gabrielhilal

10,660
6
54
81

Well, I meant to ask multiple categories *at the same time* so only posts that belong to categories "Security" AND "Rails". However, when I use the examples above, substituting AND or OR, I get no results. – John Jul 22 '12 at 09:19
`name` on `categories.name` is just an example. You need to replace it by the correct column name. Please check your log file to see the SQL generated by this ruby query. You can also test it in parts, for example: `@result = Category.where("name = 'Security' AND name = 'Rails'")`, so you can make sure that it is returning only categories matching the criteria. Also make sure you have this categories on the database, otherwise it will return nothing.. – gabrielhilal Jul 22 '12 at 09:41
Yes I've checked all of that. `Category.where("name = 'Security' AND name = 'Rails'")` would return only categories where the name was both "Security" and "Rails" at the same time, which makes no sense. All posts can have multiple categories, I need a way to find all of the posts that are in both of two separate categories, "Security" and "Rails". Posts and categories such as this already are in my database. – John Jul 23 '12 at 21:01

score 0 · Answer 2 · answered Oct 19 '12 at 19:30

i don't know anything about rails, but i'm attempting a similar thing with some sql. this may or may not work for either of us....

i have a table of articles, and a look-up table of applied categories. to get an article that has the 'security' category and the 'rails' category, i'm joining the article table to the category table, of course, but also re-joining it a second time. each join of the category table uses a hint in the table alias name (ie language or topic) pseudo code:

SELECT article.*,
category_language.category_id,
category_topic.category_id

FROM category category_language
INNER JOIN article ON category_language.articleID = article.articleID 
INNER JOIN category category_topic ON article.articleID = category_topic.articleID 

WHERE category_language.category_id in (420) /* rails */
and category_topic.category_id in (421) /* security */

this isn't completely ironed out, and i hope that if i am showing my ignorance here, someone will speak up.

Rails double match from has_and_belongs_to_many

2 Answers2