Rails 3 find all associated records has_many :through

Question

I would like to list all posts that are connected with some specific category and classroom.

I have:

class Post < ActiveRecord::Base
  has_many :category_posts
  has_many :categories, :through => :category_posts
  has_many :classroom_posts
  has_many :classrooms, :through => :classroom_posts
end

class Category < ActiveRecord::Base
  has_many :category_posts
  has_many :posts, :through => :category_posts
end

class CategoryPost < ActiveRecord::Base
  belongs_to :category
  belongs_to :post
end

class Classroom < ActiveRecord::Base
  has_many :classroom_posts
  has_many :posts, :through => :classroom_posts
end

class ClassroomPost < ActiveRecord::Base
  belongs_to :classroom
  belongs_to :post
end

And I wanna do something like this

Post.where(["category.id = ? AND classroom.id = ?", params[:category_id], params[:classroom_id]])

It indeed is very simple task, but I don't know what I should be looking for (keywords).

It's the same problem like this, but in rails.

EDIT: I added more details to the question. This works, but only if I have both params specified. Witch is not always the case - I dont know what params would be specified.

Post.joins(:categories, :classrooms).where(["categories.id = ? AND classrooms.id = ?", params[:classroom_id], params[:category_id]])

Answer 1

Category.find(params[:category_id]).posts

Also take a look at the guides:

• Guides for Rails 2.35
• Guides for Rails 3.0

Answer 2

Here is what I would do in Rails 3:

In post.rb:

def self.in_category(category_id)
  if category_id.present?
    join(:category_posts).where(category_posts: {category_id: category_id})
  else
    self
  end
end

def self.in_classroom(classroom_id)
  if classroom_id.present?
    join(:classroom_posts).where(classroom_posts: {classroom_id: category_id})
  else
    self
  end
end

I do not join Classroom or Category since it makes more work for DBMS and this is not required.

Now, you can do:

Post.in_category(params[:category_id]).in_classroom(params[:classroom_id])

I haven't tested it though. So do not hesitated to ask if needed.

Answer 3

I think that should work:

Post.joins(:category_posts, :classroom_posts)
.where(
["category_posts.category_id = ? 
AND classroom_posts.classroom_id = ?", params[:category_id], params[:classroom_id]])

This will traslate to a SQL like :

SELECT 
    p.*
FROM
    posts AS p
        INNER JOIN
    category_posts AS cap ON cap.id = p.category_posts_id
        INNER JOIN
    classroom_posts AS clp ON clpid = p.classroom_posts_id
WHERE
    cap.category_id = '1' AND clp.classroom_id = '1'
;

As to whether to use :include or joins on Post look at this answer on stackoverflow.