CODEWITHSUNDEEP
ruby-on-railsmany-to-manyassociations
Karan
Karan

Reputation: 15114

Rails: How to query based on an extra field in a many-to-many relationship?

I am intrigued on how I could query a many-to-many relationship based on an extra field present in the join table. Best asked via an example.

I have a model User, and a model Group.

The many-to-many relationship is called Groupization. It has an extra field 'active_user'. I would like to query for all the users in a group that are active.

If I do:

@user.groupizations.find_all_by_active_user(true) - gives me a list of groupizations; however, I would be looking for a list of users. Maybe I would need to do some mapping?

Thanks!

Upvotes: 1

Views: 807

Answers (2)

charlysisto
charlysisto

Reputation: 3700

With an extra field on the join table you should use has_many :through instead of has_many_to_many. It does more with the same API

class User
  has_many :groupizations
  has_many :groups, through: groupization

  scope :active, joins(:groupizations).where("groupizations.active_user='1'")
end

# controller
@active_users = User.active

Upvotes: 0

PinnyM
PinnyM

Reputation: 35541

You can do this by joining the association to the query, and then searching in the scope of the associated table:

User.joins(:groupizations).where(:groupizations => {:active_user => true})

Note that this will return duplicate rows if multiple groupizations meet this criteria. To get distinct users you can do one of the following:

  • Add a call to .select("DISTINCT users.*") with some ORDER clause as .order("users.id") (change the sort column as needed)

  • Add a GROUP BY clause like this: .group("users.id"). For non-MySQL databases, you may need to be more specific and include all columns in the grouping:

    .group(User.column_names.map{|c| "users.#{c}"}.join(','))

Upvotes: 1

Related Questions