在Rails下连接SQL Server 2005 分页

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

  # 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

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

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

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

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!