CODEWITHSUNDEEP
scalaiterator
Stefan Kunze
Stefan Kunze

Reputation: 751

Database Iterators in Conjunction with Paging

my question is of a conceptual nature but very important to me:

When using databases you typically get an iterator back from the query containing the resultset. However: when you want to do paging, you wanna be able to move that resultset back and forth. this is however not possible using an iterator. so the naive idea here would be to use a conversion to list. but that again takes a time of O(n) which can cause a serious problem performance wise.

Im sure there must be a solution to this problem (other than using elasticsearch :D ). What's the best method of approaching this problem?

with best regards,

Stefan

Upvotes: 1

Views: 414

Answers (1)

Faiz
Faiz

Reputation: 16245

You want paging, but you don't want to do an O(n) loading of your unpaged result set into memory. Fair enough - logically that implies that the database must hand you paged chunks. I think most RDMS databases have something like 'LIMIT' and 'OFFSET' SQL:

select id, name from foo where date > ? LIMIT $start, $page_size;

If you were dealing with MySQL, and were writing raw SQL, then it would be something like that. But with libraries such as Slick, you could have

val query = for { 
  d <- Parameter[Date]
  f <- foo if f.date > d
} yield (f.id, f.name)

So to get all rows unpaged you do

query(yesterday).list
// Select id, name from foo

And if you want paging, it's simple:

query(yesterday).drop(20).take(5).list
// Select id, name from foo limit 20, 5 ; %% whatever; I suck at SQL and can't remember syntac

                                          %% but you get the point.

Which will return a list of (Id, Name), of 5 elements, assuming your want only 5 per page. That means this subsequence will be the 5th page of results.

Not that this is what you'd likely do if instead of query(yesterday) you had a List of results in memory: SLICK provides you with an abstraction for queries, a Query type that contains many useful methods usually found in collections. The .list method is what actually executes the final query to get you a List[T] (in this example List[(Int, String)]) but before calling it you can 'page' your results (by calling .take, drop etc, that build uopn the original query) and in this example the SQL does the paging for you and SLICK generates that SQL so you just do .take, .drop or whatever.

It helps if your model layer takes advantage of SLICK's composability: you define basic queries in SLICK instead of writing raw SQL and these queries are usable as building blocks for others queries.

Upvotes: 1

Related Questions