when using limit and offset in mysql for pagination, how can I tell this is last page or not?

Question

is there any built-in pre-defined mysql method to check if query result with limit offset is last page or not? I found some recommendation to utilize sql_calc_found_rows and found_rows(), but it requires two different query, although I need more simple and straightforward way.

Ultimately, I am using FastAPI and SqlAlchemy right now and I want something equivalent like spring data jpa, Pageable method "isFirst()" and "isLast()". Is there any elegant way to implement those two method using FastAPI and SqlAlchemy?

I know Flast-SqlAlchemy supports hasNext() boolean-returning method, but I guess vanilla SqlAlchemy does not.

Answer 1

A trick I have used before is to get 1 more result than you need and just use it to determine if there is more. You will not know the last page's offset but you will know if the current offset is the last page.

For example if your page_count is something like 10 results then send a limit of 11. Then if you get 10 or less results you know this is the last page but if you get 11 you know there is another page and you show 10 and throw away the 11th result.

Python PSUEDO code

def get_page_of_results(current_offset, PAGE_COUNT=10):
    results = session.query(Result).offset(current_offset).limit(PAGE_COUNT + 1).all()
    return dict(
        is_first=current_offset == 0,
        is_last=len(results) < PAGE_COUNT,
        next_offset=current_offset + PAGE_COUNT,
        # Trim off last result
        results=results[:PAGE_COUNT])

The performance between 10 and 11 shouldn't be a big deal unless you are loading huge object trees or something.

Also this method is db agnostic.