How do I Order on common attribute of two models in the DB?

Question

If i have two tables Books, CDs with corresponding models.

I want to display to the user a list of books and CDs. I also want to be able to sort this list on common attributes (release date, genre, price, etc.). I also have basic filtering on the common attributes.

The list will be large so I will be using pagination in manage the load.

items = []
items << CD.all(:limit => 20, :page => params[:page], :order => "genre ASC")
items << Book.all(:limit => 20, :page => params[:page], :order => "genre ASC")
re_sort(items,"genre ASC")

Right now I am doing two queries concatenating them and then sorting them. This is very inefficient. Also this breaks down when I use paging and filtering. If I am on page 2 of how do I know what page of each table individual table I am really on? There is no way to determine this information without getting all items from each table.

I have though that if I create a new Class called items that has a one to one relationship with either a Book or CD and do something like

Item.all(:limit => 20, :page => params[:page], :include => [:books, :cds], :order => "genre ASC")

However this gives back an ambiguous error. So can only be refined as

Item.all(:limit => 20, :page => params[:page], :include => [:books, :cds], :order => "books.genre ASC")

And does not interleave the books and CDs in a way that I want.

Any suggestions.

Answer 1

The Item model idea will work, but you are going to have to pull out all the common attributes and store them in Item. Then update all you forms to store those specific values in the new table. This way, adding a different media type later would be easier.

Update after comment:

What about a union? Do find_by_sql and hand-craft the SQL. It won't be simple, but your DB scheme isn't simple. So do something like this:

class Item < ActiveModel::Base
  attr_reader :title, :genre, :media_type, ...

  def self.search(options = {})
    # parse options for search criteria, sorting, page, etc.
    # will leave that for you :)
    sql = <<-SQL
    (SELECT id, title, genre, 'cd' AS media_type
     FROM cds
     WHERE ???
     ORDER BY ???
     LIMIT ???
    ) UNION
    (SELECT id, title, genre, 'book' AS media_type
     FROM books
     WHERE ???
     ORDER BY ???
     LIMIT ???
    )
    SQL
    items = find_by_sql(sql) 
  end
end

untested

Or something to that effect. Basically build the item rows on the fly (you will need a blank items table). The media_type column is so you know how to create the links when displaying the results.

Or...

Depending on how often the data changes you could, gasp, duplicate the needed data into the items table with a rake task on a schedule.

Answer 2

You can paginate on a results array, so leave pagination out of the invidual model queries, and add it to your results array:

re_sort(items,"genre ASC").paginate(:page => params[:page], :per_page => items_per_page)

Answer 3

You say you can't change how books and CDs are stored in the database, but could you add an items view? Do you have any control over the database at all?

CREATE VIEW items
  (id, type, title, genre, created_at, updated_at)
  AS
  SELECT b.id, 'Book', b.title, b.genre, b.created_at, b.updated_at
    FROM books b
  UNION
  SELECT c.id, 'CD', c.title, c.genre, c.created_at, c.updated_at
    FROM cds c;