I query a view based upon a number of user-input criteria. The
results are displayed to the user 10 rows at a time. In my page view
for the end user, I want to list "search results, records X through X
+ 9 out of (total)".
Right now every time a person clicks through a page, the page request
results in two queries. One query gets the count of the total number
of records that meet the search criteria. The second query gets the
10 records for the current page.
I want to reduce the number of round trips to the database, and if
possible stop from performing the search twice.
I can combine the two queries like this:
select first.*, second.total_count
(select * from patient_data_view where (...) order by ... offset _
limit 10) first
join
(select count(id) as "total_count" from patient_data_view where (...))
second on true;
Doing it that way moves from two trips between the application and the
database to one, but that one query still has two searches based upon
the same criteria in it.
I cannot store the count associated with each search between page
views, because there are other people accessing the system
concurrently and the number of records meeting the search criteria can
change.
Is there a way to write the query so that it does not perform the search twice?
Thanks,
-Mike