Comments on Seth Ladd's Blog: Fixing Rails Pagination for SQL Server

Bless you! I was going crazy with this function a...

2011-03-21T21:09:06.402-07:00

Bless you! I was going crazy with this function adding extra back-slashes to the query, among other things. I did have to make a couple changes to the snippet you posted - there seemed to be a couple extra "n"s in there:

def add_limit_offset!(sql, options)
if options[:limit] and options[:offset]
options[:order] ||= sql.match('FROM (.*) ')[1] + '.id'
sql.sub!(/ORDER BY.*$/i, '')
sql.sub!(/SELECT/i,
"SELECT row_number() over( order by #{options[:order]} ) as row_num, ")
sql.replace("SELECT TOP #{options[:limit]} * FROM (#{sql}) as tmp1 " +
"WHERE row_num > #{options[:offset]}")
end
end

Hi everyone,

I posted an updated version of...

2008-09-26T01:32:02.000-07:00

Hi everyone,

I posted an updated version of the patch for SQL Server 2005 at my blog

http://alexle.net/archives/292

Cheers!

Alex

Bless you! I was going crazy with this function a...

2007-12-28T06:37:39.000-08:00

Thanks for this! I've been banging my head ag...

2007-10-17T00:57:22.000-07:00

Thanks for this! I've been banging my head against the wall, trying to get some intelligent support for :offset on SQL Server.

I'm not sure what the deal is with the "n"s at the end of lines 10 and 11 (inside the strings). I assumed they were typos, and it seems to work for me without them (but not with them).

Also, your monkey-patch broke two things (that I could find): the ability to use :join or :include, and the ability to use just :limit (without :offset).

I *think* that I fixed both of those bugs here: http://pastie.caboo.se/108142

module ActiveRecord
module ConnectionAdapters
class SQLServerAdapter

def add_limit_offset!(sql, options)
if options[:limit] and options[:offset]
options[:order] ||= sql.match('FROM (\w*) ')[1] + '.id'
sql.sub!(/ORDER BY.*$/i, '')
sql.sub!(/SELECT/i,
"SELECT row_number() over( order by #{options[:order]} ) as row_num, ")
sql.replace("select top #{options[:limit]} * from (#{sql}) as tmp_table1 " +
"where row_num > #{options[:offset]}")
elsif sql !~ /^\s*SELECT (@@|COUNT\()/i
sql.sub!(/^\s*SELECT(\s+DISTINCT)?/i) do
"SELECT#{$1} TOP #{options[:limit]}"
end unless options[:limit].nil?
end
end

end
end
end

Tried it against ActiveScaffold and it breaks on s...

2007-10-11T23:04:42.000-07:00

Tried it against ActiveScaffold and it breaks on some JOINS

Also tried
"WITH MyTmp AS
(SELECT ROW_NUMBER() OVER( order by #{options[:order]} ) as row_num , * FROM (#{sql})
SELECT * FROM MyTmp where row-num between #{options[:offset]} AND ( #{options[:offset]} + #{options[:limit]} - 1)"

But the OLE connector couldn't understand - probably needs a native 2005 connector

Cool idea though -

Comments on Seth Ladd's Blog: Fixing Rails Pagination for SQL Server

Bless you! I was going crazy with this function a...

Hi everyone,I posted an updated version of...

Bless you! I was going crazy with this function a...

Thanks for this! I've been banging my head ag...

Tried it against ActiveScaffold and it breaks on s...

Hi everyone,

I posted an updated version of...