YouTip

## Keyword Search and Pagination In this chapter, you will learn to implement combined search with SQLAlchemy and implement pagination with offset/limit. * * * ## ilike Case-Insensitive Search ## Example # Single Field Search posts = db.query(Post).filter(Post.title.ilike(f'%{keyword}%')).all() # Multi-field Combined Search (OR) 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 we need to manually implement pagination with offset + limit. | 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` | Manual calculation math.ceil(total / per_page) | ### Pagination Calculation Formula ## Example import math 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 # Number of records to skip return skip, size # Usage example skip, limit = get_pagination(page=2, size=6) # skip = 6, limit = 6 → Skip first 6 records, get records 7-12 * * * ## Complete Search + Pagination View ## Example # File path: routers/posts.py (extend index route) 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 keyword"), 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) ): """Homepage: Category filter + Keyword search + pagination""" 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 }) > `ge` in `Query(1, ge=1)` is an abbreviation for greater than or equal. FastAPI's query parameters support full numeric constraints: ge, le, gt, lt. If a user passes page=0, FastAPI will automatically return a 422 error. * * * ## Pagination Navigation in Template ## Example {% if total_pages > 1 %} {% 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 using Query() to constrain query parameter ranges. Search + category filter + pagination can be combined arbitrarily, and conditions will not be lost.