On 17 August 2010 23:08, Michael Swierczek <mike(dot)swierczek(at)gmail(dot)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
> (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
> 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.
Registered Linux user: #516935
In response to
pgsql-novice by date
|Next:||From: STA||Date: 2010-08-18 03:39:14|
|Subject: List of User Defined Types?|
|Previous:||From: Michael Swierczek||Date: 2010-08-17 22:08:40|
|Subject: query with results and also results size|