Thread: Working with pages of data (LIMIT/OFFSET keyword)

I'm generating a query on the fly to return a set of data, however I
only want to display 30 rows at a time to the user.  For this reason,
I use the LIMIT 30 OFFSET x clause on the select statement.  However,
I also want to know the total rows that match this query had there
been no limit, that way I can display to the user the total count and
the number of pages, and have Next/Prev buttons in my UI.  I can think
of the following ways to do this:

1) When the page loads, execute two totally separate queries.  One
that does the COUNT, and then another query immediately after to get
the desired page of data.  I don't like this as much because the two
queries will execute in separate transactions and it'd be nice if I
could just perform a single SQL query and get all this information at
once.  However, I will use this if there's no other way.

2) Execute two select statements in a single SQL query.  The first
table will contain a single row and column with just the count, then
the second table will contain the results for the page.  This should
work in practice, but I don't believe Npgsql supports the idea of a
single query returning multiple tables.  Or this might be a Postgres
limitation, I'm not sure.  MSSQL supports it, however.

3) Do something weird with query parameters or return values.  I've
messed around with this, but I don't think parameters, variables, or
return values are supported outside a stored function call.  Since I'm
generating my SQL statement on the fly, I'm not calling a function.  I
could write a function that takes parameters for all the values,
however I'm JOIN'ing different tables depending on what the user is
searching for.  For example, I don't join in the users table unless
they're filtering some sort of property of the user.

Anyone ran into this situation before?  What's the best approach here.  Thanks!

Mike

I would go with 2).

Npgsql supports multiple resultsets. You can pass a query separated by
semicolon ';'  or you can use a procedure call which return a setof
refcursor.

On both ways, you will need to call NextResult in your Datareader just
like with SQLServer.

You can check our user manual: http://manual.npgsql.org to get more
info about how to use refcursors

I hope it helps.

Please, let me know if you need more info.



On Wed, Jun 16, 2010 at 16:44, Szymon Guz <mabewlun@gmail.com> wrote:
>
>
> 2010/6/16 Mike Christensen <mike@kitchenpc.com>
>>
>> I'm generating a query on the fly to return a set of data, however I
>> only want to display 30 rows at a time to the user.  For this reason,
>> I use the LIMIT 30 OFFSET x clause on the select statement.  However,
>> I also want to know the total rows that match this query had there
>> been no limit, that way I can display to the user the total count and
>> the number of pages, and have Next/Prev buttons in my UI.  I can think
>> of the following ways to do this:
>>
>> 1) When the page loads, execute two totally separate queries.  One
>> that does the COUNT, and then another query immediately after to get
>> the desired page of data.  I don't like this as much because the two
>> queries will execute in separate transactions and it'd be nice if I
>> could just perform a single SQL query and get all this information at
>> once.  However, I will use this if there's no other way.
>>
>
> Just run them in one transaction.
> You can also just show the Next/Prev buttons and then do something just for
> the case where there is no data.
> Or use LIMIT 31 so you always know that there is the next page with at least
> one record.
> regards
> Szymon Guz



--
Regards,

Francisco Figueiredo Jr.
Npgsql Lead Developer
http://www.npgsql.org
http://fxjr.blogspot.com
http://twitter.com/franciscojunior

Sweet!  I knew there was a way to do this, I'll mess around with it
more in a bit.

On Thu, Jun 17, 2010 at 10:38 AM, Francisco Figueiredo Jr.
<francisco@npgsql.org> wrote:
> I would go with 2).
>
> Npgsql supports multiple resultsets. You can pass a query separated by
> semicolon ';'  or you can use a procedure call which return a setof
> refcursor.
>
> On both ways, you will need to call NextResult in your Datareader just
> like with SQLServer.
>
> You can check our user manual: http://manual.npgsql.org to get more
> info about how to use refcursors
>
> I hope it helps.
>
> Please, let me know if you need more info.
>
>
>
> On Wed, Jun 16, 2010 at 16:44, Szymon Guz <mabewlun@gmail.com> wrote:
>>
>>
>> 2010/6/16 Mike Christensen <mike@kitchenpc.com>
>>>
>>> I'm generating a query on the fly to return a set of data, however I
>>> only want to display 30 rows at a time to the user.  For this reason,
>>> I use the LIMIT 30 OFFSET x clause on the select statement.  However,
>>> I also want to know the total rows that match this query had there
>>> been no limit, that way I can display to the user the total count and
>>> the number of pages, and have Next/Prev buttons in my UI.  I can think
>>> of the following ways to do this:
>>>
>>> 1) When the page loads, execute two totally separate queries.  One
>>> that does the COUNT, and then another query immediately after to get
>>> the desired page of data.  I don't like this as much because the two
>>> queries will execute in separate transactions and it'd be nice if I
>>> could just perform a single SQL query and get all this information at
>>> once.  However, I will use this if there's no other way.
>>>
>>
>> Just run them in one transaction.
>> You can also just show the Next/Prev buttons and then do something just for
>> the case where there is no data.
>> Or use LIMIT 31 so you always know that there is the next page with at least
>> one record.
>> regards
>> Szymon Guz
>
>
>
> --
> Regards,
>
> Francisco Figueiredo Jr.
> Npgsql Lead Developer
> http://www.npgsql.org
> http://fxjr.blogspot.com
> http://twitter.com/franciscojunior
>