Rails find or load specific record from includes on has

Apologies for the terrible title!

Background

Users can have many Organisations Organisations can have many Users UserOrganisations is the join model and stores data about a specific user in a specific organisation.

class Organisation < ApplicationRecord
  has_many :user_organisations, dependent: :destroy
  has_many :users, through: :user_organisations
end
class User < ApplicationRecord
  has_many :user_organisations, dependent: :destroy
  has_many :organisations, through: :user_organisations
end
class UserOrganisation < ApplicationRecord
  belongs_to :user
  belongs_to :organisation
end

Problem

I want to get all users that belong to a specific organisation, excluding users which have a specific attribute and including or eager loading the joining UserOrganisation record.

At the moment I'm calling

class User < ApplicationRecord
  scope :super_users, -> { where(super_user: true) }
end

@organistion.users.excluding(User.super_users)

I want to be able to reference values from the specific UserOrganistion.where(user_id: user.id, organisation_id: @organistion.id)

Is there a way of doing it this way around or do I need to do @organisation.user_organisations.joins(:users).merge(User.not_super_users)

CodePudding user response：

I think you want do something like this:

SELECT u.*
FROM users u
JOIN user_organisations uo ON u.id = uo.user_id
JOIN organisations o ON o.id = uo.organisation_id
WHERE o.id = some_organisation_id
AND u.super_admin = false

and the equivalent AR query can be:

Organisation.find_by(id: some_organisation_id).users.where(admin: false)

Organisation.find_by(id: some_organisation_id).users.where.not(admin: true)

CodePudding user response：

You can use the includes method to eager load the UserOrganisation records for the specific users in the specific organisation. Then you can use the where.not method to exclude the users with the specific attribute.

Here is an example of how you can do that:

users = @organisation.users.includes(:user_organisations)
                     .where.not(user_organisations: { user_id: User.super_users.pluck(:id) })

Then you can access the specific UserOrganisation record for a user using the user_organisations association:

user_org = users.first.user_organisations.where(organisation_id: @organisation.id).first