Re: query with results and also results size

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