Strategies on Pagination
Posted: Tue Jan 13, 2009 1:18 am
I wanted to hear some of your strategies on paginating record results from the database. Note in my case I'm using PostgreSQL and I have LIMIT and OFFSET available to me.
If you've already beat this issue like a dead horse, please give me the hyperlink to that discussion. Thanks.
1. Right now, I'm using a GET form post on my search form because that's what I typically see in search forms, rather than handsome URLs, and it permits me to provide hyperlinks in other parts of the website that allow me to store a query, such as going to employer profiles and being able to click on a hyperlink to see jobs from just that employer.
2. So I guess the first thing I need to do is get the count of rows in the query. If the query is huge, I hope my SELECT will return that value correctly to me. On MS SQL Server, for instance, it used to be inaccurate unless you cycled through all the records -- don't know about MySQL or PostgreSQL.
3. I want to display 10 job search results per page. So, I have to divide the row count by that value and display 1, 2, 3, 4 ... up to the sum of that division. I take it that if they don't change the form fields in the search from their last search, that I can cache this count in a cookie or session var?
4. Next I need to display First, Prev, Next, and Last wrapped around that 1, 2, 3, 4, ...etc stuff. The First is easy -- it goes to OFFSET 1, LIMIT 10. The Last is slightly easy -- based on the value I computed from step 3, I do OFFSET of that value, LIMIT 10. The catch is that someone may add a record here or there, and so by doing a LIMIT 10 on the last page, I can hide that fact unless they start a different query.
5. The Prev needs to remember what page they are currently on and change hyperlink such that the OFFSET value has the page before it.
6. The Next needs to remember what page they are currently on and change the hyperlink such that the OFFSET value has the page after it.
7. Prev needs to be disabled on the first page. Next needs to be disabled on the last page.
8. What gets tricky is when the user jumps around on the various parts of the query -- the Prev and Next need to keep up with those changes.
9. As I build the SQL, I'm avoiding JOINs where possible. If a certain criteria is not used in the advanced search, I will not JOIN for its data.
10. I'm not caching this search result data into a temporary table.
11. I'm not using a PostgreSQL view -- to me, a view won't speed up an ad-hoc query, right?
If you've already beat this issue like a dead horse, please give me the hyperlink to that discussion. Thanks.
1. Right now, I'm using a GET form post on my search form because that's what I typically see in search forms, rather than handsome URLs, and it permits me to provide hyperlinks in other parts of the website that allow me to store a query, such as going to employer profiles and being able to click on a hyperlink to see jobs from just that employer.
2. So I guess the first thing I need to do is get the count of rows in the query. If the query is huge, I hope my SELECT will return that value correctly to me. On MS SQL Server, for instance, it used to be inaccurate unless you cycled through all the records -- don't know about MySQL or PostgreSQL.
3. I want to display 10 job search results per page. So, I have to divide the row count by that value and display 1, 2, 3, 4 ... up to the sum of that division. I take it that if they don't change the form fields in the search from their last search, that I can cache this count in a cookie or session var?
4. Next I need to display First, Prev, Next, and Last wrapped around that 1, 2, 3, 4, ...etc stuff. The First is easy -- it goes to OFFSET 1, LIMIT 10. The Last is slightly easy -- based on the value I computed from step 3, I do OFFSET of that value, LIMIT 10. The catch is that someone may add a record here or there, and so by doing a LIMIT 10 on the last page, I can hide that fact unless they start a different query.
5. The Prev needs to remember what page they are currently on and change hyperlink such that the OFFSET value has the page before it.
6. The Next needs to remember what page they are currently on and change the hyperlink such that the OFFSET value has the page after it.
7. Prev needs to be disabled on the first page. Next needs to be disabled on the last page.
8. What gets tricky is when the user jumps around on the various parts of the query -- the Prev and Next need to keep up with those changes.
9. As I build the SQL, I'm avoiding JOINs where possible. If a certain criteria is not used in the advanced search, I will not JOIN for its data.
10. I'm not caching this search result data into a temporary table.
11. I'm not using a PostgreSQL view -- to me, a view won't speed up an ad-hoc query, right?