0 Replies Latest reply: Apr 6, 2012 8:34 PM by 841578 RSS

    Query to add edited entries to the top of a paginated list

    841578
      Hello, I have a will paginated list of properties and I'm trying to find a way to display the most recently updated entries at the top of the list based on their most current modify date. The approach I'm taking is to use mysql UNION in paginate_by_sql and here's my code:
      #property_ids is an array of property id's that have been edited
      edited_list_where = " WHERE properties.property_id IN (#{property_ids.join(', ')[0..-1]} )"
      
      if(params[:page].to_i > 1)
        property_limit = " LIMIT #{property_ids.length} OFFSET #{(params[:page].to_i*10)}" if (property_ids.length > 0)
      end
      
      Property.paginate_by_sql("(SELECT properties.*, layouts.* FROM properties
                                   LEFT OUTER JOIN layouts ON layouts.property_id = properties.property_id 
                                     #{edited_list_where} 
                                         ORDER BY properties.modify_date ASC #{property_limit}) 
                                UNION
                                   (SELECT properties.*, layouts.title FROM properties
                              LEFT OUTER JOIN layouts ON layouts.property_id = properties.property_id
                                 WHERE properties.property_type='apartment' ORDER BY properties.property_status ASC)", :page => params[:page], :per_page => 10)
      This approach works perfectly when you're on the first page and the edited properties show up first ordered by the last edited, but when you're on any other page, the edited list doesn't appear on top.

      It seems like I'm asking quite a lot and I'm not sure this is even fully possible w/out adding some complicated hacks.

      Any ideas?

      Thanks!
      Clem C