CODEWITHSUNDEEP
postgresql
zumzum
zumzum

Reputation: 20278

Postgresql, define limit within join?

I have the following tables:

library
    |id|...
books
    |id|library_id (fk)|...
permissions
    |user_id (fk)|library_id(fk)|read bool| ...

I want to find the most 10 recent books a certain user (id) can see in any library the user has read permissions on.

A library can have many books

A user can have one permissions record per library with read bool true or false

What I am not sure how to do is to limit the result of books per library they're in to a certain limit I want to set dynamically.

normally I would do this:

select b.id,
       l.id
from book b
inner join library l on l.id = b.library_id
inner join permissions p on l.id = p.library_id
where p.user_id=${user.id} and p.read=true
order by b.created_at desc

I am not sure how to only return the most 10 (limit) recent books per library the user has access to.

How can I set the limit per library?

Upvotes: 0

Views: 28

Answers (1)

jjanes
jjanes

Reputation: 44423

You would do a lateral join:

select b.id,
       l.id
from permissions p
inner join library l on l.id = p.library_id
cross join lateral 
    (select * from book where library_id=l.id order by created_at desc LIMIT 10) b
where p.user_id=${user.id} and p.read=true

Upvotes: 1

Related Questions