Finding position of previous selected record in new ordering

[Tobias Hilka <th...@vps.de>]

Hello everyone,
 
we are facing the following problem:
 
We have a grid with many entries (up to several 100 000 entries). For
performance reasons we only select the window which is visible for the user
(about 50 entries). The rest of the grid is initialized with null-Items. So
far so good.
 
But now our problem: If we do order the grid by firstname ascending and
select the fith entry, then we order the by the same column descending, we
want the selection to persist and therefore we want the selection window to
contain the selected entry in its new environment (e.g. position 99 950 to
100 000 and select record number 99 996).
 
Is there any possibility to express this in torque? We know about some
special features of some databases which can handle this. And I think there
are features in SQL 2003 for this.
 
 
Thank you for your help.
 
 
Tobias

RE: Finding position of previous selected record in new ordering

[Greg Monroe <Gr...@DukeCE.com>]

You can use Criteria.setOffset to define the start point and
Criteria.setLimit
to define the number of records to return.  This gets translated into
the 
appropriate DB server specific select.  But test and verify that it work

with your specific DB since there were issues with various DB prior to
3.3 (but I think they have been worked out now).
 
Also, you might look at the LargeSelect utility class.  This does the 
same thing but also has some performance/ease of use enhancements 
like caching serveral pages, being able to select by page, supplying 
page counts and the like.


________________________________

	From: Tobias Hilka [mailto:thilka@vps.de] 
	Sent: Friday, March 16, 2007 4:40 AM
	To: Apache Torque Developers List
	Subject: Finding position of previous selected record in new
ordering
	
	
	Hello everyone,
	 
	we are facing the following problem:
	 
	We have a grid with many entries (up to several 100 000
entries). For performance reasons we only select the window which is
visible for the user (about 50 entries). The rest of the grid is
initialized with null-Items. So far so good.
	 
	But now our problem: If we do order the grid by firstname
ascending and select the fith entry, then we order the by the same
column descending, we want the selection to persist and therefore we
want the selection window to contain the selected entry in its new
environment (e.g. position 99 950 to 100 000 and select record number 99
996).
	 
	Is there any possibility to express this in torque? We know
about some special features of some databases which can handle this. And
I think there are features in SQL 2003 for this.
	 
	 
	Thank you for your help.
	 
	 
	Tobias


Duke CE Privacy Statement
Please be advised that this e-mail and any files transmitted with it are confidential communication or may otherwise be privileged or confidential and are intended solely for the individual or entity to whom they are addressed.  If you are not the intended recipient you may not rely on the contents of this email or any attachments, and we ask that you  please not read, copy or retransmit this communication, but reply to the sender and destroy the email, its contents, and all copies thereof immediately.  Any unauthorized dissemination, distribution or copying of this communication is strictly prohibited.

Re: Finding position of previous selected record in new ordering

[Thomas Vandahl <tv...@apache.org>]

Tobias Hilka wrote:
> 
> Hello everyone,
>  
> we are facing the following problem:
>  
> We have a grid with many entries (up to several 100 000 entries). For
> performance reasons we only select the window which is visible for the
> user (about 50 entries). The rest of the grid is initialized with
> null-Items. So far so good.
>  
> But now our problem: If we do order the grid by firstname ascending and
> select the fith entry, then we order the by the same column descending,
> we want the selection to persist and therefore we want the selection
> window to contain the selected entry in its new environment (e.g.
> position 99 950 to 100 000 and select record number 99 996).
>  
> Is there any possibility to express this in torque? We know about some
> special features of some databases which can handle this. And I think
> there are features in SQL 2003 for this.

This is more a general Java problem that a special Torque one, I guess.
Say, you have a total of 100000 records. Your selected entry in
ascending sort order is number 67, your window goes from record number
51 to 100 (the second page). If you can be sure that your descending
sort order is exactly reverse, you can calculate the page number your
record will be in by (roughly)

	ceil((100000 - 67)/50) = 1999

Then you can use LargeSelect to access page number 1999 and your
selected record should be in there. (Maybe one page off,but you get the
point.)

Bye, Thomas.

---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org