Working with pages of data (LIMIT/OFFSET keyword)
От | Mike Christensen |
---|---|
Тема | Working with pages of data (LIMIT/OFFSET keyword) |
Дата | |
Msg-id | AANLkTilM-HhQJMmXOAltHcjJb2xOw7Vxpr0wMABJkOVs@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Working with pages of data (LIMIT/OFFSET keyword)
|
Список | 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
В списке pgsql-general по дате отправления: