servant-util-beam-pg provides paginate_ function that works in simple cases, but doesn't work if each item you return is associated with a list of other items. For example, let's say that each house has a list of people living there and exactly one address. You have one table which stores (address, house) and another table which stores (house, person). You want to get [(address, [house])] list, i. e. all addresses and people who live there. AFAIU typically you will use left join for this purpose.
In our application we have a query that returns (Address, Maybe Person) items, then we group them by address and get [(Address, [House])] as we want. Sadly, one can't use paginate_ in this case to implement paginated getAddresses. Imagine you have [(addr1, person1), (addr1, person2), (addr2, person3)]) in DB and set limit to 2. You want to get 2 addresses in this case, but you will get only addr1.
Using Servant.Util.Dummy.Pagination.paginate outside of select is also tricky (and also obviously quite inefficient). In order to group all items by address ID (let's imagine address is a complex type and each address has a unique ID) using standard group* functions you need to sort them by address ID. And it's a problem if you also take SortingSpec and use sortBy_ which may require a different order.
I don't know what's the proper abstraction for this case, but probably there is a reasonable one. Also maybe I am just stupid and it's possible to write SQL query that would do exactly what I need (apply limit/offset to values from a specific column).
servant-util-beam-pgprovidespaginate_function that works in simple cases, but doesn't work if each item you return is associated with a list of other items. For example, let's say that each house has a list of people living there and exactly one address. You have one table which stores(address, house)and another table which stores(house, person). You want to get[(address, [house])]list, i. e. all addresses and people who live there. AFAIU typically you will use left join for this purpose.In our application we have a query that returns
(Address, Maybe Person)items, then we group them by address and get[(Address, [House])]as we want. Sadly, one can't usepaginate_in this case to implement paginatedgetAddresses. Imagine you have[(addr1, person1), (addr1, person2), (addr2, person3)])in DB and set limit to 2. You want to get 2 addresses in this case, but you will get onlyaddr1.Using
Servant.Util.Dummy.Pagination.paginateoutside ofselectis also tricky (and also obviously quite inefficient). In order to group all items by address ID (let's imagine address is a complex type and each address has a unique ID) using standardgroup*functions you need to sort them by address ID. And it's a problem if you also takeSortingSpecand usesortBy_which may require a different order.I don't know what's the proper abstraction for this case, but probably there is a reasonable one. Also maybe I am just stupid and it's possible to write SQL query that would do exactly what I need (apply limit/offset to values from a specific column).