From: | Mike Christensen <mike(at)kitchenpc(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Working with pages of data (LIMIT/OFFSET keyword) |
Date: | 2010-06-16 12:32:56 |
Message-ID: | AANLkTilM-HhQJMmXOAltHcjJb2xOw7Vxpr0wMABJkOVs@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Yeb Havinga | 2010-06-16 12:58:48 | Re: consequent PQsendQueryPrepared() failed: another command is already in progress |
Previous Message | Rob Richardson | 2010-06-16 12:29:39 | Dynamic triggers |