Ruby on Rails

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?