How to specify pagination for select query (Read side)

Lagom Lagom Persistence API
1

Hallo Lagom Team,

i have a question regarding the following configuration parameter: lagom.persistence.read-side.cassandra.max-result-size. Is this parameter defining the maximun number of rows of a result set or is it used to limit the number of rows per page for a select query with pagination?
I have the following use case, i want to stream rows from a huge table (the table can have millions of rows). I don’t want to get all the rows at once, but i want to have some kind of pagination.
Can i use the parameter lagom.persistence.read-side.cassandra.max-result-size to specify the pagination size of the result set?

Thank you
Gervais

2

That’s used to set the default fetch size for queries in the DataStax Cassandra driver. The driver does its own internal pagination, which is described in detail in the linked page. This parameter sets the size of each page.

You’ll also want to be sure to use one of the CassandraSession.select methods that returns a Source and avoid selectAll, which will try to consume the whole query into a list.

3

Cool thank you @TimMoore

1 Like
4

Hi Tim,

I’m trying to apply your explanation to lagom/online-auction-scala) project, specifically in this file: https://github.com/lagom/online-auction-scala/blob/master/item-impl/src/main/scala/com/example/auction/item/impl/ItemRepository.scala#L41 in order to paginate avoiding “selectAll” statements. At this point, I have:

val statement = new SimpleStatement(
                """
      SELECT * FROM itemSummaryByCreatorAndStatus
      WHERE creatorId = ? AND status = ?
      ORDER BY status ASC, itemId DESC
    """, creatorId, status.toString)

statement.setFetchSize(2)

val rs = session.select(statement)

rs.map(a => {
                ItemSummary(
                    a.getUUID("itemId"),
                    a.getString("title"),
                    a.getString("currencyId"),
                    a.getInt("reservePrice"),
                    api.ItemStatus.withName(a.getString("status"))
                )
            })

But I don’t know if I am in the right path. I checked https://docs.datastax.com/en/developer/java-driver/3.2/manual/paging/ and other docs related with Lagom but I didn’t find how Source works. Could you please help me?

Regards

5

Hi, even I am trying the same thing… Any pointers? Did you give with any other approach ?

6

Hi Vinaya,

You can refer to this pull request: https://github.com/lagom/online-auction-scala/pull/89.

Regards

7

This will do a linear search on the results right ? dropping elements before offset.

Cassandra also supports a pageState, any reasons to not use that which I might have missed … (other than cross version constraints)
Was able to get a minimal working paging API with pageState and fetchSize support

8

Hi Vinaya,

This pull request contains PagingState logic according to Datastax driver documentation. Please, check the last commit https://github.com/lagom/online-auction-scala/pull/89/commits/891e2bf0bc6ecff9fad3701e842737a65a344d46 where I started PagingState implementation. There are some suggestions https://github.com/lagom/online-auction-scala/pull/89#issuecomment-416135001 provided by @TimMoore about how the implementation should be. Basically, the idea is to save the PagingState string version as a query parameter in the URL to the next page.

Regards

9

Hi Frank,

Thanks for pointing me to the latest code, even I am doing something similar. Whenever I get the result set, I am sending the pageState (resultset.executionInf()…getPagingState().toString() ) in the response as next, and this is expected in the following API call as param start. This is similar to what you and Tim are suggesting. Thanks!

I was thinking that Lagom itself provides some underlying API to paginate to UI, and I was doing it the wrong way!!! Thanks a lot. Do tell if you think of some more efficient way of handling paging.

Regards,
Vinaya