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