has_many through querying attributes on join model

Hello,

This is a classic User → Membership → Group example with some twist: User is a member of a single Group at any given time. But I want to keep the history which Group the User was a member of.

I am looking for a proper way of doing it.

This is how I think models should look like: #user.rb Class User < ActiveRecord::Base has_many :memberships has_many :groups, through: :memberships

#membership.rb Class Membership < ActiveRecord::Base belongs_to :user belongs_to :group

#group.rb Class Group < ActiveRecord::Base has_many :memberships has_many :users, through: :memberships

Obviously we need date_from attribute in memberships to keep a track on when the user joined the group.

Creating/Updating is working out of the box. It’s querying data I have trouble with.

Now it is easy to get the last group the user is a member of: #user.rb … def latest_group memberships.order(:date_from).last.group end

Similarly you can get latest user for a group.

But how to get all users currently belonging to group? Furthermore how to get all the users belonging to group at given date?

Looking forward for your input.

Cheers Tomas

Tomas -

An option I’ve used in the past is to add a datetime field such as “dropped_at” that defaults to null to your memberships table.

From there, you could add a scope to the memberships model like:

class Membership < ActiveRecord::Base scope :active, → { where(dropped_at: nil) } end

``

This concept used to be used when you wanted to “soft delete” a record from your table,

but have an option to “undo” the delete. Here, in your instance - you can borrow the idea

to easily find those memberships for a given User that are “active” as well as those users

that are currently a part of a group. The standard created_at field that is on the memberships

table can be used to indicate when a user joined a group.

Hope this helps!

Mike

Hello,

Forgot to mention that I am using Postgresql, which gave me an idea to try using ‘DISTINCT ON’

So this is how I’ve solved it:

class Membership < ActiveRecord::Base

scope :current, → {at(Date.today)}

def self.at(date) where(“memberships.from <= ?”, date).select(“DISTINCT ON (user_id) *”).order(:user_id, from: :desc) end end

``

This also gives me an opportunity to get the active membership at any given date.

I hope this will help for someone.

Cheers, Tomas

2014 m. lapkritis 26 d., trečiadienis 14:51:31 UTC+2, Mike rašė: