I''ve banged my head on this for 2 days, and need to step back and ask for help. I''m doing what I would think would be a common occurrence - viewing a list of records sorted by the latest item in a has_many relationship. Contacts have many appointments, with one next_appointment that is the most recent. My view lists the contacts, using pagination. One of the displayed columns is the next_appointment. I want to support sorting the list on the next_appointment. Problem is I always get *every* appointment for each contact in the list. I can''t seem to weed everything except the latest appointment. Doesn''t matter if I use the :include or not, and DISTINCT doesn''t work becuase the records are real duplicates. I haven''t tried GROUP BY yet because I don''t think it''s going to weed anything out, though I may be wrong about that. I have the following relationships: class Contact < ActiveRecord::Base has_many :appointment, :dependent => :destroy, :order => ''created_at DESC'' has_one :next_appointment, :class_name => ''Appointment'', :order => ''appointment_on DESC'' end class Appointment < ActiveRecord::Base belongs_to :contact end Does anybody know a way to handle this situation? Cheers, Brett --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
I didn''t test this, but the :finder_sql option of the has_many
relationship
might allow you to get what you want:
has_many :next_appointment,
:class_name => ''Appointment'',
:finder_sql =>''SELECT * FROM appointments WHERE contact_id = #{id}
ORDER BY
appointment_on DESC LIMIT 1''
The relationship name is now even less descriptive than it was before since
it only really has one appointment, and it doesn''t guarantee that
it''s the
next appointement (unless you add a ''appointment_on >
now()'' clause to the
query).
A more expressive way to go about it might be to add a method to the Contact
object called next_appointment that returns the top appointment like:
def next_appointment
self.appointments.find :first,:order=>''appointment_on
DESC'',
:conditions=>''appointment_on> now()''
end
Depending on the size of your dataset, you may get better performance by
eager loading and using ruby to search the arrays (you can find a great
article about that
here<http://www.informit.com/articles/article.asp?p=26943&rl=1>
);
I didn''t test either of these, so they may be slightly off.
On 8/29/06, Brett Walker
<lapomme00-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:>
>
> I''ve banged my head on this for 2 days, and need to step back and
ask for
> help.
>
> I''m doing what I would think would be a common occurrence -
viewing a
> list of records sorted by the latest item in a has_many relationship.
>
> Contacts have many appointments, with one next_appointment that is the
> most recent.
>
> My view lists the contacts, using pagination. One of the displayed
> columns is the next_appointment. I want to support sorting the list
> on the next_appointment. Problem is I always get *every* appointment
> for each contact in the list. I can''t seem to weed everything
except
> the latest appointment.
>
> Doesn''t matter if I use the :include or not, and DISTINCT
doesn''t work
> becuase the records are real duplicates. I haven''t tried GROUP BY
yet
> because I don''t think it''s going to weed anything out,
though I may be
> wrong about that.
>
> I have the following relationships:
>
> class Contact < ActiveRecord::Base
> has_many :appointment, :dependent => :destroy, :order =>
> ''created_at DESC''
> has_one :next_appointment, :class_name =>
''Appointment'',
> :order => ''appointment_on DESC''
> end
>
> class Appointment < ActiveRecord::Base
> belongs_to :contact
> end
>
> Does anybody know a way to handle this situation?
>
> Cheers,
> Brett
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
Brett Walker
2006-Aug-30 11:04 UTC
Re: Sorting list of records by a has_many relationship...
Jeff, I gave your suggestions a try. Your has_many works the same as my has_one. Find the contact, and the appointment is the latest one. But, if you :inlcude => appointment, it pulls all appointments for a contact, not just the latest one. And I think I have to eager load (or :join) in order to get the sorting on the appointment field. So I still get a list where there are multiple entries per contact, each with a different appointment date. The dataset is large enough that I can''t pull it all into memory and sort with ruby. That''s why I want to find an SQL way so I can use pagination. I keep thinking either a subselect or group by would work, but I can''t seem to get one coded correctly. Any ideas? Cheers, Brett On 8/29/06, Jeff Dean <jeff-qQc71EY76bRBDgjK7y7TUQ@public.gmane.org> wrote:> I didn''t test this, but the :finder_sql option of the has_many relationship > might allow you to get what you want: > > has_many :next_appointment, > :class_name => ''Appointment'', > :finder_sql =>''SELECT * FROM appointments WHERE contact_id = #{id} ORDER BY > appointment_on DESC LIMIT 1'' > > The relationship name is now even less descriptive than it was before since > it only really has one appointment, and it doesn''t guarantee that it''s the > next appointement (unless you add a ''appointment_on > now()'' clause to the > query). > > A more expressive way to go about it might be to add a method to the Contact > object called next_appointment that returns the top appointment like: > > def next_appointment > self.appointments.find :first,:order=>'' appointment_on DESC'', > :conditions=>''appointment_on > now()'' > end > > Depending on the size of your dataset, you may get better performance by > eager loading and using ruby to search the arrays (you can find a great > article about that here); > > I didn''t test either of these, so they may be slightly off. > > > On 8/29/06, Brett Walker <lapomme00-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org > wrote: > > > > I''ve banged my head on this for 2 days, and need to step back and ask for > help. > > > > I''m doing what I would think would be a common occurrence - viewing a > > list of records sorted by the latest item in a has_many relationship. > > > > Contacts have many appointments, with one next_appointment that is the > > most recent. > > > > My view lists the contacts, using pagination. One of the displayed > > columns is the next_appointment. I want to support sorting the list > > on the next_appointment. Problem is I always get *every* appointment > > for each contact in the list. I can''t seem to weed everything except > > the latest appointment. > > > > Doesn''t matter if I use the :include or not, and DISTINCT doesn''t work > > becuase the records are real duplicates. I haven''t tried GROUP BY yet > > because I don''t think it''s going to weed anything out, though I may be > > wrong about that. > > > > I have the following relationships: > > > > class Contact < ActiveRecord::Base > > has_many :appointment, :dependent => > :destroy, :order => > > ''created_at DESC'' > > has_one :next_appointment, :class_name => ''Appointment'', > > :order => ''appointment_on DESC'' > > end > > > > class Appointment < ActiveRecord::Base > > belongs_to :contact > > end > > > > Does anybody know a way to handle this situation? > > > > Cheers, > > Brett > > > > > > > > > > > > >--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Phil Peterman
2006-Aug-30 12:35 UTC
Re: Sorting list of records by a has_many relationship...
Brett Walker wrote:> Jeff, > > I gave your suggestions a try. Your has_many works the same as my > has_one. Find the contact, and the appointment is the latest one. > But, if you :inlcude => appointment, it pulls all appointments for a > contact, not just the latest one. And I think I have to eager load > (or :join) in order to get the sorting on the appointment field. So I > still get a list where there are multiple entries per contact, each > with a different appointment date. > > The dataset is large enough that I can''t pull it all into memory and > sort with ruby. That''s why I want to find an SQL way so I can use > pagination. > > I keep thinking either a subselect or group by would work, but I can''t > seem to get one coded correctly. Any ideas? > > Cheers, > BrettHi Brett, you may want to take a look at acts_as_list for the appointments model. It gives you a number of handy methods like .position .first .last -- 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 -~----------~----~----~----~------~----~------~--~---