Chaining named scopes with paginate doesn't work with MS SQL Server
Reported by Jon Canady | December 8th, 2008 @ 09:10 PM
Using activerecord-sqlserver-adapter (1.0.0) and Rails 2.2.2. Set up some nice named scopes to do things like filter by user_id and status, and then chained these together with the paginate scope, but I always get the first set of results regardless of the :page parameter. However, if I code up a quick method that hacks together the :conditions parameter and call paginate manually (sans-named-scope) it seems to work fine.
Code is here: http://pastie.org/334222
For the #search method, the following SQL is being executed (for page 6): SELECT * FROM (SELECT TOP 10 * FROM (SELECT TOP 60 * FROM [TReport] WHERE (userid = '3303461' ) ORDER BY last_name) AS tmp1 ORDER BY last_name DESC) AS tmp2 ORDER BY last_name
This works.
The following SQL is being generated for the named parameters, obviously wrong:
SELECT * FROM (SELECT TOP 10 * FROM (SELECT TOP 60 * FROM [T_PAExtensions2009] WHERE (( first_name LIKE '%' OR last_name LIKE '%' OR member_id LIKE '%') AND (ncpdp = '3303461')) ORDER BY last_name) AS tmp1 ) AS tmp2
Is this an issue with activerecord-sqlserver-adapter or will_paginate?
Comments and changes to this ticket
-
Mislav December 10th, 2008 @ 12:26 PM
In the latest application I'm developing, we're really putting named scopes to the max and so far I haven't seen a single MySQL query generated incorrectly.
So, my guess is this is an issue with the adapter or maybe in your code.
There are a number of oddities with the SQL and code you pasted:
- there is mention of "T_PAExtensions2009" table, but the table name is "TReport";
- you pasted
Report.for_pharmacy(session[:userid])but I'm sure you meantfor_user; - there is the
ncpdp = '3303461'condition which is not generated by any of the scopes in your pastie.
It seems to me that the "failing SQL query" above wasn't generated by any of the code presented in the pastie.
-
Jon Canady December 10th, 2008 @ 04:15 PM
Heh. Strictly speaking the boss is wary of production table /column names leaking out, and it looks like my attempt to sanitize our data didn't work so well. Sorry about that, it turns a mostly useful bug report into a confusing mess. The two tables are in fact the same table. The two named scopes (.for_pharmacy, .for_user) reference the same named scope, and ncpdp is also user_id.
But I doubt that will clear things up any more, so I'll look at the adapter and see what I can find.
-
John Tjanaka October 4th, 2010 @ 06:04 PM
- Milestone order changed from “0” to “0”
I know this is a bit late, but I got the same exact issue, using Rails 2.2.22 and activerecord-sqlserver-adapter (1.0.0). However when I upgrade the adapter to 2.2.22, and use will_paginate 2.3.11, the problem is gone.
Please Sign in or create a free account to add a new ticket.
With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.
Create new ticket
Create your profile
Help contribute to this project by taking a few moments to create your personal profile. Create your profile ยป
Everyone's favorite Ruby library for pagination of practically anything!
Jon Canady
Mislav