Keyword search and pagination
In this chapter, you will learn to implement combined search with SQLAlchemy and use offset/limit for pagination.
ilike case-insensitive search
Example
posts = db.query(Post).filter(Post.title.ilike(f'%{keyword}%')).all()
# Multi-field combined search (OR relationship)
from sqlalchemy import or_
posts = db.query(Post).filter(
or_(
Post.title.ilike(f'%{keyword}%'),
Post.summary.ilike(f'%{keyword}%')
)
).all()
offset/limit pagination
Flask-SQLAlchemy provides a convenient.paginate()method.
FastAPI uses native SQLAlchemy, so you need to useoffset + limitManually implement pagination.
| Pagination method | Flask-SQLAlchemy | FastAPI (native SQLAlchemy) |
|---|---|---|
| Get current page data | .paginate(page, per_page).items | .offset(skip).limit(per_page).all() |
| Total records | pagination.total | query.count() |
| Total pages | pagination.pages | Manually calculate math.ceil(total / per_page) |
Pagination calculation formula
Example
from fastapi import Query
def get_pagination(page: int = 1, size: int = 6):
"""
Calculate Pagination Parameters
page: current page number (starting from 1)
size: number of items per page
"""
skip = (page - 1) * size # Skipped Records Count
return skip, size
# Usage example
skip, limit = get_pagination(page=2, size=6)
# skip = 6, limit = 6 → skip the first 6, get items 7-12
Complete search + pagination view
Example
from sqlalchemy import or_
from math import ceil
from fastapi import APIRouter, Depends, Request, Query
@router.get("/", name="index")
def index(
request: Request,
category: str | None = Query(None),
q: str | None = Query(None, description=“Search keywords”),
page: int = Query(1, ge=1, description="Page number"),
size: int = Query(6, ge=1, le=50, description="Items Per Page"),
db: Session = Depends(get_db)
):
"""首页：分类筛选 + 关键词搜索 + 分页"""
posts_query = db.query(Post).order_by(Post.created_at.desc())
# Category filter
if category:
posts_query = posts_query.join(Category).filter(Category.slug == category)
# Keyword search
if q:
keyword = f'%{q.strip()}%'
posts_query = posts_query.filter(
or_(
Post.title.ilike(keyword),
Post.summary.ilike(keyword)
)
)
# Pagination
total = posts_query.count()
total_pages = ceil(total / size)
skip = (page - 1) * size
posts = posts_query.offset(skip).limit(size).all()
return templates.TemplateResponse("index.html", {
"request": request,
"posts": posts,
"categories": db.query(Category).all(),
"category_slug": category or "",
"keyword": q or "",
"page": page,
"total_pages": total_pages,
"total": total
})
Query(1, ge=1)ingeis the abbreviation for greater than or equal. FastAPI's query parameters support complete numeric constraints: ge, le, gt, lt. If a user passes page=0, FastAPI will automatically return a 422 error.
Pagination navigation in templates
Example
{% if total_pages > 1 %}
<div class="pagination">
{% if page > 1 %}
<a href="/?page={{ page - 1 }}&category={{ category_slug }}&q={{ keyword }}">← Previous page</a>
{% endif %}
<span class="page-info">Page {{ page }} / {{ total_pages }} pages</span>
{% if page < total_pages %}
<a href="/?page={{ page + 1 }}&category={{ category_slug }}&q={{ keyword }}">Next page →</a>
{% endif %}
</div>
{% endif %}
Chapter summary
In this chapter, you implemented practical features for the list page: ilike case-insensitive search, or_() multi-field combined search, offset/limit manual pagination (replacing Flask's paginate), and Query() for constraining query parameter ranges.
Search + category filtering + pagination can be combined in any order, and the conditions will not be lost.
other extensions