Another comment on the Four Days on Rails documentation.
The code provided to sort the items by category has an unintended side effect. If an item contains a reference to a deleted category then the code outputs ‘Unfiled’. However, when a list of items with ‘Unfiled’ item(s) is sorted by category the ‘Unfiled’ item(s) disappear since the equi-join (i.e. inner join) is too restrictive to handle this properly.
Original code:
def list_by_category @item_pages = Paginator.new self, Item.count, 10, @params['page'] @items = Item.find_by_sql 'SELECT i.*, c.category FROM categories c, items i ' + 'WHERE ( c.id = i.category_id ) '+ 'ORDER BY c.category ' + 'LIMIT 10 ' + "OFFSET #{@item_pages.current.to_sql[1]}" render_action 'list' end
I came up with some new SQL:
SELECT i.*, c.category FROM items i LEFT JOIN categories c on i.category_id = c.id ORDER BY c.category LIMIT 10
However the ‘Unfiled’ item(s) always appear at the top of the ordering since <NULL> is sorted first. I found a way to force MySQL NULL values to the end of the sorted list. Therefore the SQL becomes:
SELECT i.*, c.category, category IS NULL AS isnull FROM items i LEFT JOIN categories c on i.category_id = c.id ORDER BY isnull ASC, category LIMIT 10
Resulting code to sort the items by category:
def list_by_category @item_pages = Paginator.new self, Item.count, 10, @params['page'] @items = Item.find_by_sql 'SELECT i.*, c.category, category IS NULL AS isnull FROM items i ' + 'LEFT JOIN categories c on i.category_id = c.id ' + 'ORDER BY isnull ASC, category, due_date ' + 'LIMIT 10 ' "OFFSET #{@item_pages.current.to_sql[1]}" render_action 'list' end
This approach outputs all the item rows but <NULL> category entries mess up the sort order.
def list_by_category @item_pages = Paginator.new self, Item.count, 10, @params['page'] @items = Item.find(:all, :include=> :category, :order=>'category') render_action 'list' end
Any ideas to fix this approach and not resort to using SQL?