Lighthouse has a new layout. Prefer the old one? Return to the old layout, and switch back any time from the link at the top of each page.

Chaining named scopes with paginate doesn't work with MS SQL Server

#254

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?

Reported by Jon Canady · December 8th, 2008 @ 09:10 PM

State: new
Milestone: none
Assigned to: Mislav Mislav

Activity

  1. Mislav
    Mislav

    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:

    1. there is mention of "T_PAExtensions2009" table, but the table name is "TReport";
    2. you pasted Report.for_pharmacy(session[:userid]) but I'm sure you meant for_user;
    3. 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.

    December 10th, 2008 @ 12:26 PM

  2. Jon Canady
    Jon Canady

    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.

    December 10th, 2008 @ 04:15 PM

  3. John Tjanaka
    John Tjanaka
    • 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.

    October 4th, 2010 @ 06:04 PM

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.