Sorting/Paginating/Filtering Complex Multi-AR Object Tables in Rails
- by Matt Rogish
I have a complex table pulled from a multi-ActiveRecord object array. This listing is a combined display of all of a particular user's "favorite" items (songs, messages, blog postings, whatever). Each of these items is a full-fledged AR object.
My goal is to present the user with a simplified search, sort, and pagination interface. The user need not know that the Song has a singer, and that the Message has an author -- to the end user both entries in the table will be displayed as "User". Thus, the search box will simply be a dropdown list asking them which to search on (User name, created at, etc.). Internally, I would need to convert that to the appropriate object search, combine the results, and display.
I can, separately, do pagination (mislav will_paginate), sorting, and filtering, but together I'm having some problems combining them.
For example, if I paginate the combined list of items, the pagination plugin handles it just fine. It is not efficient since the pagination is happening in the app vs. the DB, but let's assume the intended use-case would indicate the vast majority of the users will have less than 30 favorited items and all other behavior, server capabilities, etc. indicates this will not be a bottleneck.
However, if I wish to sort the list I cannot sort it via the pagination plugin because it relies on the assumption that the result set is derived from a single SQL query, and also that the field name is consistent throughout. Thus, I must sort the merged array via ruby, e.g.
@items.sort_by{ |i| i.whatever }
But, since the items do not share common names, I must first interrogate the object and then call the correct sort by. For example, if the user wishes to sort by user name, if the sorted object is a message, I sort by author but if the object is a song, I sort by singer. This is all very gross and feels quite un-ruby-like.
This same problem comes into play with the filter. If the user filters on the "parent item" (the message's thread, the song's album), I must translate that to the appropriate collection object method. Also gross.
This is not the exact set-up but is close enough. Note that this is a legacy app so changing it is quite difficult, although not impossible. Also, yes there is some DRY that can be done, but don't focus on the style or elegance of the following code. Style/elegance of the SOLUTION is important, however! :D
models:
class User < ActiveRecord::Base
...
has_and_belongs_to_many :favorite_messages, :class_name => "Message"
has_and_belongs_to_many :favorite_songs, :class_name => "Song"
has_many :authored_messages, :class_name => "Message"
has_many :sung_songs, :class_name => "Song"
end
class Message < ActiveRecord::Base
has_and_belongs_to_many :favorite_messages
belongs_to :author, :class_name => "User"
belongs_to :thread
end
class Song < ActiveRecord::Base
has_and_belongs_to_many :favorite_songs
belongs_to :singer, :class_name => "User"
belongs_to :album
end
controller:
def show
u = User.find 123
@items = Array.new
@items << u.favorite_messages
@items << u.favorite_songs
# etc. etc.
@items.flatten!
@items = @items.sort_by{ |i| i.created_at }
@items = @items.paginate :page => params[:page], :per_page => 20
end
def search
# Assume user is searching for username like 'Bob'
u = User.find 123
@items = Array.new
@items << u.favorite_messages.find( :all, :conditions => "LOWER( author ) LIKE LOWER('%bob%')" )
@items << u.favorite_songs.find( :all, :conditions => "LOWER( singer ) LIKE ... " )
# etc. etc.
@items.flatten!
@items = @items.sort_by{ |i| determine appropriate sorting based on user selection }
@items = @items.paginate :page => params[:page], :per_page => 20
end
view:
#index.html.erb
...
<table>
<tr>
<th>Title (sort ASC/DESC links)</th>
<th>Created By (sort ASC/DESC links))</th>
<th>Collection Title (sort ASC/DESC links)</th>
<th>Created At (sort ASC/DESC links)</th>
</tr>
<% @items.each |item| do %>
<%= render { :partial => "message", :locals => item } if item.is_a? Message %>
<%= render { :partial => "song", :locals => item } if item.is_a? Song %>
<%end%>
...
</table>
#message.html.erb
# shorthand, not real ruby
print out message title, author name, thread title, message created at
#song.html.erb
# shorthand
print out song title, singer name, album title, song created at
