Rails - Nov 2008 - Combining :order and :group in find yields wrong records

I have a setup with Members, Teams and Memberships.

Teams can have_many Memberships
Teams can have_many Members through Memberships

Memberships belongs to a status

Only the latest Membership of a Member is valid.

I use this setup to keep a sort of audit log of a Member''s history so I
can see which teams he applied to, where he was kicked from and which he
joined in. The Memberships table is timestamped and most of the
distinction is done based on the created_at column.

Memberships has the following columns:
id team_id member_id membership_status_id created_at updated_at

Now I would like a list for all team with their pending memberships.
(defined by the membership_status_id)

I run this query:
Membership.find(:all, :conditions => {:membership_status_id => PENDING})

This returns a list of all Memberships that HAVE EVER BEEN PENDING. So
this also yields records that are long accepted or declined. Not really
useful!

To be more precise, I want for all members their latest membership to
match the PENDING condition.

Back to the drawing board, I designed the next query:
Membership.find(:all, :group => ''member_id'', :order =>
''created_at
DESC'')

Now this actually groups correctly on the member ID and only gives me
back 1 membership for each member. But it will give me the FIRST one.
The order clause is completely ignored. How do I get the LAST one with a
group_by part? My SQL is kind of rusty in that department.
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On 5 Nov 2008, at 02:37, Chris Dekker wrote:
>
> I have a setup with Members, Teams and Memberships.
>
> Teams can have_many Memberships
> Teams can have_many Members through Memberships
>
> Memberships belongs to a status
>
> Only the latest Membership of a Member is valid.
>
> I use this setup to keep a sort of audit log of a Member''s history
> so I
> can see which teams he applied to, where he was kicked from and  
> which he
> joined in. The Memberships table is timestamped and most of the
> distinction is done based on the created_at column.
>
> Memberships has the following columns:
> id team_id member_id membership_status_id created_at updated_at
>
> Now I would like a list for all team with their pending memberships.
> (defined by the membership_status_id)
>
> I run this query:
> Membership.find(:all, :conditions => {:membership_status_id =>  
> PENDING})
>
> This returns a list of all Memberships that HAVE EVER BEEN PENDING. So
> this also yields records that are long accepted or declined. Not  
> really
> useful!
>
> To be more precise, I want for all members their latest membership to
> match the PENDING condition.
>
> Back to the drawing board, I designed the next query:
> Membership.find(:all, :group => ''member_id'', :order
=> ''created_at
> DESC'')
>
> Now this actually groups correctly on the member ID and only gives me
> back 1 membership for each member. But it will give me the FIRST one.
> The order clause is completely ignored. How do I get the LAST one  
> with a
> group_by part? My SQL is kind of rusty in that department.
The order clause isn''t ignored - it''s used for sorting the
final array
(or to put things another way it does not sort then group). When you  
group by something, all non aggregate columns (ie things other than  
things like SUM, COUNT) are indeterminate. If you are using mysql see  
also
http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

Fred

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---