From: | Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> |
---|---|
To: | David Wheeler <david(at)wheeler(dot)net> |
Cc: | <sfpug(at)postgresql(dot)org> |
Subject: | Re: Offset |
Date: | 2003-08-28 04:52:29 |
Message-ID: | 20030827214715.G90515-100000@megazone.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | sfpug |
On Wed, 27 Aug 2003, David Wheeler wrote:
> Hi All,
>
> I've got a quick question about OFFSET. Bricolage has a paginated
> search interface. Currently, it retrieves and constructs all of the
> objects for a search from the database, but then only displays those
> for the current page. This is of course very inefficient, especially if
> there are 1000s of objects and you're displaying only 50/page.
>
> What I'd like to do is change the queries to use ORDER, LIMIT, and
> OFFSET to limit the number of objects fetched from PostgreSQL to just
> those for the current page. However, the queries to grab the objects
> use joins, and thus there will usually be multiple rows for each object.
>
> My question is this: Is there any way to use OFFSET or some other
> construct to limit the number of rows to the number of objects I'm
> fetching? There will always be an ID field to identify the objects as
> objects.
Might a subselect in from on the table containing the main objects with a
limit on that do what you need?
So instead of a query like:
select * from foo left outer join bar on (foo.id=bar.fooid);
you get something like:
select * from
(select * from foo limit <n> offset <m>) as foo left outer
join bar on (foo.id=bar.fooid);
Or am I misunderstanding? One problem with that is that I'm not sure at
what version it'll start working, so it may not gain you anything since
you need backward compatibility.
From | Date | Subject | |
---|---|---|---|
Next Message | David Wheeler | 2003-08-28 17:36:31 | Re: Offset |
Previous Message | David Wheeler | 2003-08-27 14:08:00 | Offset |