CODEWITHSUNDEEP
sqlpostgresql
davidbaguetta
davidbaguetta

Reputation: 532

PostgreSQL - Pagination and join

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?

Upvotes: 0

Views: 1387

Answers (1)

Julius Tuskenis
Julius Tuskenis

Reputation: 1620

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

Upvotes: 1

Related Questions