CODEWITHSUNDEEP
ruby-on-railsrubypostgresql
gwalshington
gwalshington

Reputation: 1505

Find all records associated with a user in same group as current_user

My models: User, Group, GroupsUser, and Availability.

I want to find all Availabilities created by users who are associated to a group, or several groups. 

class Availability < ApplicationRecord
  belongs_to :user
end

class User < ApplicationRecord
  has_many :group_users
  has_many :groups, through: :group_users

  has_many :availabilities
end

class Group < ApplicationRecord
  has_many :group_users
  has_many :users, through: :group_users
end

class GroupUser < ApplicationRecord
  belongs_to :group
  belongs_to :user
end

Here is what I've tried to find all Availabilities created by a user in the same group as the first user. How can I make this query?

user = User.first

Availability.joins(:user).where('user.groups.pluck[:id] IN (?)', user.groups.pluck[:id])

Would also be great to put a scope on the Group, but it throws an error each time I do that.

Upvotes: 3

Views: 244

Answers (2)

mindlis
mindlis

Reputation: 1677

Does this get what you're looking for? 

class Availability < ApplicationRecord
  belongs_to :user
  has_many :groups, through: :user

  scope :for_user_in_groups, lambda { |user|
    next all unless user.present?

    joins(user: :groups).where(groups: { id: user.groups.select(:id) }).distinct
  }
end

This produces:

[32] pry(main)> Availability.for_user_in_groups(User.find(2))
  User Load (0.2ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2  [["id", 2], ["LIMIT", 1]]
  Availability Load (0.8ms)  SELECT DISTINCT "availabilities".* FROM "availabilities" INNER JOIN "users" ON "users"."id" = "availabilities"."user_id" INNER JOIN "group_users" ON "group_users"."user_id" = "users"."id" INNER JOIN "groups" ON "groups"."id" = "group_users"."group_id" WHERE "groups"."id" IN (SELECT "groups"."id" FROM "groups" INNER JOIN "group_users" ON "groups"."id" = "group_users"."group_id" WHERE "group_users"."user_id" = $1)  [["user_id", 2]]
  => [#<Availability:0x00007fd3830bae38 id: 6, user_id: 3>,
    #<Availability:0x00007fd3830bacf8 id: 5, user_id: 2>,
    #<Availability:0x00007fd3830babb8 id: 3, user_id: 3>,
    #<Availability:0x00007fd3830baa78 id: 2, user_id: 2>]

Upvotes: 0

Peter Balaban
Peter Balaban

Reputation: 643

There are two options to do so:

1) Through the join

Availability.joins(user: :groups).distinct

2) Through the sub queries

Availability.where(user_id: User.joins(:groups).distinct.pluck(:id))

If you need to filter Availability with a specific group you can add .where(groups: { name: 'Group name' }) condition

Upvotes: 1

Related Questions