PostgreSQL - Pagination and join

Question

Imagine I have two DB tables with the following fields:

Shop: id; Apple: id, shop_id

A shop can have multiple apples, but an apple can only be in one shop.

I am trying to return a paginated list of 10 shops. However, I also want to do a join on Apple so I can get all the apples each of the 10 shops has. If i do something like:

select *
from shop s
left join apple a on s.id = a.shop_id
limit 10

This will only return 10 rows always. So if there are 2 shops with 5 apples each, it returns those, and not all 10 shops. It makes sense, but how can I write the query I want?

If you want to return every apple, you'll have to do a right join to the apples so It will always shows all the apples availables with its corresponding shop. — fluid undefined
– fluid undefined, Commented Sep 9, 2021 at 14:43

Julius Tuskenis · Accepted Answer · 2021-09-09 14:49:44Z

1

So you want 10 shops with their apples - move the limit into subquery:

SELECT
  *
FROM
  (SELECT * FROM shop ORDER BY id limit 10) s
  LEFT JOIN apple a on s.id = a.shop_id

If you like to receive just one record per shop you may aggregate apples:

SELECT
  s.id, array_agg(apple.id)
FROM
  shop s
  left join apple a on s.id = a.shop_id
GROUP BY
  s.id
ORDER BY
  s.id
LIMIT 10

answered Sep 9, 2021 at 14:49

Julius Tuskenis

1,63010 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL - Pagination and join

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related