has_many through querying attributes on join model

[Tomas]

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

[veyron]

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

[Tomas]

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