Select parent based on attribute of most recent child element

I'm writing a simple membership application. I have two models, 开发者_StackOverflow社区Member and Membership.

class Member < ActiveRecord::Base
   has_many :memberships
end

class Membership < ActiveRecord::Base
  belongs_to :member
end

A member holds information like name and date of birth, a membership holds the date the membership was applied for, the date it started, the date it expires etc. Once a membership expires, the member can renew it, which creates a new membership. This means that each member can have multiple memberships, although only the latest will be their current, valid membership.

I now want to retrieve, for example, all members whose membership has expired. I can't just do something like

@members = Member.joins(:memberships).where('memberships.expires < ?', Time.now) 

as this will include any current members who have had memberships in the past. What I really need to do is to be able to join to only the most recent of a member's memberships and base the query on that. I'm new to Rails though so struggling a bit with this - help or ideas very much appreciated.

EDIT: Obviously this is the kind of thing that's not difficult to do in plain old SQL, but I was hoping there'd be some nice Rails way to do it (other than just pasting the SQL in).

I would also rather not add another column to either of the tables just to make the queries I want to do possible. It's messy, I shouldn't have to do it, and will most likely cause problems down the line.

---

Let's think of it logically.

all members whose most recent membership has expired

Is actually the same as

all members who have no active membership

In the latter case you can do it in SQL like so

SELECT * FROM members
WHERE NOT EXISTS (
  SELECT * FROM memberships
  WHERE members.id = memberships.member_id
  AND   memberships.expires > #{Time.now}
)

You can achieve the same with Active Record

Member.where(["NOT EXISTS (
  SELECT * FROM memberships
  WHERE members.id = memberships.member_id
  AND   memberships.expires > ?
)", Time.now])

Now that is quite nasty, but that is exactly what you're asking for.

---

Approach 1- Group MAX

You might get better results by using a JOIN.

Member.joins("JOIN (
     SELECT a.member_id, MAX(a.expires) expires_at
     FROM   memberships a
     GROUP BY a.member_id
     WHERE a.paid = 1 AND  a.expires IS NOT NULL
   ) b ON b.member_id = members.id
  ").
  where("b.expires < ?", Time.now) 

Approach 2 - LEFT JOIN

Member.joins(
  "
    JOIN
    ( 
      SELECT m1.membership_id
      FROM   membership m1
      LEFT OUTER JOIN membership m2 
             ON m2.membership_id = m1.membership_id AND
                m2.expires IS NOT NULL AND
                m2.expires < m1.expires 
      WHERE  m2.expires IS NULL AND 
             m1.expires < #{sanitize(Time.now)}
    ) m ON m.membership_id = members.id
 "
)

Approach 3 - Denormalization

Better solution is to add a flag called is_current on the memberships table and set the default value to true.

class Membership

  after_create :reset_membership

  # set the old current to false    
  def reset_membership
    Membership.update_all({:is_current => false}, 
      ["member_id = ? AND id != ?", member_id, id])
  end
end

class Member
  has_many :memberships
  scope :recently_expired, lambda {
   { 
     :joins      => :memberships, 
     :conditions => [ "memberships.is_current = ? AND memberships.expires < ?", 
                       true, Time.now]
   }
  }
end

Now you can get the recently expired members as:

Member.recently_expired

---

If you add a column past_expired in membership model and becomes true when the member add a new membership, so you can get easyly the last membership.

---

Another SQL query that solves this [greatest-n-per-group] type problem would be the following. It joins the members and memberships tables using a complex condition that limits the join to the one row of memberships that has the latest expiration date:

SELECT members.*
     , m.applied AS applied
     , m.paid    AS paid
     , m.started AS started
     , m.expires AS expires
FROM 
    members
  INNER JOIN 
    memberships AS m
      ON m.id = ( SELECT m1.id
                  FROM memberships m1 
                  WHERE m1.member_id = members.id
                  ORDER BY m1.expires DESC
                  LIMIT 1
                )

---

Thanks for all your suggestions. I've voted them up rather than accepting them as I don't feel they quite cover what I want, but what I'm going to do has certainly benefitted from them.

I still feel that SQL should be avoided if possible (for all the reasons I've already mentioned in comments), but I think in this case it can't, so I've decided to define a named scope for the Member model like this:

EDIT: I originally defined this as the default scope - however I have decided to heed the warning from KandadaBoggu on complex default scopes, so have made it a named scope instead. The query is also a bit more complicated than others described to cope with excluding renewed memberships (where the start date is in the future) when a currently active membership exists. Thanks again to KandadaBoggu for the bones of the query and hint on avoiding N+1 selects.

scope :with_membership, lambda { 
select('members.*, m.applied AS applied, m.paid AS paid, m.start AS start, m.expiry as expiry').
joins("INNER JOIN (
  SELECT m3.*
  FROM memberships m3
  LEFT OUTER JOIN memberships m5 
  ON m3.member_id = m5.member_id 
  AND m5.created_at > m3.created_at
  AND m5.expiry > #{sanitize(Time.now)}
  WHERE m3.expiry > #{sanitize(Time.now)}
  AND m5.id IS NULL
  UNION
  SELECT m1.*
  FROM   memberships m1
  LEFT OUTER JOIN memberships m2 
  ON m1.member_id = m2.member_id 
  AND m2.created_at > m1.created_at 
  LEFT OUTER JOIN memberships m4 
  ON m1.member_id = m4.member_id 
  AND m4.expiry > #{sanitize(Time.now)}
  WHERE  m2.id IS NULL AND m4.id IS NULL
  ) m 
  on (m.member_id = members.id)") }         

I've seen prettier bits of code. But my rationale is this - if you're going to have to have a horrible bit of SQL like this, you might as well have it in only one place rather than repeated all over the place for every different query you might need (like expired members, members who haven't paid yet etc etc).

With the above scope in place, I can just treat the extra membership columns as normal columns on Member, and write nicely simple queries like this:

#expired
Member.with_membership.find :all, :conditions => ['expires < ?', Time.now ]

#current
Member.with_membership.find :all, :conditions => ['started < ? AND expires > ?', Time.now, Time.now ]

#pending payment
Member.with_membership.find :all, :conditions => ['applied < ? AND paid IS NULL', Time.now ]

To briefly justify accepting my own answer rather than one of the other, very useful answers, I want to point out that this was never a question about how to get the 'greatest n per group' (although this is a component of it). It was about how to best deal with this kind of query in the specific environment of Rails, with the specific problem of members with multiple memberships where only one is active at any one time, and with a number of similar queries that all need fields from this single active membership.

That's why I think using a named scope in this way is, ultimately, the best answer to the question. Put up with the hideous SQL query, but have it in one place only.