Reputation: 20278
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
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