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]])

yfeldblum · Answer 1 · 2010-05-10T10:41:20.250

4

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

Also take a look at the guides:

edited May 10 '10 at 10:41

answered May 09 '10 at 18:55

yfeldblum

65,165
12
129
169

That works, thanks. But what if I have one more many-to-many association (let's say Classrooms) to that Post and I wanna find both by Category and by Classroom? Something like Post.where(["category.id = ? AND classroom.id = ?", params[:category_id], params[:classroom_id]]) – Sergey May 09 '10 at 19:04
How about this: Post.where(["category_id =?", params[:category_id]).where(["classroom_id =?", params[:classroom_id]]) – Chap May 09 '10 at 22:59
@chap I don't have those fields in Post table. That's why I am asking. Please read the question. – Sergey May 10 '10 at 09:28
Your answer will only help finding Post which belong to a certain category, and will not filter it by a classroom too. – jake Sep 30 '11 at 11:17

jbescoyez · Answer 2 · 2014-09-08T12:44:17.897

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.

score 2 · Answer 3 · edited May 23 '17 at 12:12

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.

score 0 · Answer 4 · answered Sep 30 '11 at 03:34

Sounds like you need an if statment.

if params[:category_id] && params[:classroom_id]
  Post.joins(:categories, :classrooms).where("classrooms.id" => params[:classroom_id], "categories.id" => params[:category_id]])
elsif params[:category_id]
  Category.find(params[:category_id]).posts
else
  Classroom.find(params[:classroom_id]).posts
end

Rails 3 find all associated records has_many :through

4 Answers4