activerecord - How to turn a self-table subquery SQL to a Rails named_scope? -

i'm using rails 2.3.10 , new named_scope. i'm dealing sql retrieves list of last invitations of particular event. came sql subquery , looks can want. i'm thinking of possible use named_scope same thing can make use of find().

i have following questions:

1. is possible implement sql named_scope?
2. can in elegant way sub-select not included in :condition?more 1 named_scope needed?
3. how named_scope(s) like?
4. how find() when includes name_scope(s)?

sql:

select *   invitation inv1   join (          select event_id, user_id, max(invite_time) last_invite_time            invitation           group event_id, user_id        ) last_invite on        inv1.event_id = last_invite.event_id ,        inv1.user_id = last_invite.user_id ,        inv1.invite_time = last_invite.last_invite_time 

invitation data:

event_id     user_id     invite_time       invite_reply_code 1            78          2011-02-01 15:21  1 2            78          2011-02-02 11:45  1 2            79          2011-02-02 11:50  1 2            79          2011-02-02 11:55  1 2            80          2011-02-02 11:50  1 2            80          2011-02-02 11:51  1 

expected result:

event_id     user_id     invite_time       invite_reply_code 2            78          2011-02-02 11:45  1 2            79          2011-02-02 11:55  1 2            80          2011-02-02 11:51  1 

the :joins option in find can take string, stick sql. event_id in there need use lambda, you're after:

named_scope :foo, lambda { |event_id|   { :select => "oinv.*",     :from => "invitation oinv"     :joins => <<-sql       join (              select event_id, user_id, max(invite_time) last_invite_time                invitation jinv               group event_id, user_id            ) last_invite on            oinv.event_id       = last_invite.event_id ,            oinv.user_id        = last_invite.user_id ,            oinv.invite_time    = last_invite.last_invite_time       sql     , :conditions => [ "oinv.event_id = ?", event_id ]   } } 

this totally untested, can see i'm doing , sets on right path.