Add "advanced" version of paginate_

[gromakovsky]

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).

[gromakovsky]

Might be related: https://stackoverflow.com/questions/45750740/limit-query-by-count-distinct-column-values

[Martoon-00]

Did you manage to implement the desired thing eventually? If there was no servant-util, how would you write your query?

Looks like the stackoverflow question you mention has an answer mentioning window functions, and AFAIU they are supported by beam-postresql, maybe you managed to construct the desired query using them?

[gromakovsky]

If there was no servant-util, how would you write your query?

I don't know, I would spend some time trying to figure it our maybe would succeed eventually.

Looks like the stackoverflow question you mention has an answer mentioning window functions, and AFAIU they are supported by beam-postresql, maybe you managed to construct the desired query using them?

I am new to beam-postgres and decided to not even try. Maybe @sashasashasasha151 will try to implement that approach since he's more experienced with beam-postgres.

Did you manage to implement the desired thing eventually?

So I ended up implementing it by getting all items and then carefully grouping and paginating them. Just in case you want to see code, it's in this PR: https://github.com/serokell/edna/pull/75/files#diff-8ec788fddbaafebffde7742cb5f9728f55a9a12928455e19a02f888022028310