在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.

view plain copy to clipboard print ?

1. # monkey-patching SQLServerAdapter to support SQL Server 2005-style pagination   
2. module ActiveRecord   
3.   module ConnectionAdapters   
4.     class SQLServerAdapter   
5.       def add_limit_offset!(sql, options)   
6.         puts sql   
7.         options[:offset] ||= 0   
8.         options_limit = options[:limit] ? "TOP #{options[:limit]}" : ""  
9.         options[:order] ||= if order_by = sql.match(/ORDER BY(.*$)/i)   
10.                               order_by[1]   
11.                             else  
12.                               sql.match('FROM (.+?)\b')[1] + '.id'  
13.                             end  
14.         sql.sub!(/ORDER BY.*$/i, '')   
15.         sql.sub!(/SELECT/i, "SELECT #{options_limit} * FROM ( SELECT ROW_NUMBER() OVER( ORDER BY #{options[:order] } ) AS row_num, ")   
16.         sql << ") AS t WHERE row_num > #{options[:offset]}”   
17.         puts sql   
18.         sql   
19.       end  
20.     end  
21.   end  
22. 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:

view plain copy to clipboard print ?

1. Resource.paginate_by_sql([   
2.       %!SELECT  resources.*   
3.             ,skills_count.skill_count   
4.         FROM resources   
5.             ,(   
6.                 SELECT resource_id   
7.                     , COUNT(*) AS skill_count   
8.                 FROM resource_skills   
9.             WHERE meta_skill_id IN (1,2,3,4,5,6,7,8,9,10)   
10.                 GROUP BY resource_id   
11.             ) AS skills_count   
12.         WHERE resources.is_active = ?   
13.           AND resources.id = skills_count.resource_id   
14.         ORDER BY skill_count DESC  
15.       !, 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:

view plain copy to clipboard print ?

1. SELECT TOP 2 * FROM ( SELECT ROW_NUMBER() OVER( ORDER BY skill_count DESC ) AS row_num, resources.*   
2.     ,skills_count.skill_count   
3.  FROM resources   
4.     ,(   
5.         SELECT resource_id   
6.             , COUNT(*) AS skill_count   
7.         FROM resource_skills   
8.  WHERE meta_skill_id IN (1,2,3,4,5,6,7,8,9,10)   
9.         GROUP BY resource_id   
10.     ) AS skills_count   
11.  WHERE resources.is_active = 1   
12.  AND resources.id = skills_count.resource_id   
13.   
14.  ) 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

view plain copy to clipboard print ?

1. SELECT COUNT(*) FROM (   
2.  SELECT resources.*   
3.     ,skills_count.skill_count   
4.  FROM resources   
5.     ,(   
6.         SELECT resource_id   
7.             , COUNT(*) AS skill_count   
8.         FROM resource_skills   
9.  WHERE meta_skill_id IN (21,22)   
10.         GROUP BY resource_id   
11.     ) AS skills_count   
12.  WHERE resources.is_active = 1   
13.  AND resources.id = skills_count.resource_id   
14.  ) 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

view plain copy to clipboard print ?

1. Resource.find(:first)   
2. # original query:  SELECT * FROM resources   
3. # 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!