All,
I have the following controller/model:
class Task < ActiveRecord::Base
set_table_name "tasks"
set_primary_key "task_id"
end
class TaskController < ApplicationController
def index
list
render :action => 'list'
end
def task
end
def list
@task_pages, @tasks = paginate :task, :per_page => 10
end
def show
@task = Task.find(params[:id])
end
def new
@task = Task.new
end
def create
@task = Task.new(params[:task])
if @task.save
flash[:notice] = 'Task was successfully created.'
redirect_to :action => 'list'
else
render :action => 'new'
end
end
def edit
@task = Task.find(params[:id])
end
def update
@task = Task.find(params[:id])
if @task.update_attributes(params[:task])
flash[:notice] = 'Task was successfully updated.'
redirect_to :action => 'show', :id => @task
else
render :action => 'edit'
end
end
def destroy
Task.find(params[:id]).destroy
redirect_to :action => 'list'
end
end
The initial page displays fine, but when I click the "Next Page" link,
nothing changes on the screen. I am using the scaffold generate for the code.
Ron
_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails
Give the pagination an ordering parameter. MS Sql doesn''t support limit/top type command like most SQL''s do, so the adapter generates a really complex query that gets everything upto and including your page, then flips it and gets you page off the top, and then flips it again to get you page in the right order. This means an order has to be specified, otherwise it can''t flip (It really should default to something, maybe I''ll patch that) def list @task_pages, @tasks = paginate :task, :per_page => 10, :order_by=>"id ASC" End David> -----Original Message----- > From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org [mailto:rails- > bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Ron DiFrango > Sent: Tuesday, September 27, 2005 2:19 PM > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > Subject: [Rails] MS Sql Server and pagination > > All, > > I have the following controller/model: > > class Task < ActiveRecord::Base > set_table_name "tasks" > set_primary_key "task_id" > end > > class TaskController < ApplicationController > def index > list > render :action => ''list'' > end > def task > end > def list > @task_pages, @tasks = paginate :task, :per_page => 10 > end > def show > @task = Task.find(params[:id]) > end > def new > @task = Task.new > end > def create > @task = Task.new(params[:task]) > if @task.save > flash[:notice] = ''Task was successfully created.'' > redirect_to :action => ''list'' > else > render :action => ''new'' > end > end > def edit > @task = Task.find(params[:id]) > end > def update > @task = Task.find(params[:id]) > if @task.update_attributes(params[:task]) > flash[:notice] = ''Task was successfully updated.'' > redirect_to :action => ''show'', :id => @task > else > render :action => ''edit'' > end > end > def destroy > Task.find(params[:id]).destroy > redirect_to :action => ''list'' > end > end > > The initial page displays fine, but when I click the "Next Page" link, > nothing changes on the screen. I am using the scaffold generate forthe> code. > > Ron==============================================================================CONFIDENTIALITY NOTICE: The information in this electronic message (including any attachments) is confidential and may be privileged or proprietary. If you are not the intended recipient, any dissemination, disclosure, copying, downloading, or other use of the information is prohibited and unauthorized, and may be unlawful, regardless of address or routing. If you are not the intended recipient, please inform the sender immediately and permanently delete and destroy the original and any copies of this message, including any attachments. ==============================================================================
David, I am not sure what you are talking about, but you can select the top X number like the folowing: SELECT TOP 100 * FROM [TIM].[dbo].[contact]; Ron -----Original Message----- From: David Chilton [mailto:david_chilton@mckee.com] Sent: Tue 9/27/2005 4:02 PM To: rails@lists.rubyonrails.org Cc: Subject: RE: [Rails] MS Sql Server and pagination Give the pagination an ordering parameter. MS Sql doesn't support limit/top type command like most SQL's do, so the adapter generates a really complex query that gets everything upto and including your page, then flips it and gets you page off the top, and then flips it again to get you page in the right order. This means an order has to be specified, otherwise it can't flip (It really should default to something, maybe I'll patch that) def list @task_pages, @tasks = paginate :task, :per_page => 10, :order_by=>"id ASC" End David > -----Original Message----- > From: rails-bounces@lists.rubyonrails.org [mailto:rails- > bounces@lists.rubyonrails.org] On Behalf Of Ron DiFrango > Sent: Tuesday, September 27, 2005 2:19 PM > To: rails@lists.rubyonrails.org > Subject: [Rails] MS Sql Server and pagination > > All, > > I have the following controller/model: > > class Task < ActiveRecord::Base > set_table_name "tasks" > set_primary_key "task_id" > end > > class TaskController < ApplicationController > def index > list > render :action => 'list' > end > def task > end > def list > @task_pages, @tasks = paginate :task, :per_page => 10 > end > def show > @task = Task.find(params[:id]) > end > def new > @task = Task.new > end > def create > @task = Task.new(params[:task]) > if @task.save > flash[:notice] = 'Task was successfully created.' > redirect_to :action => 'list' > else > render :action => 'new' > end > end > def edit > @task = Task.find(params[:id]) > end > def update > @task = Task.find(params[:id]) > if @task.update_attributes(params[:task]) > flash[:notice] = 'Task was successfully updated.' > redirect_to :action => 'show', :id => @task > else > render :action => 'edit' > end > end > def destroy > Task.find(params[:id]).destroy > redirect_to :action => 'list' > end > end > > The initial page displays fine, but when I click the "Next Page" link, > nothing changes on the screen. I am using the scaffold generate for the > code. > > Ron ==============================================================================CONFIDENTIALITY NOTICE: The information in this electronic message (including any attachments) is confidential and may be privileged or proprietary. If you are not the intended recipient, any dissemination, disclosure, copying, downloading, or other use of the information is prohibited and unauthorized, and may be unlawful, regardless of address or routing. If you are not the intended recipient, please inform the sender immediately and permanently delete and destroy the original and any copies of this message, including any attachments. ============================================================================= _______________________________________________ Rails mailing list Rails@lists.rubyonrails.org http://lists.rubyonrails.org/mailman/listinfo/rails _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Right, but you can''t offset the top by 100 To get the second page I want the top 100 starting with number 101, SQL Server doesn''t have syntax for that, which is why the adapter does this: Select * FROM ( SELECT TOP 100 FROM ( SELECT TOP (offset + 1)*100 FROM TIM.dbo.contacts ORDER BY id ASC) ORDER BY id DESC ) ORDER BY id ASC If you don''t pass in an order parameter, then the sql code doesn''t work right because the adapter doesn''t default to one. David> -----Original Message----- > From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org [mailto:rails- > bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Ron DiFrango > Sent: Tuesday, September 27, 2005 4:17 PM > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > Subject: RE: [Rails] MS Sql Server and pagination > > David, > > I am not sure what you are talking about, but you can select the top X > number like the folowing: > > SELECT TOP 100 * FROM [TIM].[dbo].[contact]; > > Ron > > -----Original Message----- > From: David Chilton [mailto:david_chilton-Yt2XQmYAGVYAvxtiuMwx3w@public.gmane.org] > Sent: Tue 9/27/2005 4:02 PM > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > Cc: > Subject: RE: [Rails] MS Sql Server and pagination > > > > Give the pagination an ordering parameter. > > MS Sql doesn''t support limit/top type command like most SQL''sdo, so> the > adapter generates a really complex query that gets everythingupto> and > including your page, then flips it and gets you page off thetop,> and > then flips it again to get you page in the right order. Thismeans> an > order has to be specified, otherwise it can''t flip (It reallyshould> default to something, maybe I''ll patch that) > > def list > @task_pages, @tasks = paginate :task, :per_page => 10, > :order_by=>"id ASC" > End > > > David > > > -----Original Message----- > > From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org [mailto:rails- > > bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Ron DiFrango > > Sent: Tuesday, September 27, 2005 2:19 PM > > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > Subject: [Rails] MS Sql Server and pagination > > > > All, > > > > I have the following controller/model: > > > > class Task < ActiveRecord::Base > > set_table_name "tasks" > > set_primary_key "task_id" > > end > > > > class TaskController < ApplicationController > > def index > > list > > render :action => ''list'' > > end > > def task > > end > > def list > > @task_pages, @tasks = paginate :task, :per_page => 10 > > end > > def show > > @task = Task.find(params[:id]) > > end > > def new > > @task = Task.new > > end > > def create > > @task = Task.new(params[:task]) > > if @task.save > > flash[:notice] = ''Task was successfully created.'' > > redirect_to :action => ''list'' > > else > > render :action => ''new'' > > end > > end > > def edit > > @task = Task.find(params[:id]) > > end > > def update > > @task = Task.find(params[:id]) > > if @task.update_attributes(params[:task]) > > flash[:notice] = ''Task was successfully updated.'' > > redirect_to :action => ''show'', :id => @task > > else > > render :action => ''edit'' > > end > > end > > def destroy > > Task.find(params[:id]).destroy > > redirect_to :action => ''list'' > > end > > end > > > > The initial page displays fine, but when I click the "NextPage"> link, > > nothing changes on the screen. I am using the scaffoldgenerate> for > the > > code. > > > > Ron > >===================================================================> ==========CONFIDENTIALITY NOTICE: The information in this electronic> message (including any attachments) is confidential and may beprivileged> or proprietary. If you are not the intended recipient, anydissemination,> disclosure, copying, downloading, or other use of the information is > prohibited and unauthorized, and may be unlawful, regardless ofaddress or> routing. If you are not the intended recipient, please inform thesender> immediately and permanently delete and destroy the original and anycopies> of this message, including any attachments. >===================================================================> =========> _______________________________________________> Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >==============================================================================CONFIDENTIALITY NOTICE: The information in this electronic message (including any attachments) is confidential and may be privileged or proprietary. If you are not the intended recipient, any dissemination, disclosure, copying, downloading, or other use of the information is prohibited and unauthorized, and may be unlawful, regardless of address or routing. If you are not the intended recipient, please inform the sender immediately and permanently delete and destroy the original and any copies of this message, including any attachments. ==============================================================================
Got it, it would be nice to have a workaround for SQL Server in this case. What about doing some sort of between on the ID field and just assume that the increment is 1 and it starts at one so you could do: SELECT * FROM [TIM].[dbo].[contact] where contact_id between 1 and 10; And increment it that way? -----Original Message----- From: David Chilton [mailto:david_chilton@mckee.com] Sent: Tue 9/27/2005 4:24 PM To: rails@lists.rubyonrails.org Cc: Subject: RE: [Rails] MS Sql Server and pagination Right, but you can't offset the top by 100 To get the second page I want the top 100 starting with number 101, SQL Server doesn't have syntax for that, which is why the adapter does this: Select * FROM ( SELECT TOP 100 FROM ( SELECT TOP (offset + 1)*100 FROM TIM.dbo.contacts ORDER BY id ASC) ORDER BY id DESC ) ORDER BY id ASC If you don't pass in an order parameter, then the sql code doesn't work right because the adapter doesn't default to one. David > -----Original Message----- > From: rails-bounces@lists.rubyonrails.org [mailto:rails- > bounces@lists.rubyonrails.org] On Behalf Of Ron DiFrango > Sent: Tuesday, September 27, 2005 4:17 PM > To: rails@lists.rubyonrails.org > Subject: RE: [Rails] MS Sql Server and pagination > > David, > > I am not sure what you are talking about, but you can select the top X > number like the folowing: > > SELECT TOP 100 * FROM [TIM].[dbo].[contact]; > > Ron > > -----Original Message----- > From: David Chilton [mailto:david_chilton@mckee.com] > Sent: Tue 9/27/2005 4:02 PM > To: rails@lists.rubyonrails.org > Cc: > Subject: RE: [Rails] MS Sql Server and pagination > > > > Give the pagination an ordering parameter. > > MS Sql doesn't support limit/top type command like most SQL's do, so > the > adapter generates a really complex query that gets everything upto > and > including your page, then flips it and gets you page off the top, > and > then flips it again to get you page in the right order. This means > an > order has to be specified, otherwise it can't flip (It really should > default to something, maybe I'll patch that) > > def list > @task_pages, @tasks = paginate :task, :per_page => 10, > :order_by=>"id ASC" > End > > > David > > > -----Original Message----- > > From: rails-bounces@lists.rubyonrails.org [mailto:rails- > > bounces@lists.rubyonrails.org] On Behalf Of Ron DiFrango > > Sent: Tuesday, September 27, 2005 2:19 PM > > To: rails@lists.rubyonrails.org > > Subject: [Rails] MS Sql Server and pagination > > > > All, > > > > I have the following controller/model: > > > > class Task < ActiveRecord::Base > > set_table_name "tasks" > > set_primary_key "task_id" > > end > > > > class TaskController < ApplicationController > > def index > > list > > render :action => 'list' > > end > > def task > > end > > def list > > @task_pages, @tasks = paginate :task, :per_page => 10 > > end > > def show > > @task = Task.find(params[:id]) > > end > > def new > > @task = Task.new > > end > > def create > > @task = Task.new(params[:task]) > > if @task.save > > flash[:notice] = 'Task was successfully created.' > > redirect_to :action => 'list' > > else > > render :action => 'new' > > end > > end > > def edit > > @task = Task.find(params[:id]) > > end > > def update > > @task = Task.find(params[:id]) > > if @task.update_attributes(params[:task]) > > flash[:notice] = 'Task was successfully updated.' > > redirect_to :action => 'show', :id => @task > > else > > render :action => 'edit' > > end > > end > > def destroy > > Task.find(params[:id]).destroy > > redirect_to :action => 'list' > > end > > end > > > > The initial page displays fine, but when I click the "Next Page" > link, > > nothing changes on the screen. I am using the scaffold generate > for > the > > code. > > > > Ron > > =================================================================== > ==========CONFIDENTIALITY NOTICE: The information in this electronic > message (including any attachments) is confidential and may be privileged > or proprietary. If you are not the intended recipient, any dissemination, > disclosure, copying, downloading, or other use of the information is > prohibited and unauthorized, and may be unlawful, regardless of address or > routing. If you are not the intended recipient, please inform the sender > immediately and permanently delete and destroy the original and any copies > of this message, including any attachments. > =================================================================== > ========= > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails > ==============================================================================CONFIDENTIALITY NOTICE: The information in this electronic message (including any attachments) is confidential and may be privileged or proprietary. If you are not the intended recipient, any dissemination, disclosure, copying, downloading, or other use of the information is prohibited and unauthorized, and may be unlawful, regardless of address or routing. If you are not the intended recipient, please inform the sender immediately and permanently delete and destroy the original and any copies of this message, including any attachments. ============================================================================= _______________________________________________ Rails mailing list Rails@lists.rubyonrails.org http://lists.rubyonrails.org/mailman/listinfo/rails _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
I wouldn''t want to do that because the results may not be sequential in the id field, or their (God Forbid) may not be one. I''m going to look at patching the section of the Sql server adapter to automatically order by the primary key if there is nothing specified for order by for just this type of query. David ________________________________ From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Ron DiFrango Sent: Tuesday, September 27, 2005 4:49 PM To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org Subject: RE: [Rails] MS Sql Server and pagination Got it, it would be nice to have a workaround for SQL Server in this case. What about doing some sort of between on the ID field and just assume that the increment is 1 and it starts at one so you could do: SELECT * FROM [TIM].[dbo].[contact] where contact_id between 1 and 10; And increment it that way? -----Original Message----- From: David Chilton [mailto:david_chilton-Yt2XQmYAGVYAvxtiuMwx3w@public.gmane.org] Sent: Tue 9/27/2005 4:24 PM To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org Cc: Subject: RE: [Rails] MS Sql Server and pagination Right, but you can''t offset the top by 100 To get the second page I want the top 100 starting with number 101, SQL Server doesn''t have syntax for that, which is why the adapter does this: Select * FROM ( SELECT TOP 100 FROM ( SELECT TOP (offset + 1)*100 FROM TIM.dbo.contacts ORDER BY id ASC) ORDER BY id DESC ) ORDER BY id ASC If you don''t pass in an order parameter, then the sql code doesn''t work right because the adapter doesn''t default to one. David > -----Original Message----- > From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org [mailto:rails- > bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Ron DiFrango > Sent: Tuesday, September 27, 2005 4:17 PM > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > Subject: RE: [Rails] MS Sql Server and pagination > > David, > > I am not sure what you are talking about, but you can select the top X > number like the folowing: > > SELECT TOP 100 * FROM [TIM].[dbo].[contact]; > > Ron > > -----Original Message----- > From: David Chilton [mailto:david_chilton-Yt2XQmYAGVYAvxtiuMwx3w@public.gmane.org] > Sent: Tue 9/27/2005 4:02 PM > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > Cc: > Subject: RE: [Rails] MS Sql Server and pagination > > > > Give the pagination an ordering parameter. > > MS Sql doesn''t support limit/top type command like most SQL''s do, so > the > adapter generates a really complex query that gets everything upto > and > including your page, then flips it and gets you page off the top, > and > then flips it again to get you page in the right order. This means > an > order has to be specified, otherwise it can''t flip (It really should > default to something, maybe I''ll patch that) > > def list > @task_pages, @tasks = paginate :task, :per_page => 10, > :order_by=>"id ASC" > End > > > David > > > -----Original Message----- > > From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org [mailto:rails- > > bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Ron DiFrango > > Sent: Tuesday, September 27, 2005 2:19 PM > > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > Subject: [Rails] MS Sql Server and pagination > > > > All, > > > > I have the following controller/model: > > > > class Task < ActiveRecord::Base > > set_table_name "tasks" > > set_primary_key "task_id" > > end > > > > class TaskController < ApplicationController > > def index > > list > > render :action => ''list'' > > end > > def task > > end > > def list > > @task_pages, @tasks = paginate :task, :per_page => 10 > > end > > def show > > @task = Task.find(params[:id]) > > end > > def new > > @task = Task.new > > end > > def create > > @task = Task.new(params[:task]) > > if @task.save > > flash[:notice] = ''Task was successfully created.'' > > redirect_to :action => ''list'' > > else > > render :action => ''new'' > > end > > end > > def edit > > @task = Task.find(params[:id]) > > end > > def update > > @task = Task.find(params[:id]) > > if @task.update_attributes(params[:task]) > > flash[:notice] = ''Task was successfully updated.'' > > redirect_to :action => ''show'', :id => @task > > else > > render :action => ''edit'' > > end > > end > > def destroy > > Task.find(params[:id]).destroy > > redirect_to :action => ''list'' > > end > > end > > > > The initial page displays fine, but when I click the "Next Page" > link, > > nothing changes on the screen. I am using the scaffold generate > for > the > > code. > > > > Ron > > =================================================================== > ==========CONFIDENTIALITY NOTICE: The information in this electronic > message (including any attachments) is confidential and may be privileged > or proprietary. If you are not the intended recipient, any dissemination, > disclosure, copying, downloading, or other use of the information is > prohibited and unauthorized, and may be unlawful, regardless of address or > routing. If you are not the intended recipient, please inform the sender > immediately and permanently delete and destroy the original and any copies > of this message, including any attachments. > =================================================================== > ========= > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails > =============================================================================CONFIDENTIALITY NOTICE: The information in this electronic message (including any attachments) is confidential and may be privileged or proprietary. If you are not the intended recipient, any dissemination, disclosure, copying, downloading, or other use of the information is prohibited and unauthorized, and may be unlawful, regardless of address or routing. If you are not the intended recipient, please inform the sender immediately and permanently delete and destroy the original and any copies of this message, including any attachments. ============================================================================ _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails ==============================================================================CONFIDENTIALITY NOTICE: The information in this electronic message (including any attachments) is confidential and may be privileged or proprietary. If you are not the intended recipient, any dissemination, disclosure, copying, downloading, or other use of the information is prohibited and unauthorized, and may be unlawful, regardless of address or routing. If you are not the intended recipient, please inform the sender immediately and permanently delete and destroy the original and any copies of this message, including any attachments. ============================================================================= _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Sounds like a good plan. Any patches you have can yo usend them directly my way? -----Original Message----- From: David Chilton [mailto:david_chilton@mckee.com] Sent: Tue 9/27/2005 4:53 PM To: rails@lists.rubyonrails.org Cc: Subject: RE: [Rails] MS Sql Server and pagination I wouldn't want to do that because the results may not be sequential in the id field, or their (God Forbid) may not be one. I'm going to look at patching the section of the Sql server adapter to automatically order by the primary key if there is nothing specified for order by for just this type of query. David _____ From: rails-bounces@lists.rubyonrails.org [mailto:rails-bounces@lists.rubyonrails.org] On Behalf Of Ron DiFrango Sent: Tuesday, September 27, 2005 4:49 PM To: rails@lists.rubyonrails.org Subject: RE: [Rails] MS Sql Server and pagination Got it, it would be nice to have a workaround for SQL Server in this case. What about doing some sort of between on the ID field and just assume that the increment is 1 and it starts at one so you could do: SELECT * FROM [TIM].[dbo].[contact] where contact_id between 1 and 10; And increment it that way? -----Original Message----- From: David Chilton [mailto:david_chilton@mckee.com] Sent: Tue 9/27/2005 4:24 PM To: rails@lists.rubyonrails.org Cc: Subject: RE: [Rails] MS Sql Server and pagination Right, but you can't offset the top by 100 To get the second page I want the top 100 starting with number 101, SQL Server doesn't have syntax for that, which is why the adapter does this: Select * FROM ( SELECT TOP 100 FROM ( SELECT TOP (offset + 1)*100 FROM TIM.dbo.contacts ORDER BY id ASC) ORDER BY id DESC ) ORDER BY id ASC If you don't pass in an order parameter, then the sql code doesn't work right because the adapter doesn't default to one. David > -----Original Message----- > From: rails-bounces@lists.rubyonrails.org [mailto:rails- > bounces@lists.rubyonrails.org] On Behalf Of Ron DiFrango > Sent: Tuesday, September 27, 2005 4:17 PM > To: rails@lists.rubyonrails.org > Subject: RE: [Rails] MS Sql Server and pagination > > David, > > I am not sure what you are talking about, but you can select the top X > number like the folowing: > > SELECT TOP 100 * FROM [TIM].[dbo].[contact]; > > Ron > > -----Original Message----- > From: David Chilton [mailto:david_chilton@mckee.com] > Sent: Tue 9/27/2005 4:02 PM > To: rails@lists.rubyonrails.org > Cc: > Subject: RE: [Rails] MS Sql Server and pagination > > > > Give the pagination an ordering parameter. > > MS Sql doesn't support limit/top type command like most SQL's do, so > the > adapter generates a really complex query that gets everything upto > and > including your page, then flips it and gets you page off the top, > and > then flips it again to get you page in the right order. This means > an > order has to be specified, otherwise it can't flip (It really should > default to something, maybe I'll patch that) > > def list > @task_pages, @tasks = paginate :task, :per_page => 10, > :order_by=>"id ASC" > End > > > David > > > -----Original Message----- > > From: rails-bounces@lists.rubyonrails.org [mailto:rails- > > bounces@lists.rubyonrails.org] On Behalf Of Ron DiFrango > > Sent: Tuesday, September 27, 2005 2:19 PM > > To: rails@lists.rubyonrails.org > > Subject: [Rails] MS Sql Server and pagination > > > > All, > > > > I have the following controller/model: > > > > class Task < ActiveRecord::Base > > set_table_name "tasks" > > set_primary_key "task_id" > > end > > > > class TaskController < ApplicationController > > def index > > list > > render :action => 'list' > > end > > def task > > end > > def list > > @task_pages, @tasks = paginate :task, :per_page => 10 > > end > > def show > > @task = Task.find(params[:id]) > > end > > def new > > @task = Task.new > > end > > def create > > @task = Task.new(params[:task]) > > if @task.save > > flash[:notice] = 'Task was successfully created.' > > redirect_to :action => 'list' > > else > > render :action => 'new' > > end > > end > > def edit > > @task = Task.find(params[:id]) > > end > > def update > > @task = Task.find(params[:id]) > > if @task.update_attributes(params[:task]) > > flash[:notice] = 'Task was successfully updated.' > > redirect_to :action => 'show', :id => @task > > else > > render :action => 'edit' > > end > > end > > def destroy > > Task.find(params[:id]).destroy > > redirect_to :action => 'list' > > end > > end > > > > The initial page displays fine, but when I click the "Next Page" > link, > > nothing changes on the screen. I am using the scaffold generate > for > the > > code. > > > > Ron > > =================================================================== > ==========CONFIDENTIALITY NOTICE: The information in this electronic > message (including any attachments) is confidential and may be privileged > or proprietary. If you are not the intended recipient, any dissemination, > disclosure, copying, downloading, or other use of the information is > prohibited and unauthorized, and may be unlawful, regardless of address or > routing. If you are not the intended recipient, please inform the sender > immediately and permanently delete and destroy the original and any copies > of this message, including any attachments. > =================================================================== > ========= > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails > ==============================================================================CONFIDENTIALITY NOTICE: The information in this electronic message (including any attachments) is confidential and may be privileged or proprietary. If you are not the intended recipient, any dissemination, disclosure, copying, downloading, or other use of the information is prohibited and unauthorized, and may be unlawful, regardless of address or routing. If you are not the intended recipient, please inform the sender immediately and permanently delete and destroy the original and any copies of this message, including any attachments. ============================================================================= _______________________________________________ Rails mailing list Rails@lists.rubyonrails.org http://lists.rubyonrails.org/mailman/listinfo/rails ==============================================================================CONFIDENTIALITY NOTICE: The information in this electronic message (including any attachments) is confidential and may be privileged or proprietary. If you are not the intended recipient, any dissemination, disclosure, copying, downloading, or other use of the information is prohibited and unauthorized, and may be unlawful, regardless of address or routing. If you are not the intended recipient, please inform the sender immediately and permanently delete and destroy the original and any copies of this message, including any attachments. ============================================================================= _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Sure, it looks like someone tried to do It already in the svn rails, but its not working for me, I''m trying to fix it David ________________________________ From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Ron DiFrango Sent: Tuesday, September 27, 2005 5:13 PM To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org Subject: RE: [Rails] MS Sql Server and pagination Sounds like a good plan. Any patches you have can yo usend them directly my way? -----Original Message----- From: David Chilton [mailto:david_chilton-Yt2XQmYAGVYAvxtiuMwx3w@public.gmane.org] Sent: Tue 9/27/2005 4:53 PM To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org Cc: Subject: RE: [Rails] MS Sql Server and pagination I wouldn''t want to do that because the results may not be sequential in the id field, or their (God Forbid) may not be one. I''m going to look at patching the section of the Sql server adapter to automatically order by the primary key if there is nothing specified for order by for just this type of query. David ________________________________ From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Ron DiFrango Sent: Tuesday, September 27, 2005 4:49 PM To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org Subject: RE: [Rails] MS Sql Server and pagination Got it, it would be nice to have a workaround for SQL Server in this case. What about doing some sort of between on the ID field and just assume that the increment is 1 and it starts at one so you could do: SELECT * FROM [TIM].[dbo].[contact] where contact_id between 1 and 10; And increment it that way? -----Original Message----- From: David Chilton [mailto:david_chilton-Yt2XQmYAGVYAvxtiuMwx3w@public.gmane.org] Sent: Tue 9/27/2005 4:24 PM To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org Cc: Subject: RE: [Rails] MS Sql Server and pagination Right, but you can''t offset the top by 100 To get the second page I want the top 100 starting with number 101, SQL Server doesn''t have syntax for that, which is why the adapter does this: Select * FROM ( SELECT TOP 100 FROM ( SELECT TOP (offset + 1)*100 FROM TIM.dbo.contacts ORDER BY id ASC) ORDER BY id DESC ) ORDER BY id ASC If you don''t pass in an order parameter, then the sql code doesn''t work right because the adapter doesn''t default to one. David > -----Original Message----- > From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org [mailto:rails- > bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Ron DiFrango > Sent: Tuesday, September 27, 2005 4:17 PM > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > Subject: RE: [Rails] MS Sql Server and pagination > > David, > > I am not sure what you are talking about, but you can select the top X > number like the folowing: > > SELECT TOP 100 * FROM [TIM].[dbo].[contact]; > > Ron > > -----Original Message----- > From: David Chilton [mailto:david_chilton-Yt2XQmYAGVYAvxtiuMwx3w@public.gmane.org] > Sent: Tue 9/27/2005 4:02 PM > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > Cc: > Subject: RE: [Rails] MS Sql Server and pagination > > > > Give the pagination an ordering parameter. > > MS Sql doesn''t support limit/top type command like most SQL''s do, so > the > adapter generates a really complex query that gets everything upto > and > including your page, then flips it and gets you page off the top, > and > then flips it again to get you page in the right order. This means > an > order has to be specified, otherwise it can''t flip (It really should > default to something, maybe I''ll patch that) > > def list > @task_pages, @tasks = paginate :task, :per_page => 10, > :order_by=>"id ASC" > End > > > David > > > -----Original Message----- > > From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org [mailto:rails- > > bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Ron DiFrango > > Sent: Tuesday, September 27, 2005 2:19 PM > > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > Subject: [Rails] MS Sql Server and pagination > > > > All, > > > > I have the following controller/model: > > > > class Task < ActiveRecord::Base > > set_table_name "tasks" > > set_primary_key "task_id" > > end > > > > class TaskController < ApplicationController > > def index > > list > > render :action => ''list'' > > end > > def task > > end > > def list > > @task_pages, @tasks = paginate :task, :per_page => 10 > > end > > def show > > @task = Task.find(params[:id]) > > end > > def new > > @task = Task.new > > end > > def create > > @task = Task.new(params[:task]) > > if @task.save > > flash[:notice] = ''Task was successfully created.'' > > redirect_to :action => ''list'' > > else > > render :action => ''new'' > > end > > end > > def edit > > @task = Task.find(params[:id]) > > end > > def update > > @task = Task.find(params[:id]) > > if @task.update_attributes(params[:task]) > > flash[:notice] = ''Task was successfully updated.'' > > redirect_to :action => ''show'', :id => @task > > else > > render :action => ''edit'' > > end > > end > > def destroy > > Task.find(params[:id]).destroy > > redirect_to :action => ''list'' > > end > > end > > > > The initial page displays fine, but when I click the "Next Page" > link, > > nothing changes on the screen. I am using the scaffold generate > for > the > > code. > > > > Ron > > =================================================================== > ==========CONFIDENTIALITY NOTICE: The information in this electronic > message (including any attachments) is confidential and may be privileged > or proprietary. If you are not the intended recipient, any dissemination, > disclosure, copying, downloading, or other use of the information is > prohibited and unauthorized, and may be unlawful, regardless of address or > routing. If you are not the intended recipient, please inform the sender > immediately and permanently delete and destroy the original and any copies > of this message, including any attachments. > =================================================================== > ========= > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails > =============================================================================CONFIDENTIALITY NOTICE: The information in this electronic message (including any attachments) is confidential and may be privileged or proprietary. If you are not the intended recipient, any dissemination, disclosure, copying, downloading, or other use of the information is prohibited and unauthorized, and may be unlawful, regardless of address or routing. If you are not the intended recipient, please inform the sender immediately and permanently delete and destroy the original and any copies of this message, including any attachments. ============================================================================ _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails =============================================================================CONFIDENTIALITY NOTICE: The information in this electronic message (including any attachments) is confidential and may be privileged or proprietary. If you are not the intended recipient, any dissemination, disclosure, copying, downloading, or other use of the information is prohibited and unauthorized, and may be unlawful, regardless of address or routing. If you are not the intended recipient, please inform the sender immediately and permanently delete and destroy the original and any copies of this message, including any attachments. ============================================================================ ==============================================================================CONFIDENTIALITY NOTICE: The information in this electronic message (including any attachments) is confidential and may be privileged or proprietary. If you are not the intended recipient, any dissemination, disclosure, copying, downloading, or other use of the information is prohibited and unauthorized, and may be unlawful, regardless of address or routing. If you are not the intended recipient, please inform the sender immediately and permanently delete and destroy the original and any copies of this message, including any attachments. ============================================================================= _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
See the attached ruby file. It needs to replace activerecord/lib/active_record/connections_adapters/sqlserver_adapter.rb . Actually, only the method add_limit_offset! At line 302 really needs to be changed. I''m going to look into submitting an official patch. David ________________________________________ From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org [mailto:rails-bounces-1W37MKcQCpI5u/9nSYX9/w@public.gmane.orgnrails.org] On Behalf Of David Chilton Sent: Tuesday, September 27, 2005 5:17 PM To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org Subject: RE: [Rails] MS Sql Server and pagination Sure, it looks like someone tried to do It already in the svn rails, but its not working for me, I''m trying to fix it David ________________________________________ From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org [mailto:rails-bounces-1W37MKcQCpI5u/9nSYX9/w@public.gmane.orgnrails.org] On Behalf Of Ron DiFrango Sent: Tuesday, September 27, 2005 5:13 PM To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org Subject: RE: [Rails] MS Sql Server and pagination Sounds like a good plan. Any patches you have can yo usend them directly my way? -----Original Message----- From: David Chilton [mailto:david_chilton-Yt2XQmYAGVYAvxtiuMwx3w@public.gmane.org] Sent: Tue 9/27/2005 4:53 PM To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org Cc: Subject: RE: [Rails] MS Sql Server and pagination I wouldn''t want to do that because the results may not be sequential in the id field, or their (God Forbid) may not be one. I''m going to look at patching the section of the Sql server adapter to automatically order by the primary key if there is nothing specified for order by for just this type of query. David ________________________________________ From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org [mailto:rails-bounces-1W37MKcQCpI5u/9nSYX9/w@public.gmane.orgnrails.org] On Behalf Of Ron DiFrango Sent: Tuesday, September 27, 2005 4:49 PM To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org Subject: RE: [Rails] MS Sql Server and pagination Got it, it would be nice to have a workaround for SQL Server in this case. What about doing some sort of between on the ID field and just assume that the increment is 1 and it starts at one so you could do: SELECT * FROM [TIM].[dbo].[contact] where contact_id between 1 and 10; And increment it that way? -----Original Message----- From: David Chilton [mailto:david_chilton-Yt2XQmYAGVYAvxtiuMwx3w@public.gmane.org] Sent: Tue 9/27/2005 4:24 PM To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org Cc: Subject: RE: [Rails] MS Sql Server and pagination Right, but you can''t offset the top by 100 To get the second page I want the top 100 starting with number 101, SQL Server doesn''t have syntax for that, which is why the adapter does this: Select * FROM ( SELECT TOP 100 FROM ( SELECT TOP (offset + 1)*100 FROM TIM.dbo.contacts ORDER BY id ASC) ORDER BY id DESC ) ORDER BY id ASC If you don''t pass in an order parameter, then the sql code doesn''t work right because the adapter doesn''t default to one. David> -----Original Message----- > From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org [mailto:rails- > bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Ron DiFrango > Sent: Tuesday, September 27, 2005 4:17 PM > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > Subject: RE: [Rails] MS Sql Server and pagination > > David, > > I am not sure what you are talking about, but you can select the top X > number like the folowing: > > SELECT TOP 100 * FROM [TIM].[dbo].[contact]; > > Ron > > -----Original Message----- > From: David Chilton [mailto:david_chilton-Yt2XQmYAGVYAvxtiuMwx3w@public.gmane.org] > Sent: Tue 9/27/2005 4:02 PM > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > Cc: > Subject: RE: [Rails] MS Sql Server and pagination > > > > Give the pagination an ordering parameter. > > MS Sql doesn''t support limit/top type command like most SQL''sdo, so> the > adapter generates a really complex query that gets everythingupto> and > including your page, then flips it and gets you page off thetop,> and > then flips it again to get you page in the right order. Thismeans> an > order has to be specified, otherwise it can''t flip (It reallyshould> default to something, maybe I''ll patch that) > > def list > @task_pages, @tasks = paginate :task, :per_page => 10, > :order_by=>"id ASC" > End > > > David > > > -----Original Message----- > > From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org [mailto:rails- > > bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Ron DiFrango > > Sent: Tuesday, September 27, 2005 2:19 PM > > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > Subject: [Rails] MS Sql Server and pagination > > > > All, > > > > I have the following controller/model: > > > > class Task < ActiveRecord::Base > > set_table_name "tasks" > > set_primary_key "task_id" > > end > > > > class TaskController < ApplicationController > > def index > > list > > render :action => ''list'' > > end > > def task > > end > > def list > > @task_pages, @tasks = paginate :task, :per_page => 10 > > end > > def show > > @task = Task.find(params[:id]) > > end > > def new > > @task = Task.new > > end > > def create > > @task = Task.new(params[:task]) > > if @task.save > > flash[:notice] = ''Task was successfully created.'' > > redirect_to :action => ''list'' > > else > > render :action => ''new'' > > end > > end > > def edit > > @task = Task.find(params[:id]) > > end > > def update > > @task = Task.find(params[:id]) > > if @task.update_attributes(params[:task]) > > flash[:notice] = ''Task was successfully updated.'' > > redirect_to :action => ''show'', :id => @task > > else > > render :action => ''edit'' > > end > > end > > def destroy > > Task.find(params[:id]).destroy > > redirect_to :action => ''list'' > > end > > end > > > > The initial page displays fine, but when I click the "NextPage"> link, > > nothing changes on the screen. I am using the scaffoldgenerate> for > the > > code. > > > > Ron > >===================================================================> ==========CONFIDENTIALITY NOTICE: The information in this electronic> message (including any attachments) is confidential and may beprivileged> or proprietary. If you are not the intended recipient, anydissemination,> disclosure, copying, downloading, or other use of the information is > prohibited and unauthorized, and may be unlawful, regardless ofaddress or> routing. If you are not the intended recipient, please inform thesender> immediately and permanently delete and destroy the original and anycopies> of this message, including any attachments. >===================================================================> =========> _______________________________________________> Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >==============================================================================CONFIDENTIALITY NOTICE: The information in this electronic message (including any attachments) is confidential and may be privileged or proprietary. If you are not the intended recipient, any dissemination, disclosure, copying, downloading, or other use of the information is prohibited and unauthorized, and may be unlawful, regardless of address or routing. If you are not the intended recipient, please inform the sender immediately and permanently delete and destroy the original and any copies of this message, including any attachments. =============================================================================_______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails ==============================================================================CONFIDENTIALITY NOTICE: The information in this electronic message (including any attachments) is confidential and may be privileged or proprietary. If you are not the intended recipient, any dissemination, disclosure, copying, downloading, or other use of the information is prohibited and unauthorized, and may be unlawful, regardless of address or routing. If you are not the intended recipient, please inform the sender immediately and permanently delete and destroy the original and any copies of this message, including any attachments. ===========================================================================================================================================================CONFIDENTIALITY NOTICE: The information in this electronic message (including any attachments) is confidential and may be privileged or proprietary. If you are not the intended recipient, any dissemination, disclosure, copying, downloading, or other use of the information is prohibited and unauthorized, and may be unlawful, regardless of address or routing. If you are not the intended recipient, please inform the sender immediately and permanently delete and destroy the original and any copies of this message, including any attachments. ============================================================================= ==============================================================================CONFIDENTIALITY NOTICE: The information in this electronic message (including any attachments) is confidential and may be privileged or proprietary. If you are not the intended recipient, any dissemination, disclosure, copying, downloading, or other use of the information is prohibited and unauthorized, and may be unlawful, regardless of address or routing. If you are not the intended recipient, please inform the sender immediately and permanently delete and destroy the original and any copies of this message, including any attachments. ============================================================================= _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Actually, use this function instead, the other way has a bug, it fails if you do specify an order, this fixes that. David> -----Original Message----- > From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org [mailto:rails- > bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of David Chilton > Sent: Tuesday, September 27, 2005 5:39 PM > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > Subject: RE: [Rails] MS Sql Server and pagination > > See the attached ruby file. It needs to replace > activerecord/lib/active_record/connections_adapters/sqlserver_adapter.rb . > > Actually, only the method add_limit_offset! At line 302 really needs to be > changed. > > I''m going to look into submitting an official patch. > > David > ________________________________________ > From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org [mailto:rails- > bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of David Chilton > Sent: Tuesday, September 27, 2005 5:17 PM > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > Subject: RE: [Rails] MS Sql Server and pagination > > Sure, it looks like someone tried to do It already in the svn rails, but > its not working for me, I''m trying to fix it > > David > ________________________________________ > From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org [mailto:rails- > bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Ron DiFrango > Sent: Tuesday, September 27, 2005 5:13 PM > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > Subject: RE: [Rails] MS Sql Server and pagination > > Sounds like a good plan. Any patches you have can yo usend them directly > my way? > -----Original Message----- > From: David Chilton [mailto:david_chilton-Yt2XQmYAGVYAvxtiuMwx3w@public.gmane.org] > Sent: Tue 9/27/2005 4:53 PM > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > Cc: > Subject: RE: [Rails] MS Sql Server and pagination > I wouldn''t want to do that because the results may not be sequential in > the id field, or their (God Forbid) may not be one. > > I''m going to look at patching the section of the Sql server adapter to > automatically order by the primary key if there is nothing specified for > order by for just this type of query. > > David > ________________________________________ > From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org [mailto:rails- > bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Ron DiFrango > Sent: Tuesday, September 27, 2005 4:49 PM > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > Subject: RE: [Rails] MS Sql Server and pagination > > Got it, it would be nice to have a workaround for SQL Server in this > case. What about doing some sort of between on the ID field and just > assume that the increment is 1 and it starts at one so you could do: > > SELECT * FROM [TIM].[dbo].[contact] where contact_id between 1 and 10; > And increment it that way? > -----Original Message----- > From: David Chilton [mailto:david_chilton-Yt2XQmYAGVYAvxtiuMwx3w@public.gmane.org] > Sent: Tue 9/27/2005 4:24 PM > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > Cc: > Subject: RE: [Rails] MS Sql Server and pagination > Right, but you can''t offset the top by 100 > > To get the second page I want the top 100 starting with number 101, SQL > Server doesn''t have syntax for that, which is why the adapter does this: > > Select * FROM ( SELECT TOP 100 FROM ( SELECT TOP (offset + 1)*100 FROM > TIM.dbo.contacts ORDER BY id ASC) ORDER BY id DESC ) ORDER BY id ASC > > If you don''t pass in an order parameter, then the sql code doesn''t work > right because the adapter doesn''t default to one. > > David > > > > -----Original Message----- > > From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org [mailto:rails- > > bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Ron DiFrango > > Sent: Tuesday, September 27, 2005 4:17 PM > > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > Subject: RE: [Rails] MS Sql Server and pagination > > > > David, > > > > I am not sure what you are talking about, but you can select the top X > > number like the folowing: > > > > SELECT TOP 100 * FROM [TIM].[dbo].[contact]; > > > > Ron > > > > -----Original Message----- > > From: David Chilton [mailto:david_chilton-Yt2XQmYAGVYAvxtiuMwx3w@public.gmane.org] > > Sent: Tue 9/27/2005 4:02 PM > > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > Cc: > > Subject: RE: [Rails] MS Sql Server and pagination > > > > > > > > Give the pagination an ordering parameter. > > > > MS Sql doesn''t support limit/top type command like most SQL''s > do, so > > the > > adapter generates a really complex query that gets everything > upto > > and > > including your page, then flips it and gets you page off the > top, > > and > > then flips it again to get you page in the right order. This > means > > an > > order has to be specified, otherwise it can''t flip (It really > should > > default to something, maybe I''ll patch that) > > > > def list > > @task_pages, @tasks = paginate :task, :per_page => 10, > > :order_by=>"id ASC" > > End > > > > > > David > > > > > -----Original Message----- > > > From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org [mailto:rails- > > > bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Ron DiFrango > > > Sent: Tuesday, September 27, 2005 2:19 PM > > > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > > Subject: [Rails] MS Sql Server and pagination > > > > > > All, > > > > > > I have the following controller/model: > > > > > > class Task < ActiveRecord::Base > > > set_table_name "tasks" > > > set_primary_key "task_id" > > > end > > > > > > class TaskController < ApplicationController > > > def index > > > list > > > render :action => ''list'' > > > end > > > def task > > > end > > > def list > > > @task_pages, @tasks = paginate :task, :per_page => 10 > > > end > > > def show > > > @task = Task.find(params[:id]) > > > end > > > def new > > > @task = Task.new > > > end > > > def create > > > @task = Task.new(params[:task]) > > > if @task.save > > > flash[:notice] = ''Task was successfully created.'' > > > redirect_to :action => ''list'' > > > else > > > render :action => ''new'' > > > end > > > end > > > def edit > > > @task = Task.find(params[:id]) > > > end > > > def update > > > @task = Task.find(params[:id]) > > > if @task.update_attributes(params[:task]) > > > flash[:notice] = ''Task was successfully updated.'' > > > redirect_to :action => ''show'', :id => @task > > > else > > > render :action => ''edit'' > > > end > > > end > > > def destroy > > > Task.find(params[:id]).destroy > > > redirect_to :action => ''list'' > > > end > > > end > > > > > > The initial page displays fine, but when I click the "Next > Page" > > link, > > > nothing changes on the screen. I am using the scaffold > generate > > for > > the > > > code. > > > > > > Ron > > > > > ===================================================================> > ==========CONFIDENTIALITY NOTICE: The information in this electronic > > message (including any attachments) is confidential and may be > privileged > > or proprietary. If you are not the intended recipient, any > dissemination, > > disclosure, copying, downloading, or other use of the information is > > prohibited and unauthorized, and may be unlawful, regardless of > address or > > routing. If you are not the intended recipient, please inform the > sender > > immediately and permanently delete and destroy the original and any > copies > > of this message, including any attachments. > > > ===================================================================> > =========> > _______________________________________________ > > Rails mailing list > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > =========================================================================> ====CONFIDENTIALITY NOTICE: The information in this electronic message > (including any attachments) is confidential and may be privileged or > proprietary. If you are not the intended recipient, any dissemination, > disclosure, copying, downloading, or other use of the information is > prohibited and unauthorized, and may be unlawful, regardless of address or > routing. If you are not the intended recipient, please inform the sender > immediately and permanently delete and destroy the original and any copies > of this message, including any attachments. > =========================================================================> ===> _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails > =========================================================================> ====CONFIDENTIALITY NOTICE: The information in this electronic message > (including any attachments) is confidential and may be privileged or > proprietary. If you are not the intended recipient, any dissemination, > disclosure, copying, downloading, or other use of the information is > prohibited and unauthorized, and may be unlawful, regardless of address or > routing. If you are not the intended recipient, please inform the sender > immediately and permanently delete and destroy the original and any copies > of this message, including any attachments. > =========================================================================> ===> =========================================================================> ====CONFIDENTIALITY NOTICE: The information in this electronic message > (including any attachments) is confidential and may be privileged or > proprietary. If you are not the intended recipient, any dissemination, > disclosure, copying, downloading, or other use of the information is > prohibited and unauthorized, and may be unlawful, regardless of address or > routing. If you are not the intended recipient, please inform the sender > immediately and permanently delete and destroy the original and any copies > of this message, including any attachments. > =========================================================================> ===> > =========================================================================> ====CONFIDENTIALITY NOTICE: The information in this electronic message > (including any attachments) is confidential and may be privileged or > proprietary. If you are not the intended recipient, any dissemination, > disclosure, copying, downloading, or other use of the information is > prohibited and unauthorized, and may be unlawful, regardless of address or > routing. If you are not the intended recipient, please inform the sender > immediately and permanently delete and destroy the original and any copies > of this message, including any attachments. > =========================================================================> =================================================================================CONFIDENTIALITY NOTICE: The information in this electronic message (including any attachments) is confidential and may be privileged or proprietary. If you are not the intended recipient, any dissemination, disclosure, copying, downloading, or other use of the information is prohibited and unauthorized, and may be unlawful, regardless of address or routing. If you are not the intended recipient, please inform the sender immediately and permanently delete and destroy the original and any copies of this message, including any attachments. ============================================================================= _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
This worked for me David! Thanks! -----Original Message----- From: David Chilton [mailto:david_chilton@mckee.com] Sent: Tue 9/27/2005 7:07 PM To: rails@lists.rubyonrails.org Cc: Subject: RE: [Rails] MS Sql Server and pagination Actually, use this function instead, the other way has a bug, it fails if you do specify an order, this fixes that. David > -----Original Message----- > From: rails-bounces@lists.rubyonrails.org [mailto:rails- > bounces@lists.rubyonrails.org] On Behalf Of David Chilton > Sent: Tuesday, September 27, 2005 5:39 PM > To: rails@lists.rubyonrails.org > Subject: RE: [Rails] MS Sql Server and pagination > > See the attached ruby file. It needs to replace > activerecord/lib/active_record/connections_adapters/sqlserver_adapter.rb . > > Actually, only the method add_limit_offset! At line 302 really needs to be > changed. > > I'm going to look into submitting an official patch. > > David > ________________________________________ > From: rails-bounces@lists.rubyonrails.org [mailto:rails- > bounces@lists.rubyonrails.org] On Behalf Of David Chilton > Sent: Tuesday, September 27, 2005 5:17 PM > To: rails@lists.rubyonrails.org > Subject: RE: [Rails] MS Sql Server and pagination > > Sure, it looks like someone tried to do It already in the svn rails, but > its not working for me, I'm trying to fix it > > David > ________________________________________ > From: rails-bounces@lists.rubyonrails.org [mailto:rails- > bounces@lists.rubyonrails.org] On Behalf Of Ron DiFrango > Sent: Tuesday, September 27, 2005 5:13 PM > To: rails@lists.rubyonrails.org > Subject: RE: [Rails] MS Sql Server and pagination > > Sounds like a good plan. Any patches you have can yo usend them directly > my way? > -----Original Message----- > From: David Chilton [mailto:david_chilton@mckee.com] > Sent: Tue 9/27/2005 4:53 PM > To: rails@lists.rubyonrails.org > Cc: > Subject: RE: [Rails] MS Sql Server and pagination > I wouldn't want to do that because the results may not be sequential in > the id field, or their (God Forbid) may not be one. > > I'm going to look at patching the section of the Sql server adapter to > automatically order by the primary key if there is nothing specified for > order by for just this type of query. > > David > ________________________________________ > From: rails-bounces@lists.rubyonrails.org [mailto:rails- > bounces@lists.rubyonrails.org] On Behalf Of Ron DiFrango > Sent: Tuesday, September 27, 2005 4:49 PM > To: rails@lists.rubyonrails.org > Subject: RE: [Rails] MS Sql Server and pagination > > Got it, it would be nice to have a workaround for SQL Server in this > case. What about doing some sort of between on the ID field and just > assume that the increment is 1 and it starts at one so you could do: > > SELECT * FROM [TIM].[dbo].[contact] where contact_id between 1 and 10; > And increment it that way? > -----Original Message----- > From: David Chilton [mailto:david_chilton@mckee.com] > Sent: Tue 9/27/2005 4:24 PM > To: rails@lists.rubyonrails.org > Cc: > Subject: RE: [Rails] MS Sql Server and pagination > Right, but you can't offset the top by 100 > > To get the second page I want the top 100 starting with number 101, SQL > Server doesn't have syntax for that, which is why the adapter does this: > > Select * FROM ( SELECT TOP 100 FROM ( SELECT TOP (offset + 1)*100 FROM > TIM.dbo.contacts ORDER BY id ASC) ORDER BY id DESC ) ORDER BY id ASC > > If you don't pass in an order parameter, then the sql code doesn't work > right because the adapter doesn't default to one. > > David > > > > -----Original Message----- > > From: rails-bounces@lists.rubyonrails.org [mailto:rails- > > bounces@lists.rubyonrails.org] On Behalf Of Ron DiFrango > > Sent: Tuesday, September 27, 2005 4:17 PM > > To: rails@lists.rubyonrails.org > > Subject: RE: [Rails] MS Sql Server and pagination > > > > David, > > > > I am not sure what you are talking about, but you can select the top X > > number like the folowing: > > > > SELECT TOP 100 * FROM [TIM].[dbo].[contact]; > > > > Ron > > > > -----Original Message----- > > From: David Chilton [mailto:david_chilton@mckee.com] > > Sent: Tue 9/27/2005 4:02 PM > > To: rails@lists.rubyonrails.org > > Cc: > > Subject: RE: [Rails] MS Sql Server and pagination > > > > > > > > Give the pagination an ordering parameter. > > > > MS Sql doesn't support limit/top type command like most SQL's > do, so > > the > > adapter generates a really complex query that gets everything > upto > > and > > including your page, then flips it and gets you page off the > top, > > and > > then flips it again to get you page in the right order. This > means > > an > > order has to be specified, otherwise it can't flip (It really > should > > default to something, maybe I'll patch that) > > > > def list > > @task_pages, @tasks = paginate :task, :per_page => 10, > > :order_by=>"id ASC" > > End > > > > > > David > > > > > -----Original Message----- > > > From: rails-bounces@lists.rubyonrails.org [mailto:rails- > > > bounces@lists.rubyonrails.org] On Behalf Of Ron DiFrango > > > Sent: Tuesday, September 27, 2005 2:19 PM > > > To: rails@lists.rubyonrails.org > > > Subject: [Rails] MS Sql Server and pagination > > > > > > All, > > > > > > I have the following controller/model: > > > > > > class Task < ActiveRecord::Base > > > set_table_name "tasks" > > > set_primary_key "task_id" > > > end > > > > > > class TaskController < ApplicationController > > > def index > > > list > > > render :action => 'list' > > > end > > > def task > > > end > > > def list > > > @task_pages, @tasks = paginate :task, :per_page => 10 > > > end > > > def show > > > @task = Task.find(params[:id]) > > > end > > > def new > > > @task = Task.new > > > end > > > def create > > > @task = Task.new(params[:task]) > > > if @task.save > > > flash[:notice] = 'Task was successfully created.' > > > redirect_to :action => 'list' > > > else > > > render :action => 'new' > > > end > > > end > > > def edit > > > @task = Task.find(params[:id]) > > > end > > > def update > > > @task = Task.find(params[:id]) > > > if @task.update_attributes(params[:task]) > > > flash[:notice] = 'Task was successfully updated.' > > > redirect_to :action => 'show', :id => @task > > > else > > > render :action => 'edit' > > > end > > > end > > > def destroy > > > Task.find(params[:id]).destroy > > > redirect_to :action => 'list' > > > end > > > end > > > > > > The initial page displays fine, but when I click the "Next > Page" > > link, > > > nothing changes on the screen. I am using the scaffold > generate > > for > > the > > > code. > > > > > > Ron > > > > _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
David, Just a note this does not work if the primary key column is NOT an identity column. Or like in my case it is character based data, that I prefer not change. Any thoughts? Ron -----Original Message----- From: Ron DiFrango on behalf of Ron DiFrango Sent: Tue 9/27/2005 8:58 PM To: rails@lists.rubyonrails.org Cc: Subject: RE: [Rails] MS Sql Server and pagination This worked for me David! Thanks! -----Original Message----- From: David Chilton [mailto:david_chilton@mckee.com] Sent: Tue 9/27/2005 7:07 PM To: rails@lists.rubyonrails.org Cc: Subject: RE: [Rails] MS Sql Server and pagination Actually, use this function instead, the other way has a bug, it fails if you do specify an order, this fixes that. David > -----Original Message----- > From: rails-bounces@lists.rubyonrails.org [mailto:rails- > bounces@lists.rubyonrails.org] On Behalf Of David Chilton > Sent: Tuesday, September 27, 2005 5:39 PM > To: rails@lists.rubyonrails.org > Subject: RE: [Rails] MS Sql Server and pagination > > See the attached ruby file. It needs to replace > activerecord/lib/active_record/connections_adapters/sqlserver_adapter.rb . > > Actually, only the method add_limit_offset! At line 302 really needs to be > changed. > > I'm going to look into submitting an official patch. > > David > ________________________________________ > From: rails-bounces@lists.rubyonrails.org [mailto:rails- > bounces@lists.rubyonrails.org] On Behalf Of David Chilton > Sent: Tuesday, September 27, 2005 5:17 PM > To: rails@lists.rubyonrails.org > Subject: RE: [Rails] MS Sql Server and pagination > > Sure, it looks like someone tried to do It already in the svn rails, but > its not working for me, I'm trying to fix it > > David > ________________________________________ > From: rails-bounces@lists.rubyonrails.org [mailto:rails- > bounces@lists.rubyonrails.org] On Behalf Of Ron DiFrango > Sent: Tuesday, September 27, 2005 5:13 PM > To: rails@lists.rubyonrails.org > Subject: RE: [Rails] MS Sql Server and pagination > > Sounds like a good plan. Any patches you have can yo usend them directly > my way? > -----Original Message----- > From: David Chilton [mailto:david_chilton@mckee.com] > Sent: Tue 9/27/2005 4:53 PM > To: rails@lists.rubyonrails.org > Cc: > Subject: RE: [Rails] MS Sql Server and pagination > I wouldn't want to do that because the results may not be sequential in > the id field, or their (God Forbid) may not be one. > > I'm going to look at patching the section of the Sql server adapter to > automatically order by the primary key if there is nothing specified for > order by for just this type of query. > > David > ________________________________________ > From: rails-bounces@lists.rubyonrails.org [mailto:rails- > bounces@lists.rubyonrails.org] On Behalf Of Ron DiFrango > Sent: Tuesday, September 27, 2005 4:49 PM > To: rails@lists.rubyonrails.org > Subject: RE: [Rails] MS Sql Server and pagination > > Got it, it would be nice to have a workaround for SQL Server in this > case. What about doing some sort of between on the ID field and just > assume that the increment is 1 and it starts at one so you could do: > > SELECT * FROM [TIM].[dbo].[contact] where contact_id between 1 and 10; > And increment it that way? > -----Original Message----- > From: David Chilton [mailto:david_chilton@mckee.com] > Sent: Tue 9/27/2005 4:24 PM > To: rails@lists.rubyonrails.org > Cc: > Subject: RE: [Rails] MS Sql Server and pagination > Right, but you can't offset the top by 100 > > To get the second page I want the top 100 starting with number 101, SQL > Server doesn't have syntax for that, which is why the adapter does this: > > Select * FROM ( SELECT TOP 100 FROM ( SELECT TOP (offset + 1)*100 FROM > TIM.dbo.contacts ORDER BY id ASC) ORDER BY id DESC ) ORDER BY id ASC > > If you don't pass in an order parameter, then the sql code doesn't work > right because the adapter doesn't default to one. > > David > > > > -----Original Message----- > > From: rails-bounces@lists.rubyonrails.org [mailto:rails- > > bounces@lists.rubyonrails.org] On Behalf Of Ron DiFrango > > Sent: Tuesday, September 27, 2005 4:17 PM > > To: rails@lists.rubyonrails.org > > Subject: RE: [Rails] MS Sql Server and pagination > > > > David, > > > > I am not sure what you are talking about, but you can select the top X > > number like the folowing: > > > > SELECT TOP 100 * FROM [TIM].[dbo].[contact]; > > > > Ron > > > > -----Original Message----- > > From: David Chilton [mailto:david_chilton@mckee.com] > > Sent: Tue 9/27/2005 4:02 PM > > To: rails@lists.rubyonrails.org > > Cc: > > Subject: RE: [Rails] MS Sql Server and pagination > > > > > > > > Give the pagination an ordering parameter. > > > > MS Sql doesn't support limit/top type command like most SQL's > do, so > > the > > adapter generates a really complex query that gets everything > upto > > and > > including your page, then flips it and gets you page off the > top, > > and > > then flips it again to get you page in the right order. This > means > > an > > order has to be specified, otherwise it can't flip (It really > should > > default to something, maybe I'll patch that) > > > > def list > > @task_pages, @tasks = paginate :task, :per_page => 10, > > :order_by=>"id ASC" > > End > > > > > > David > > > > > -----Original Message----- > > > From: rails-bounces@lists.rubyonrails.org [mailto:rails- > > > bounces@lists.rubyonrails.org] On Behalf Of Ron DiFrango > > > Sent: Tuesday, September 27, 2005 2:19 PM > > > To: rails@lists.rubyonrails.org > > > Subject: [Rails] MS Sql Server and pagination > > > > > > All, > > > > > > I have the following controller/model: > > > > > > class Task < ActiveRecord::Base > > > set_table_name "tasks" > > > set_primary_key "task_id" > > > end > > > > > > class TaskController < ApplicationController > > > def index > > > list > > > render :action => 'list' > > > end > > > def task > > > end > > > def list > > > @task_pages, @tasks = paginate :task, :per_page => 10 > > > end > > > def show > > > @task = Task.find(params[:id]) > > > end > > > def new > > > @task = Task.new > > > end > > > def create > > > @task = Task.new(params[:task]) > > > if @task.save > > > flash[:notice] = 'Task was successfully created.' > > > redirect_to :action => 'list' > > > else > > > render :action => 'new' > > > end > > > end > > > def edit > > > @task = Task.find(params[:id]) > > > end > > > def update > > > @task = Task.find(params[:id]) > > > if @task.update_attributes(params[:task]) > > > flash[:notice] = 'Task was successfully updated.' > > > redirect_to :action => 'show', :id => @task > > > else > > > render :action => 'edit' > > > end > > > end > > > def destroy > > > Task.find(params[:id]).destroy > > > redirect_to :action => 'list' > > > end > > > end > > > > > > The initial page displays fine, but when I click the "Next > Page" > > link, > > > nothing changes on the screen. I am using the scaffold > generate > > for > > the > > > code. > > > > > > Ron > > > > _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
For that case I would suggest making sure that you specify an order column in the query call, it needs to be able to sort something to get the page results. David ________________________________ From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Ron DiFrango Sent: Friday, September 30, 2005 1:42 PM To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org Subject: RE: [Rails] MS Sql Server and pagination David, Just a note this does not work if the primary key column is NOT an identity column. Or like in my case it is character based data, that I prefer not change. Any thoughts? Ron -----Original Message----- From: Ron DiFrango on behalf of Ron DiFrango Sent: Tue 9/27/2005 8:58 PM To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org Cc: Subject: RE: [Rails] MS Sql Server and pagination This worked for me David! Thanks! -----Original Message----- From: David Chilton [mailto:david_chilton-Yt2XQmYAGVYAvxtiuMwx3w@public.gmane.org] Sent: Tue 9/27/2005 7:07 PM To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org Cc: Subject: RE: [Rails] MS Sql Server and pagination Actually, use this function instead, the other way has a bug, it fails if you do specify an order, this fixes that. David > -----Original Message----- > From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org [mailto:rails- > bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of David Chilton > Sent: Tuesday, September 27, 2005 5:39 PM > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > Subject: RE: [Rails] MS Sql Server and pagination > > See the attached ruby file. It needs to replace > activerecord/lib/active_record/connections_adapters/sqlserver_adapter.rb . > > Actually, only the method add_limit_offset! At line 302 really needs to be > changed. > > I''m going to look into submitting an official patch. > > David > ________________________________________ > From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org [mailto:rails- > bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of David Chilton > Sent: Tuesday, September 27, 2005 5:17 PM > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > Subject: RE: [Rails] MS Sql Server and pagination > > Sure, it looks like someone tried to do It already in the svn rails, but > its not working for me, I''m trying to fix it > > David > ________________________________________ > From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org [mailto:rails- > bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Ron DiFrango > Sent: Tuesday, September 27, 2005 5:13 PM > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > Subject: RE: [Rails] MS Sql Server and pagination > > Sounds like a good plan. Any patches you have can yo usend them directly > my way? > -----Original Message----- > From: David Chilton [mailto:david_chilton-Yt2XQmYAGVYAvxtiuMwx3w@public.gmane.org] > Sent: Tue 9/27/2005 4:53 PM > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > Cc: > Subject: RE: [Rails] MS Sql Server and pagination > I wouldn''t want to do that because the results may not be sequential in > the id field, or their (God Forbid) may not be one. > > I''m going to look at patching the section of the Sql server adapter to > automatically order by the primary key if there is nothing specified for > order by for just this type of query. > > David > ________________________________________ > From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org [mailto:rails- > bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Ron DiFrango > Sent: Tuesday, September 27, 2005 4:49 PM > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > Subject: RE: [Rails] MS Sql Server and pagination > > Got it, it would be nice to have a workaround for SQL Server in this > case. What about doing some sort of between on the ID field and just > assume that the increment is 1 and it starts at one so you could do: > > SELECT * FROM [TIM].[dbo].[contact] where contact_id between 1 and 10; > And increment it that way? > -----Original Message----- > From: David Chilton [mailto:david_chilton-Yt2XQmYAGVYAvxtiuMwx3w@public.gmane.org] > Sent: Tue 9/27/2005 4:24 PM > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > Cc: > Subject: RE: [Rails] MS Sql Server and pagination > Right, but you can''t offset the top by 100 > > To get the second page I want the top 100 starting with number 101, SQL > Server doesn''t have syntax for that, which is why the adapter does this: > > Select * FROM ( SELECT TOP 100 FROM ( SELECT TOP (offset + 1)*100 FROM > TIM.dbo.contacts ORDER BY id ASC) ORDER BY id DESC ) ORDER BY id ASC > > If you don''t pass in an order parameter, then the sql code doesn''t work > right because the adapter doesn''t default to one. > > David > > > > -----Original Message----- > > From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org [mailto:rails- > > bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Ron DiFrango > > Sent: Tuesday, September 27, 2005 4:17 PM > > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > Subject: RE: [Rails] MS Sql Server and pagination > > > > David, > > > > I am not sure what you are talking about, but you can select the top X > > number like the folowing: > > > > SELECT TOP 100 * FROM [TIM].[dbo].[contact]; > > > > Ron > > > > -----Original Message----- > > From: David Chilton [mailto:david_chilton-Yt2XQmYAGVYAvxtiuMwx3w@public.gmane.org] > > Sent: Tue 9/27/2005 4:02 PM > > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > Cc: > > Subject: RE: [Rails] MS Sql Server and pagination > > > > > > > > Give the pagination an ordering parameter. > > > > MS Sql doesn''t support limit/top type command like most SQL''s > do, so > > the > > adapter generates a really complex query that gets everything > upto > > and > > including your page, then flips it and gets you page off the > top, > > and > > then flips it again to get you page in the right order. This > means > > an > > order has to be specified, otherwise it can''t flip (It really > should > > default to something, maybe I''ll patch that) > > > > def list > > @task_pages, @tasks = paginate :task, :per_page => 10, > > :order_by=>"id ASC" > > End > > > > > > David > > > > > -----Original Message----- > > > From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org [mailto:rails- > > > bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Ron DiFrango > > > Sent: Tuesday, September 27, 2005 2:19 PM > > > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > > Subject: [Rails] MS Sql Server and pagination > > > > > > All, > > > > > > I have the following controller/model: > > > > > > class Task < ActiveRecord::Base > > > set_table_name "tasks" > > > set_primary_key "task_id" > > > end > > > > > > class TaskController < ApplicationController > > > def index > > > list > > > render :action => ''list'' > > > end > > > def task > > > end > > > def list > > > @task_pages, @tasks = paginate :task, :per_page => 10 > > > end > > > def show > > > @task = Task.find(params[:id]) > > > end > > > def new > > > @task = Task.new > > > end > > > def create > > > @task = Task.new(params[:task]) > > > if @task.save > > > flash[:notice] = ''Task was successfully created.'' > > > redirect_to :action => ''list'' > > > else > > > render :action => ''new'' > > > end > > > end > > > def edit > > > @task = Task.find(params[:id]) > > > end > > > def update > > > @task = Task.find(params[:id]) > > > if @task.update_attributes(params[:task]) > > > flash[:notice] = ''Task was successfully updated.'' > > > redirect_to :action => ''show'', :id => @task > > > else > > > render :action => ''edit'' > > > end > > > end > > > def destroy > > > Task.find(params[:id]).destroy > > > redirect_to :action => ''list'' > > > end > > > end > > > > > > The initial page displays fine, but when I click the "Next > Page" > > link, > > > nothing changes on the screen. I am using the scaffold > generate > > for > > the > > > code. > > > > > > Ron > > > > ==============================================================================CONFIDENTIALITY NOTICE: The information in this electronic message (including any attachments) is confidential and may be privileged or proprietary. If you are not the intended recipient, any dissemination, disclosure, copying, downloading, or other use of the information is prohibited and unauthorized, and may be unlawful, regardless of address or routing. If you are not the intended recipient, please inform the sender immediately and permanently delete and destroy the original and any copies of this message, including any attachments. ============================================================================= _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
In my working with the sqlserver adapter, I seem to have to specify an :order_by value in the model, even for an autoinc id field per convention. Not that big of a big deal. What if a PK field is populated by a trigger? In Oracle, it is possible to query a sequence (generator in Firebird/Interbase), and get the nextval (which guarantees that the next call to the sequence will have a different value, which is very good). It looks like you can do it in the trigger with SCOPE_IDENTITY (for sqlserver 2000+), but only for autoinc/identity fields. @@IDENTITY is the "old" way, but it''s not safe, as it gets the last value inserted overall, not necessarily the value you inserted.\ On 10/3/05, David Chilton <david_chilton-Yt2XQmYAGVYAvxtiuMwx3w@public.gmane.org> wrote:> > For that case I would suggest making sure that you specify an order > column in the query call, it needs to be able to sort something to get the > page results. > > David > ------------------------------ > > *From:* rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org [mailto: > rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] *On Behalf Of *Ron DiFrango > *Sent:* Friday, September 30, 2005 1:42 PM > *To:* rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > *Subject:* RE: [Rails] MS Sql Server and pagination > > David, > > Just a note this does not work if the primary key column is NOT an > identity column. Or like in my case it is character based data, that I > prefer not change. Any thoughts? > > Ron > > -----Original Message----- > *From:* Ron DiFrango on behalf of Ron DiFrango > *Sent:* Tue 9/27/2005 8:58 PM > *To:* rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > *Cc:* > *Subject:* RE: [Rails] MS Sql Server and pagination > > This worked for me David! > > Thanks! > > -----Original Message----- > *From:* David Chilton [mailto:david_chilton-Yt2XQmYAGVYAvxtiuMwx3w@public.gmane.org] > *Sent:* Tue 9/27/2005 7:07 PM > *To:* rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > *Cc:* > *Subject:* RE: [Rails] MS Sql Server and pagination > > Actually, use this function instead, the other way has a bug, it fails if > you do specify an order, this fixes that. > David > > > > -----Original Message----- > > From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org [mailto:rails- <rails-> > > bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of David Chilton > > Sent: Tuesday, September 27, 2005 5:39 PM > > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > Subject: RE: [Rails] MS Sql Server and pagination > > > > See the attached ruby file. It needs to replace > > activerecord/lib/active_record/connections_adapters/sqlserver_adapter.rb > . > > > > Actually, only the method add_limit_offset! At line 302 really needs to > be > > changed. > > > > I''m going to look into submitting an official patch. > > > > David > > ________________________________________ > > From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org [mailto:rails- <rails-> > > bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of David Chilton > > Sent: Tuesday, September 27, 2005 5:17 PM > > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > Subject: RE: [Rails] MS Sql Server and pagination > > > > Sure, it looks like someone tried to do It already in the svn rails, but > > its not working for me, I''m trying to fix it > > > > David > > ________________________________________ > > From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org [mailto:rails- <rails-> > > bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Ron DiFrango > > Sent: Tuesday, September 27, 2005 5:13 PM > > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > Subject: RE: [Rails] MS Sql Server and pagination > > > > Sounds like a good plan. Any patches you have can yo usend them directly > > my way? > > -----Original Message----- > > From: David Chilton [mailto:david_chilton-Yt2XQmYAGVYAvxtiuMwx3w@public.gmane.org<david_chilton@mckee.com> > ] > > Sent: Tue 9/27/2005 4:53 PM > > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > Cc: > > Subject: RE: [Rails] MS Sql Server and pagination > > I wouldn''t want to do that because the results may not be sequential in > > the id field, or their (God Forbid) may not be one. > > > > I''m going to look at patching the section of the Sql server adapter to > > automatically order by the primary key if there is nothing specified for > > order by for just this type of query. > > > > David > > ________________________________________ > > From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org [mailto:rails- <rails-> > > bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Ron DiFrango > > Sent: Tuesday, September 27, 2005 4:49 PM > > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > Subject: RE: [Rails] MS Sql Server and pagination > > > > Got it, it would be nice to have a workaround for SQL Server in this > > case. What about doing some sort of between on the ID field and just > > assume that the increment is 1 and it starts at one so you could do: > > > > SELECT * FROM [TIM].[dbo].[contact] where contact_id between 1 and 10; > > And increment it that way? > > -----Original Message----- > > From: David Chilton [mailto:david_chilton-Yt2XQmYAGVYAvxtiuMwx3w@public.gmane.org<david_chilton@mckee.com> > ] > > Sent: Tue 9/27/2005 4:24 PM > > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > Cc: > > Subject: RE: [Rails] MS Sql Server and pagination > > Right, but you can''t offset the top by 100 > > > > To get the second page I want the top 100 starting with number 101, SQL > > Server doesn''t have syntax for that, which is why the adapter does this: > > > > Select * FROM ( SELECT TOP 100 FROM ( SELECT TOP (offset + 1)*100 FROM > > TIM.dbo.contacts ORDER BY id ASC) ORDER BY id DESC ) ORDER BY id ASC > > > > If you don''t pass in an order parameter, then the sql code doesn''t work > > right because the adapter doesn''t default to one. > > > > David > > > > > > > -----Original Message----- > > > From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org [mailto:rails- <rails-> > > > bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Ron DiFrango > > > Sent: Tuesday, September 27, 2005 4:17 PM > > > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > > Subject: RE: [Rails] MS Sql Server and pagination > > > > > > David, > > > > > > I am not sure what you are talking about, but you can select the top X > > > number like the folowing: > > > > > > SELECT TOP 100 * FROM [TIM].[dbo].[contact]; > > > > > > Ron > > > > > > -----Original Message----- > > > From: David Chilton [mailto:david_chilton-Yt2XQmYAGVYAvxtiuMwx3w@public.gmane.org<david_chilton@mckee.com> > ] > > > Sent: Tue 9/27/2005 4:02 PM > > > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > > Cc: > > > Subject: RE: [Rails] MS Sql Server and pagination > > > > > > > > > > > > Give the pagination an ordering parameter. > > > > > > MS Sql doesn''t support limit/top type command like most SQL''s > > do, so > > > the > > > adapter generates a really complex query that gets everything > > upto > > > and > > > including your page, then flips it and gets you page off the > > top, > > > and > > > then flips it again to get you page in the right order. This > > means > > > an > > > order has to be specified, otherwise it can''t flip (It really > > should > > > default to something, maybe I''ll patch that) > > > > > > def list > > > @task_pages, @tasks = paginate :task, :per_page => 10, > > > :order_by=>"id ASC" > > > End > > > > > > > > > David > > > > > > > -----Original Message----- > > > > From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org [mailto:rails- <rails-> > > > > bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Ron DiFrango > > > > Sent: Tuesday, September 27, 2005 2:19 PM > > > > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > > > Subject: [Rails] MS Sql Server and pagination > > > > > > > > All, > > > > > > > > I have the following controller/model: > > > > > > > > class Task < ActiveRecord::Base > > > > set_table_name "tasks" > > > > set_primary_key "task_id" > > > > end > > > > > > > > class TaskController < ApplicationController > > > > def index > > > > list > > > > render :action => ''list'' > > > > end > > > > def task > > > > end > > > > def list > > > > @task_pages, @tasks = paginate :task, :per_page => 10 > > > > end > > > > def show > > > > @task = Task.find(params[:id]) > > > > end > > > > def new > > > > @task = Task.new > > > > end > > > > def create > > > > @task = Task.new(params[:task]) > > > > if @task.save > > > > flash[:notice] = ''Task was successfully created.'' > > > > redirect_to :action => ''list'' > > > > else > > > > render :action => ''new'' > > > > end > > > > end > > > > def edit > > > > @task = Task.find(params[:id]) > > > > end > > > > def update > > > > @task = Task.find(params[:id]) > > > > if @task.update_attributes(params[:task]) > > > > flash[:notice] = ''Task was successfully updated.'' > > > > redirect_to :action => ''show'', :id => @task > > > > else > > > > render :action => ''edit'' > > > > end > > > > end > > > > def destroy > > > > Task.find(params[:id]).destroy > > > > redirect_to :action => ''list'' > > > > end > > > > end > > > > > > > > The initial page displays fine, but when I click the "Next > > Page" > > > link, > > > > nothing changes on the screen. I am using the scaffold > > generate > > > for > > > the > > > > code. > > > > > > > > Ron > > > > > > > > ==============================================================================CONFIDENTIALITY > NOTICE: The information in this electronic message (including any > attachments) is confidential and may be privileged or proprietary. If you > are not the intended recipient, any dissemination, disclosure, copying, > downloading, or other use of the information is prohibited and unauthorized, > and may be unlawful, regardless of address or routing. If you are not the > intended recipient, please inform the sender immediately and permanently > delete and destroy the original and any copies of this message, including > any attachments. > > =============================================================================> > _______________________________________________ > 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