I wonder if there is a way to generate ORDER BY sql in conjunction with the Paginated List. We are already mapping bean values to columns. If we were able to easily pass the bean property names onto a stack with '+','-' or '' for ascending, descending and no sort then we might be able to use some sort of dynamic sql order tag to auto generate ORDER BY sql. I haven't thought through the implementation details much. Maybe this is a bad idea in the end. But, it is a problem that I have heard of more than once.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
The user has the ability to sort either ASC, or DSC on any column on every page. the intention of the sort is to properly order the entire result set (ie.. 500 rows) and not just the page view. right now there is no way to sory all 500 records since only between 5-15 are in memory and the next db call might get records that are out of order..
I'm willing to accept a slight perf hit if we can get this working because i really don't want to store all 500 rows in memory as memory is more important to me.
Let me know if this makes sense!
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi guys.. I guess my issue is this.. lets say there are 500 results.. no original sort.. 20 per page the user then chooses to sort on this particular page.. The intention of this sort is to properly sort all 500 records. so that say.. if the order was key descending.. the key=500 displays on page 1. 480 displays on page 2 etc.. the user is then able to switch sorting on any column at will at any time during the paging process.
I'm not sure exactly how the paginate list object works so maybe i'm not making sense..?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Your need makes perfect sense. It's simply the case that PaginatedDataList doesn't support this concept yet. Luckily there is a simple workaround that I've used in my own apps:
1) Use a $substitution$ parameter for the ORDER BY and direction (ascending/descending) of the query query.
2) Keep the parameter object in the session.
3) Receive the sort request.
4) Change the parameters for the order and the direction on the parameter object.
5) Rerun the query for the paginated list.
6) Navigate to the current page.
The cost of this is about 6x that of a next-page request, but it's what the PaginatedDataList would need to do anyway, so the cost is fixed.
It really is simple to implement, and I hope that it works for you.
PS: I'm considering adding a .refresh() method to the PaginatedList interface that would take care of #5 and #6 for you.
Cheers,
Clinton
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I wonder if there is a way to generate ORDER BY sql in conjunction with the Paginated List. We are already mapping bean values to columns. If we were able to easily pass the bean property names onto a stack with '+','-' or '' for ascending, descending and no sort then we might be able to use some sort of dynamic sql order tag to auto generate ORDER BY sql. I haven't thought through the implementation details much. Maybe this is a bad idea in the end. But, it is a problem that I have heard of more than once.
Something beyond
ORDER BY $columnName$
?
Originally posted by moved because it was a duplicate:
By: Richard Nendza - rnendza
Paginated List and Sorting Feature Request
2004-05-05 08:35
Please see https://sourceforge.net/forum/forum.php?thread_id=1067182&forum_id=206693
Basically I'm having an user with a user triggered (jsp) page level sort when dealing with the PaginatedList Object..
ie.. lets say page size is set to 5 rows and 500 total rows are available in the DB.
Page 1
req # Desc
1 xxxx
5 xxxx
7 xxxx
8 xxxx
9 xxxx
Page 2
req # desc
12 xxx
14 xxx
16 xxx
18 xxx
20 xxx
The user has the ability to sort either ASC, or DSC on any column on every page. the intention of the sort is to properly order the entire result set (ie.. 500 rows) and not just the page view. right now there is no way to sory all 500 records since only between 5-15 are in memory and the next db call might get records that are out of order..
I'm willing to accept a slight perf hit if we can get this working because i really don't want to store all 500 rows in memory as memory is more important to me.
Let me know if this makes sense!
Hi guys.. I guess my issue is this.. lets say there are 500 results.. no original sort.. 20 per page the user then chooses to sort on this particular page.. The intention of this sort is to properly sort all 500 records. so that say.. if the order was key descending.. the key=500 displays on page 1. 480 displays on page 2 etc.. the user is then able to switch sorting on any column at will at any time during the paging process.
I'm not sure exactly how the paginate list object works so maybe i'm not making sense..?
Your need makes perfect sense. It's simply the case that PaginatedDataList doesn't support this concept yet. Luckily there is a simple workaround that I've used in my own apps:
1) Use a $substitution$ parameter for the ORDER BY and direction (ascending/descending) of the query query.
2) Keep the parameter object in the session.
3) Receive the sort request.
4) Change the parameters for the order and the direction on the parameter object.
5) Rerun the query for the paginated list.
6) Navigate to the current page.
The cost of this is about 6x that of a next-page request, but it's what the PaginatedDataList would need to do anyway, so the cost is fixed.
It really is simple to implement, and I hope that it works for you.
PS: I'm considering adding a .refresh() method to the PaginatedList interface that would take care of #5 and #6 for you.
Cheers,
Clinton