Custom Pagination.rb

# Get all the properties that aren't fulltext (which will cause the query to break)
props = Product.properties.reject{|p| p.type == DataMapper::Types::Text }.map{|p| "`products`.`#{p.name}`" }.join(', ')
# Get categories before hand as it tends to be a little faster. 
@categories = Category.all( :name.like => "%#{params[:search]}%" ) 
# Do the massive query.
# You'll notice the << -1 on the queries.  Since @category.id is an autoincrement it'll never be -1 and this is easier than a messey ternary statement.  I'd imagine the messy statement could be a little faster, however.

@t_products = Product.find_by_sql(["SELECT " + props + "
  FROM products 
    LEFT JOIN categories_products ON categories_products.product_id = products.id 
    INNER JOIN categories ON categories_products.category_id = categories.id 
  WHERE 
    `products`.`name` LIKE ? OR categories.id IN ?
  GROUP BY " + props + "
  ORDER BY 
    ( CASE WHEN sort_order IS NULL THEN 0 ELSE 1 END ) desc, 
    sort_order ASC, 
    ( CASE WHEN LOCATE(?, categories.name) IS NULL THEN 999 WHEN LOCATE(?, categories.name) = 0 THEN 999 ELSE LOCATE(?, categories.name) END ) ASC",
  "%#{params[:search]}%", @categories.map{ |c| c.id } << -1,"#{params[:search]}", "#{params[:search]}", "#{params[:search]}"]).to_a
# We force it to an array, otherwise WillPageinate sees that it's a DataMapper::Collection and tries to convert it to a "SELECT FROM products WHERE id in ( ... )" which is definitely NOT what we want

# Do the custom pagination.  This could be sped up even quicker and use less memory by running the above query twice - once with COUNT(*) and once with OFFSET n, LIMIT n
@products = WillPaginate::Collection.create(params[:page] || 1, 20) do |pager|
  page = params[:page].nil? ? 1 : params[:page].to_i
  pager.replace(@t_products[((page-1)*20)..(page*20 - 1)])
  pager.total_entries = @t_products.length
end