CODEWITHSUNDEEP
joinruby-on-rails-3.2has-many-through
pedrogrande
pedrogrande

Reputation: 43

Query with three way association / has_many :through / join

I'm teaching myself Rails using the guides/apis/books available but I'm having trouble understanding joins with three way/nested has_many :through associations.

I have users connected with groups :through memberships.

I also have posts in a many-to-many with groups. The same post can be posted to many groups + groups can have many posts.

What I want to be able to do is for the user's home page, display all the distinct posts for the groups that the user is a member of.

eg. current_user.groups.posts # i wish it was this easy!!

Here is my code.

Models:

class User < ActiveRecord::Base
  has_many :memberships
  has_many :groups, :through => :memberships
  has_many :posts  # as author of post
end

class Group < ActiveRecord::Base
  has_many :memberships
  has_many :users, :through => :memberships
  has_and_belongs_to_many :posts
end

class Membership < ActiveRecord::Base
  belongs_to :user
  belongs_to :group
end

class Post < ActiveRecord::Base
  belongs_to :user
  has_and_belongs_to_many :groups
end

routes.rb

Myapp::Application.routes.draw do
  get "admin/index"
  devise_for :users

  resources :users do
    member do
      get :groups
    end
  end

  resources :groups do
    member do
      get :members
      post :join
      post :leave
    end
  end

  resources :posts

home_controller.rb#index

class HomeController < ApplicationController
  before_filter :authenticate_user!

  def index
    @user = current_user
    @groups = Group.all
    @user_groups = @user.groups
    @home_page_posts = Post.joins(:groups, :user)
  end
end

This obviously just gives me a non-distinct list of all posts in all groups.

If anyone can point me in the right direction. I've tried http://guides.rubyonrails.org/active_record_querying.html#joining-tables but none of the examples apply as far as I can see.

Please let me know if you need more information from me. :D

Upvotes: 3

Views: 3249

Answers (2)

pedrogrande
pedrogrande

Reputation: 43

Thanks to @shioyama, I ended up using his answer to create a model method for Post:

def self.posts_for_users_groups(current_user)
  includes(:groups => :users).where('users.id' => current_user.id)
end

which I call from my home controller:

def index
  @user_visible_posts = Post.posts_for_users_groups(current_user)
end

Thanks again @shioyama :D

Upvotes: 1

Chris Salzberg
Chris Salzberg

Reputation: 27374

I believe this is what you want:

Post.includes(:groups => :users).where('users.id' => current_user.id)

This will generate SQL like this (for current_user.id = 1):

SELECT "posts"."id" AS t0_r0, "posts"."user_id" AS t0_r1,
  "posts"."created_at" AS t0_r2, "posts"."updated_at" AS t0_r3,
  "groups"."id" AS t1_r0, "groups"."created_at" AS t1_r1,
  "groups"."updated_at" AS t1_r2, "users"."id" AS t2_r0,
  "users"."name" AS t2_r1, "users"."created_at" AS t2_r2,
  "users"."updated_at" AS t2_r3
  FROM "posts"
  LEFT OUTER JOIN "groups_posts" ON "groups_posts"."post_id" = "posts"."id"
  LEFT OUTER JOIN "groups" ON "groups"."id" = "groups_posts"."group_id"
  LEFT OUTER JOIN "memberships" ON "memberships"."group_id" = "groups"."id"
  LEFT OUTER JOIN "users" ON "users"."id" = "memberships"."user_id"
  WHERE "users"."id" = 1

See also: Filter model with multiple has many through (not exactly the same, but similar)

Upvotes: 1

Related Questions