CODEWITHSUNDEEP
mysqlruby-on-railsrubyrails-activerecord
Bitwise
Bitwise

Reputation: 8461

How to find number of records based on association through join table

I have User, Assignment, and Account models. Users can belong to many accounts and accounts have many assignments. As an admin on an account you can 'assign' users to an assignment. I'm trying to find how many users are assigned to a particular assignment. Also, users and assignments have a join table called assignment_relationships. That table has a boolean that flips if the user is assigned to that table – the attribute is called designated. This is kinda confusing but it should be pretty straight forward. Here are the associations:

User:

class User < ApplicationRecord
  has_many :account_memberships
  has_many :accounts, through: :account_memberships
  has_many :assignment_relationships
  has_many :assignments, through: :assignment_relationships
end

Account:

class Account < ApplicationRecord
  has_many :assignments
end

Assignment:

class Assignment < ApplicationRecord    
  belongs_to :account
  has_many :assignment_relationships
  has_many :users, through: :assignment_relationships
end

Assignment_relationships:

class AssignmentRelationship < ApplicationRecord
  belongs_to :user
  belongs_to :assignment
end

So as a recap, I'm trying to find a query that will tell me how many users are assigned to a particular assignment. Thanks for the help!

Upvotes: 1

Views: 40

Answers (2)

Karan Shah
Karan Shah

Reputation: 1324

SELECT count(users.id), assignments.id
FROM assignment_relationships
WHERE designated IS TRUE
GROUP BY assignments.id

You just need one table ideally

Upvotes: 0

jeffdill2
jeffdill2

Reputation: 4114

I feel like perhaps I'm missing something in your question (because my answer is incredibly simple) but why can't you just do:

@assignment.users.count

Since it appears that you have your has_many, through: relationship set up properly, calling users on an Assignment object should correctly go through your assignment_relationships join table to return any users connected to the assignment.

Upvotes: 1

Related Questions