A database query for implementing paging in a Play Framework application

As a quick note to self, one way to implement paging in a Play Framework application is to use the limit and offset arguments in a MySQL query. This way you can show X number of objects on one web page, then give the user Next and Previous links so they can scroll backwards and forwards through the dataset.

To implement paging with a MySQL query, use the limit and offset query parameters, as shown in this example:

def getUrls(page: Int = 0, numUrlsPerPage: Int = 50): Seq[Url] = db.withConnection { implicit c =>
    val offset = page * numUrlsPerPage
    val q = SQL"""
        select id, long_url, short_uri, notes, created
        from urls
        order by created desc
        limit $numUrlsPerPage
        offset $offset
    """
    q.as(fullUrlRowParser *)
}

With this code, if the page is 0, the offset will be 0, so the query will be this:

select id, long_url, short_uri, notes, created
from urls
order by created desc
limit 50
offset 0

Then if the page is 1, the offset will be 50, so the query will be this:

select id, long_url, short_uri, notes, created
from urls
order by created desc
limit 50
offset 50

As long as this method is called with the “page” the user wants to see, this query will give you the correct results for that page of information. Note that “offset 50” means “start on record 51.”