On 17 August 2010 23:08, Michael Swierczek <mike.swierczek@gmail.com> wrote:
> 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?
>
How about using LIMIT 11? That way you can display 10, but if you
count 11, you can provide a "next" link.
--
Thom Brown
Registered Linux user: #516935