Re: Paged Query

On Wed, Jul 4, 2012 at 6:25 AM, Hermann Matthes <hermann(dot)matthes(at)web(dot)de>wrote:

> I want to implement a "paged Query" feature, where the user can enter in a
> dialog, how much rows he want to see. After displaying the first page of
> rows, he can can push a button to display the next/previous page.
> On database level I could user "limit" to implement this feature. My
> problem now is, that the user is not permitted to view all rows. For every
> row a permission check is performed and if permission is granted, the row
> is added to the list of rows sent to the client.
> If for example the user has entered a page size of 50 and I use "limit 50"
> to only fetch 50 records, what should I do if he is only permitted to see
> 20 of these 50 records? There may be more records he can view.
> But if I don't use "limit", what happens if the query would return
> 5,000,000 rows? Would my result set contain 5,000,000 rows or would the
> performance of the database go down?
>
>
Sounds like your permission check is not implemented in the database. If
it were, those records would be excluded and the OFFSET-LIMIT combo would
be your solution. Also appears that you have access to the application.
If so, I would recommend implementing the permission check in the
database. Much cleaner from a query & pagination standpoint.

An alternative is to have the application complicate the query with the
appropriate permission logic excluding the unviewable records from the
final ORDER BY-OFFSET-LIMIT. This will give you an accurate page count.

IMHO, the worst alternative is to select your max page size, exclude rows
the user cannot see, rinse and repeat until you have your records per page
limit. Whatever you're ordering on will serve as the page number. Issue
with this solution is you may not have an accurate page count.

Luck.

-Greg