I have a one to many relationship between conversations and messages i.e. there is one or more messages per conversation. How would I in SQL get the latest message per conversation? I am using created_at to keep track of when the messages are being created. My DB is postgresql if that makes a difference. Thanks for your help. John Kopanas
On 7/7/05, John Kopanas <john.kopanas-O1KSuMybMhqBUy7/sJONFg@public.gmane.org> wrote:> I have a one to many relationship between conversations and messages > i.e. there is one or more messages per conversation. How would I in > SQL get the latest message per conversation? I am using created_at > to keep track of when the messages are being created. > > My DB is postgresql if that makes a difference. > > Thanks for your help. > > John Kopanas > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >Something like (untested) Conversation.find(:id).messages.find(:first, :order => "created_at desc")
That would work for one conversation but I am looking at finding the latest message for every conversation not just one. And looping through would not be a good option. On 7-Jul-05, at 5:35 PM, Jason Foreman wrote:> On 7/7/05, John Kopanas <john.kopanas-O1KSuMybMhqBUy7/sJONFg@public.gmane.org> wrote: > >> I have a one to many relationship between conversations and messages >> i.e. there is one or more messages per conversation. How would I in >> SQL get the latest message per conversation? I am using created_at >> to keep track of when the messages are being created. >> >> My DB is postgresql if that makes a difference. >> >> Thanks for your help. >> >> John Kopanas >> >> _______________________________________________ >> Rails mailing list >> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >> http://lists.rubyonrails.org/mailman/listinfo/rails >> >> > > Something like (untested) > > Conversation.find(:id).messages.find(:first, :order => "created_at > desc") > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >John Kopanas http://www.thedatingguy.com - Online Dating the way it should be. http://blog.thedatingguy.com
On 7/8/05, John Kopanas <john.kopanas-O1KSuMybMhqBUy7/sJONFg@public.gmane.org> wrote:> That would work for one conversation but I am looking at finding the > latest message for every conversation not just one. > > And looping through would not be a good option.Why not? Have you profiled it to show that it''s slower than making the database do exactly the same thing? Having said that, the easiest option is for you to store the newest message on the conversation itself. -- Cheers Koz
I agree that it would be easiest and probably most elegant to add a property to your conversations object to hold the latest message. I do think that if you don''t go that route, the database will definitely be faster to find your results. They are really, really good at that kind of looping. Here''s an approximation (e.g. not tested) of what you''d need: select msgs.id, convs.id from messages msgs, conversations convs where msgs.conversation_id = convs.id group by convs.id having MAX(msgs.created_at) is not null The MAX function is the key. I haven''t used Postgres, but I think it''s probably in there. It will return the row with the max value for that column, in this case, the biggest (most recent) date. -Tom On 7/7/05, Michael Koziarski <koziarski-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On 7/8/05, John Kopanas <john.kopanas-O1KSuMybMhqBUy7/sJONFg@public.gmane.org> wrote: > > That would work for one conversation but I am looking at finding the > > latest message for every conversation not just one. > > > > And looping through would not be a good option. > > Why not? Have you profiled it to show that it''s slower than making > the database do exactly the same thing? > > Having said that, the easiest option is for you to store the newest > message on the conversation itself. > > -- > Cheers > > Koz > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- Tom Wilcoxen http://convergentarts.com http://www.dreamhost.com/r.cgi?twilcoxen
On Thu, 2005-07-07 at 17:27 -0400, John Kopanas wrote:> I have a one to many relationship between conversations and messages > i.e. there is one or more messages per conversation. How would I in > SQL get the latest message per conversation? I am using created_at > to keep track of when the messages are being created.Rule #1 with Rails - assume it is already being handled and check the API docs Rule #2 with Rails - if you didn''t locate with Rule #1 - TRY AGAIN - it is there - you didn''t look at the right spot :) You want the :order option for the has_many association. You can use created_at DESC to get your conversations returned to you in order.
* John W Higgins [2005-07-07 22:42]:> Rule #1 with Rails - assume it is already being handled and check the > API docs > > Rule #2 with Rails - if you didn''t locate with Rule #1 - TRY AGAIN - it > is there - you didn''t look at the right spot :)And another point, for any code, learn to use `grep` and friends. There are GUI versions for Windows too if you''re shell-phobic. One of the most useful tools in the (code) world! -- ________________________________ toddgrimason*todd[ at ]slack.net