For a Rails/SQLServer application I’m working on, I had to deal with pagination with custom queries because of the different joins. The mislav-will_paginate plugin works great for MySQL, but for SQL Server, the paginated query generated by the current SQL Server Adapter (I’m using activerecord-sqlserver-adapter-1.0.0.9250) does not work very well. The current implementation is targetted really for SQL Server 2000 and older versions since these versions do not have support for ROW_NUMBER() method. It is a major pain in the butt to do pagination with these databases. With the newer SQL Sever 2005, the job is a bit easier. Microsoft implemented the ROW_NUMBER() method with a convoluted syntax to have better support for pagination, but it is still a drag because of the weird syntax.
Semergence wrote in his blog about patching the SQLServerAdapter to support pagination. Based on his post, I improved ActiveRecord::ConnectionAdapters::SQLServerAdapter::add_limit_offset! to make the query work in a more general way with free-form queries, e.g. queries ran with the paginate_by_sql() method provided by mislav-will_paginate
Include this script in your environment.rb file, or an external file and “require” the file within environment.rb.
# monkey-patching SQLServerAdapter to support SQL Server 2005-style pagination
module ActiveRecord
module ConnectionAdapters
class SQLServerAdapter
def add_limit_offset!(sql, options)
puts sql
options[:offset] ||= 0
options_limit = options[:limit] ? "TOP #{options[:limit]}" : ""
options[:order] ||= if order_by = sql.match(/ORDER BY(.*$)/i)
order_by[1]
else
sql.match('FROM (.+?)\b')[1] + '.id'
end
sql.sub!(/ORDER BY.*$/i, '')
sql.sub!(/SELECT/i, "SELECT #{options_limit} * FROM ( SELECT ROW_NUMBER() OVER( ORDER BY #{options[:order] } ) AS row_num, ")
sql << ") AS t WHERE row_num > #{options[:offset]}”
puts sql
sql
end
end
end
end
The method above monkey-patches the SQLServerAdapter by overwriting the add_limit_offset! method.
Here’s a custom query that I used and the transformed result:
Resource.paginate_by_sql([
%!SELECT resources.*
,skills_count.skill_count
FROM resources
,(
SELECT resource_id
, COUNT(*) AS skill_count
FROM resource_skills
WHERE meta_skill_id IN (1,2,3,4,5,6,7,8,9,10)
GROUP BY resource_id
) AS skills_count
WHERE resources.is_active = ?
AND resources.id = skills_count.resource_id
ORDER BY skill_count DESC
!, true ], :page => page, :per_page => per_page
With :page = 1, :per_page = 2, the resulted SQL is:
SELECT TOP 2 * FROM ( SELECT ROW_NUMBER() OVER( ORDER BY skill_count DESC ) AS row_num, resources.* ,skills_count.skill_count FROM resources ,( SELECT resource_id , COUNT(*) AS skill_count FROM resource_skills WHERE meta_skill_id IN (1,2,3,4,5,6,7,8,9,10) GROUP BY resource_id ) AS skills_count WHERE resources.is_active = 1 AND resources.id = skills_count.resource_id ) AS t WHERE row_num > 0
The will_pagination’s COUNT query is
SELECT COUNT(*) FROM ( SELECT resources.* ,skills_count.skill_count FROM resources ,( SELECT resource_id , COUNT(*) AS skill_count FROM resource_skills WHERE meta_skill_id IN (21,22) GROUP BY resource_id ) AS skills_count WHERE resources.is_active = 1 AND resources.id = skills_count.resource_id ) AS count_table
The ORDER BY part is automatically removed from the main query (which becomes a sub-select) by the plugin to speed up the query. This in turns sanatizes the sql so that SQL Server doesn’t not complain about nested “ORDER BY” within a sub-select. Neat!
The only catch with the current add_limit_offset! is that it does not support ALIAS-ing, because the aliasing confuses the reqex to parse out the ORDER BY condition in the OVER() part of the query.
For regular find() queries, here’s a sample result
Resource.find(:first) # original query: SELECT * FROM resources # transformed: SELECT TOP 1 * FROM ( SELECT ROW_NUMBER() OVER( ORDER BY resources.id ) AS row_number, * FROM resources ) AS t WHERE row_num > 0
Hope this helps and cheers!
