Need help with an SQL query in Rails

on my site, I'm trying to display the hiking trails with the latest posted pictures (Scroll to the "Picture section: http://www.trailheadfinder.com/trail_search/latest_trails). However, the current query I use, is showing the trails in order of the "first" picture posted. So when a new picture is added at a later date, the trail does not show at the top. I have a trail table and a trailpicture table that are linked. Here is the current query I use:

@trails_pictures = Trailpicture.find(:all,
                                     :limit => 20,
                                     :include => [:trail],
                                     :select => 'trailpictures.trail_id, trails.name, trails.short_description, trails.city, trails.state, trails.country',
                                     :group => 'trailpictures.trail_id',
                                     :conditions => ["trailpictures.parent_id is NULL"],
                   开发者_开发技巧                  :order => 'trailpictures.id DESC')

Any help would be greatly appreciated!

Thank You,

Nick,

You need to order by created_at. :)

I finally figured it out. This is how it needs to be written:

@trails_pictures = Trail.find(:all,
                              :joins => 'INNER JOIN trailpictures ON trails.id = trailpictures.trail_id',
                              :limit => 20,
                              :conditions => ["trailpictures.parent_id is NULL"],
                              :select => 'trails.id, trails.name, trails.short_description, trails.city, trails.state, trails.country, max(trailpictures.id)',
                              :group => 'trails.id',
                              :order => 'max(trailpictures.id) DESC')