Re: Offset

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.