For posterity''s sake (though it doesn''t seem there are many
using SQL Server
on the list), here''s the solution to the pagination problem I was
having.
When using the paginator helper, the :order_by parameter must be specified
and must include the direction in upper case letters ("ASC",
"DESC"). E.g.:
@company_pages, @companies = paginate :companies,
:order_by => "id ASC",
:per_page => 20
The following will not paginate correctly:
# doesn''t work
@company_pages, @companies = paginate :companies,
:order_by => "id asc",
:per_page => 20
# also doesn''t work
@company_pages, @companies = paginate :companies,
:order_by => "id asc",
:per_page => 20
Allow me to expound on the reason. The SQL Server adapter modifies the SQL
string for a query specifying a limit and offset in the following function:
def add_limit_with_offset!(sql, limit, offset)
Since the Transact-SQL implemented in Microsoft SQL Server doesn''t
include a
LIMITS clause for the SELECT statement, add_limit_with_offset runs the
following query:
SELECT * FROM ( SELECT TOP <page-size> * FROM ( SELECT TOP <offset>
* FROM
units ORDER BY <order-clause>) AS tmp1 ORDER BY
<order-clause-reversed>) AS
tmp2 ORDER BY id <order-clause>
To extract the ORDER BY clause from the SELECT statement,
add_limit_with_offset calls:
368 def get_order_by(sql)
369 return sql, sql.gsub(/\s*DESC\s*/, "").gsub(/\s*ASC\s*/, "
DESC")
370 end
Note the uppercase "ASC" and "DESC" in line 369, which
require the :order_by
parameter to include the order direction in uppercase letters.
Happy rails to you!
Josh
-----Original Message-----
From: Joshua Rechanek [mailto:josh-zulXKkpI+OpZroRs9YW3xA@public.gmane.org]
Sent: Tuesday, March 29, 2005 9:07 PM
To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
Subject: Problems with Paginator and SQL Server
This is a follow-up to my previous message ([Rails] Can''t get
pagination to
work with Pagination Helper).
In a nutshell, the Paginator helper wasn''t working as advertised for
me.
I''m running Rails 0.11.1 on Windows XP Professional with Microsoft SQL
Server 2000 (MSDE, actually, but the same engine). When I moved from page
to page, the data set displayed stayed exactly the same, as if anchored by
some great boulder.
I decided to experiment and duplicated my table structure on MySQL
(4.0.21-nt). I inserted my data, pointed my database.yml at MySQL,
restarted WebBrick, and, wouldn''t you know it, pagination works like a
charm.
Now, before being berated about my choice of database software, let me
indicate that this is a client constraint, not self-imposed. So, is there
something a bit off with the SQL Server adapter?
I shall continue to dig and post whatever I find. If anybody has run into
the problem and has a fix, any tip would be appreciated.
Josh