2

I want to paginate through 2 different sources. I think I can do this using the thing where u join 2 queries into one, however, I don't want to merge them in parallel.

Is it possible to select the size of the first query, so I can limit the size of the second query; allowing me to properly paginate; first through the first query; and if partial / no result; then paginate through the second query.

I could obviously implement this at my application level, but I would prefer a single query.


After thinking about it, I could maybe do something like this?

with a as (... offset :offset limit :limit), 
    a_count as (select count(*) from a)
    select * a 
    union
    select ...
    offset (select count from a_count)
    limit greatest(:limit - (select count from a_count), 0)

Is this efficient? What can I improve?

0

1 Answer 1

3

Simply:

SELECT 'a' AS source, * FROM a
UNION ALL                         -- NOT just UNION!!
SELECT 'b' AS source, * FROM b    -- matching row type!
OFFSET :offset
LIMIT  :limit;

The added source is optional.

Unless using parentheses, LIMIT / OFFSET apply to the combined result set. While combined with UNION ALL, and no outer ORDER BY, you get rows from table a first, and rows from table b next.

If you need a particular sort order, add ORDER BY per table with parentheses. Like:

(SELECT * FROM a ORDER BY foo)
UNION ALL
(SELECT * FROM b ORDER BY bar)
OFFSET :offset
LIMIT  :limit;

Still no outer ORDER BY!

However, LIMIT / OFFEST for pagination scales terribly for big tables. Consider instead:

3
  • Thanks for this, I didn't realise how much I overcomplicated the query. And also the union all tip
    – Tobi
    Jan 30, 2022 at 17:32
  • Do you know how you would filter duplicates, will maintaining the same order
    – Tobi
    Jan 31, 2022 at 10:24
  • @Tobi: Please ask your new question as question (with the necessary details). Comments are not the place. You can always link to this one for context. Jan 31, 2022 at 14:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.