My head is spinning a bit this morning - can somebody help me with this? Given these two models with the following associations: class Article < ActiveRecord::Base belongs_to :category - has an attribute called "published_on" end class Category < ActiveRecord::Base has_many :articles end I need to list all categories, the order should be determined by the "published_on" date of their latest associated article. So for example: cat1 = Category.create cat1.articles.create(:published_on => 5.days.ago) cat1.articles.create(:published_on => 4.days.ago) cat2 = Category.create cat2.articles.create(:published_on => 2.days.ago) Then my list should be ordered: [cat2, cat1] I believe this would be trivial to make in raw SQL, but I have trouble figuring out, how to do it with AR''s find() method. - Carsten -- Posted via http://www.ruby-forum.com/.
Carsten Gehling wrote:> My head is spinning a bit this morning - can somebody help me with this? > > Given these two models with the following associations: > > class Article < ActiveRecord::Base > belongs_to :category > > - has an attribute called "published_on" > end > > class Category < ActiveRecord::Base > has_many :articles > end > > > I need to list all categories, the order should be determined by the > "published_on" date of their latest associated article. > > So for example: > > cat1 = Category.create > cat1.articles.create(:published_on => 5.days.ago) > cat1.articles.create(:published_on => 4.days.ago) > > cat2 = Category.create > cat2.articles.create(:published_on => 2.days.ago) > > Then my list should be ordered: > > [cat2, cat1] > > I believe this would be trivial to make in raw SQL, but I have trouble > figuring out, how to do it with AR''s find() method. > > - CarstenHey Carsten Are you aware that you can include associations in find queries with ":include" http://api.rubyonrails.org/classes/ActiveRecord/Base.html Off the top of my head, try: Category.all :include => :articles, :order => "articles.published_on DESC" Let me know if that works Cheers, Gavin http://handyrailstips.com -- Posted via http://www.ruby-forum.com/.
Gavin Morrice wrote:> > Off the top of my head, try: > > Category.all :include => :articles, :order => "articles.published_on > DESC"Works like a charm - thanks! Now that you got my head pointed in the right direction ;-) I noticed, that :joins does the same job for me (ie. same result) with some slight internal difference: :include - uses LEFT OUTER JOIN and explicitely selects all attributes from both tables (specifying each attribute by name) :joins - uses INNER JOIN and only selects categories.* While it probably won''t matter with my 300-400 articles, in a larger dataset I should think, that :joins is the faster of the two. Thank you for your help. :-) - Carsten -- Posted via http://www.ruby-forum.com/.