Neville Burnell
2005-Aug-10 01:11 UTC
SQL generated by paginator problems [and a workaround]
Hi,
I''m playing with paginators in Rails 13.1 with SQL Server, and
I''m
getting incorrect results for the last page, which should be partially
filled, but is returning a full page which repeats some items from the
second last page. My paginator code is pretty standard stuff:
@pages, @sites = paginate :sites,
:conditions => [''dealer_id = ?'',
dealer_id],
:order_by => "#@order_by #@order_seq",
:per_page => 20
The problem seems to be the SQL generated by the paginator, which does
not use "offset" or "limit" as one might expect ... Instead
its trying
to be overly tricky:
SELECT * FROM
(SELECT TOP 20 * FROM
(SELECT TOP 140 * FROM sites WHERE dealer_id = 1629 ORDER BY name asc )
AS tmp1 ORDER BY name DESC )
AS tmp2 ORDER BY name asc
There are actually only 134 rows in this set, so the last page should
return 14 rows. Instead, it returns a full 20 rows due to the "TOP
20".
I changed the code to use the "classic" style [as per the API doc] as
follows, but it didn''t help ... The sql was unchanged:
n_sites = Sites.count([''dealer_id = ?'', dealer_id])
@pages = Paginator.new self, n_sites, 20, @params[''page'']
@sites = Sites.find :all,
:order => "#@order_by #@order_seq",
:conditions => [''dealer_id = ?'',
dealer_id],
:limit => @pages.items_per_page,
:offset => @pages.current.offset
I then changed the :limit as follows, and the SQL generated is now
correct:
n_sites = Sites.count([''dealer_id = ?'', dealer_id])
@pages = Paginator.new self, n_sites, 20, @params[''page'']
@sites = Sites.find :all,
:order => "#@order_by #@order_seq",
:conditions => [''dealer_id = ?'',
dealer_id],
:limit => [n_sites - @pages.current.offset,
@pages.items_per_page].min,
:offset => @pages.current.offset
Which generates:
SELECT * FROM
(SELECT TOP 14 * FROM
(SELECT TOP 134 * FROM sites WHERE dealer_id = 1629 ORDER BY name asc )
AS tmp1 ORDER BY name DESC )
AS tmp2 ORDER BY name asc
Is this a known issue, or am I missing something?
It''s just the way the SQL Server adapter works. I think it''s because SQL server doesn''t have an ''offset'' feature when fetching results. On 8/9/05, Neville Burnell <Neville.Burnell-uEDVyssJ3mUpAS55Wn97og@public.gmane.org> wrote:> Hi, > > I''m playing with paginators in Rails 13.1 with SQL Server, and I''m > getting incorrect results for the last page, which should be partially > filled, but is returning a full page which repeats some items from the > second last page. My paginator code is pretty standard stuff: > > @pages, @sites = paginate :sites, > :conditions => [''dealer_id = ?'', dealer_id], > :order_by => "#@order_by #@order_seq", > :per_page => 20 > > The problem seems to be the SQL generated by the paginator, which does > not use "offset" or "limit" as one might expect ... Instead its trying > to be overly tricky: > > SELECT * FROM > (SELECT TOP 20 * FROM > (SELECT TOP 140 * FROM sites WHERE dealer_id = 1629 ORDER BY name asc ) > AS tmp1 ORDER BY name DESC ) > AS tmp2 ORDER BY name asc > > There are actually only 134 rows in this set, so the last page should > return 14 rows. Instead, it returns a full 20 rows due to the "TOP 20". > > I changed the code to use the "classic" style [as per the API doc] as > follows, but it didn''t help ... The sql was unchanged: > > n_sites = Sites.count([''dealer_id = ?'', dealer_id]) > @pages = Paginator.new self, n_sites, 20, @params[''page''] > @sites = Sites.find :all, > :order => "#@order_by #@order_seq", > :conditions => [''dealer_id = ?'', dealer_id], > :limit => @pages.items_per_page, > :offset => @pages.current.offset > > I then changed the :limit as follows, and the SQL generated is now > correct: > > n_sites = Sites.count([''dealer_id = ?'', dealer_id]) > @pages = Paginator.new self, n_sites, 20, @params[''page''] > @sites = Sites.find :all, > :order => "#@order_by #@order_seq", > :conditions => [''dealer_id = ?'', dealer_id], > :limit => [n_sites - @pages.current.offset, > @pages.items_per_page].min, > :offset => @pages.current.offset > > Which generates: > > SELECT * FROM > (SELECT TOP 14 * FROM > (SELECT TOP 134 * FROM sites WHERE dealer_id = 1629 ORDER BY name asc ) > AS tmp1 ORDER BY name DESC ) > AS tmp2 ORDER BY name asc > > > Is this a known issue, or am I missing something? > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
Neville Burnell
2005-Aug-10 01:22 UTC
RE: SQL generated by paginator problems [and a workaround]
Actually I think it''s a bug somewhere in the paginator code, because I have another paginator [again using the SQL Server Adapter] which does work correctly ... The main difference between the two paginators is that the working one does not have any ":conditions", so it seems the paginator code isnt correctly getting the result set size when :conditions are in play -----Original Message----- From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of H M Sent: Wednesday, 10 August 2005 11:14 AM To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org Subject: Re: [Rails] SQL generated by paginator problems [and a workaround] It''s just the way the SQL Server adapter works. I think it''s because SQL server doesn''t have an ''offset'' feature when fetching results. On 8/9/05, Neville Burnell <Neville.Burnell-uEDVyssJ3mUpAS55Wn97og@public.gmane.org> wrote:> Hi, > > I''m playing with paginators in Rails 13.1 with SQL Server, and I''m > getting incorrect results for the last page, which should be partially> filled, but is returning a full page which repeats some items from the> second last page. My paginator code is pretty standard stuff: > > @pages, @sites = paginate :sites, > :conditions => [''dealer_id = ?'', dealer_id], > :order_by => "#@order_by #@order_seq", > :per_page => 20 > > The problem seems to be the SQL generated by the paginator, which does> not use "offset" or "limit" as one might expect ... Instead its trying> to be overly tricky: > > SELECT * FROM > (SELECT TOP 20 * FROM > (SELECT TOP 140 * FROM sites WHERE dealer_id = 1629 ORDER BY name asc > ) AS tmp1 ORDER BY name DESC ) AS tmp2 ORDER BY name asc > > There are actually only 134 rows in this set, so the last page should > return 14 rows. Instead, it returns a full 20 rows due to the "TOP20".> > I changed the code to use the "classic" style [as per the API doc] as > follows, but it didn''t help ... The sql was unchanged: > > n_sites = Sites.count([''dealer_id = ?'', dealer_id]) @pages = > Paginator.new self, n_sites, 20, @params[''page''] @sites = Sites.find > :all, > :order => "#@order_by #@order_seq", > :conditions => [''dealer_id = ?'', dealer_id], > :limit => @pages.items_per_page, > :offset => @pages.current.offset > > I then changed the :limit as follows, and the SQL generated is now > correct: > > n_sites = Sites.count([''dealer_id = ?'', dealer_id]) @pages = > Paginator.new self, n_sites, 20, @params[''page''] @sites = Sites.find > :all, > :order => "#@order_by #@order_seq", > :conditions => [''dealer_id = ?'', dealer_id], > :limit => [n_sites - @pages.current.offset, > @pages.items_per_page].min, > :offset => @pages.current.offset > > Which generates: > > SELECT * FROM > (SELECT TOP 14 * FROM > (SELECT TOP 134 * FROM sites WHERE dealer_id = 1629 ORDER BY name asc > ) AS tmp1 ORDER BY name DESC ) AS tmp2 ORDER BY name asc > > > Is this a known issue, or am I missing something? > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >_______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails