Keyword search and pagination

In this chapter, you will learn to use SQLAlchemy for combined search and optimize the list page with Flask-SQLAlchemy's built-in pagination.

ilike — case-insensitive search

SQLAlchemy'silikeThe method implements case-insensitive fuzzy matching.

It corresponds to SQL'sLIKEoperator, but ignores case.

Example

# Single field search
posts = Post.query.filter(Post.title.ilike('%flask%')).all()

# Multi-field combined search: or_() denotes OR relationship
from sqlalchemy import or_
posts = Post.query.filter(
or_(
Post.title.ilike('%flask%'),
Post.summary.ilike('%flask%')
)
).all()

Methods	SQL operators	Case Sensitive
like()	LIKE	Distinguish
ilike()	LIKE (case-insensitive)	Not distinguish
contains()	LIKE '%x%'	Distinguish
startswith()	LIKE 'x%'	Distinguish
in_()	IN	Exact match

paginate — Pagination

Flask-SQLAlchemy providespaginate()The method returns current page data + pagination metadata in a single call.

Example

# Refactor the index view from main.py
page = request.args.get('page', 1, type=int) # Current page number (default page 1)
per_page = 6 # Display 6 articles per page

# paginate(page, per_page, error_out)
# error_out=False: when page number exceeds range, returns an empty list instead of 404
pagination = posts_query.paginate(page=page, per_page=per_page, error_out=False)
posts = pagination.items # Article list for current page

pagination object attributes

Property	Type	Description
items	List	Current page data
page	int	Current page number
pages	int	Total pages
total	int	Total records
has_prev	bool	Whether there is a previous page
has_next	bool	Whether there is a next page
prev_num	int	Previous page number
next_num	int	Next page number

Complete search + pagination view

Example

# File path: app/blueprints/main.py
from flask import Blueprint, render_template, request
from sqlalchemy import or_
from app.models import Post, Category

main_bp = Blueprint('main', __name__)

@main_bp.route("/")
def index():
category_slug = request.args.get('category', '')
keyword = request.args.get('q', '').strip()
page = request.args.get('page', 1, type=int)
per_page = 6

# Basic query
posts_query = Post.query.order_by(Post.created_at.desc())

# Category filter
if category_slug:
posts_query = posts_query.join(Category).filter(Category.slug == category_slug)

# Keyword search (title or summary)
if keyword:
posts_query = posts_query.filter(
or_(
Post.title.ilike(f'%{keyword}%'),
Post.summary.ilike(f'%{keyword}%')
)
)

# Pagination
pagination = posts_query.paginate(
page=page, per_page=per_page, error_out=False)
posts = pagination.items

return render_template('index.html',
posts=posts, pagination=pagination,
categories=Category.query.all(),
category_slug=category_slug, keyword=keyword)

Query parameters are passed via URL (e.g.,/?q=flask&page=2), the pagination navigation links must carry the current search and category parameters, otherwise the conditions will be lost after paging.

Search box and pagination navigation in the template

Example

{% extends 'base.html' %}

{% block title %}EXAMPLE Blog - Home{% endblock %}

{% block content %}
<h2 class="section-title">Latest Articles</h2>


<div class="search-bar">
<form method="get" action="{{ url_for('main.index') }}">
<input type="text" name="q" value="{{ keyword }}"
placeholder=Search article titles or summaries... class="search-input">
{% if keyword %}
<a href="{{ url_for('main.index', category=category_slug) }}" class="clear-btn">✕</a>
{% endif %}
</form>
</div>


<div class="category-bar">
<a href="{{ url_for('main.index', q=keyword) }}"
class="{% if not category_slug %}active{% endif %}">All</a>
{% for cat in categories %}
<a href="{{ url_for('main.index', category=cat.slug, q=keyword) }}"
class="{% if category_slug == cat.slug %}active{% endif %}">
{{ cat.name }}
</a>
{% endfor %}
</div>

<p class="result-info">
Total {{ pagination.total }} articles
{% if keyword %}, searching "{{ keyword }}"{% endif %}
</p>

{% if posts %}
<div class="article-grid">
{% for post in posts %}
<a href="{{ url_for('posts.post_detail', post_id=post.id) }}" class="card-link">
<div class="article-card">
<div class="card-content">
<span class="card-category">{{ post.category.name }}</span>
<h3>{{ post.title }}</h3>
<p>{{ post.summary|truncate(80) }}</p>
<span class="card-date">{{ post.created_at.strftime('%Y-%m-%d') }}</span>
</div>
</div>
</a>
{% endfor %}
</div>


{% if pagination.pages > 1 %}
<div class="pagination">
{% if pagination.has_prev %}
<a href="{{ url_for('main.index', page=pagination.prev_num, category=category_slug, q=keyword) }}">← Previous page</a>
{% endif %}

{% for page_num in range(1, pagination.pages + 1) %}
{% if page_num == pagination.page %}
<span class="current">{{ page_num }}</span>
{% else %}
<a href="{{ url_for('main.index', page=page_num, category=category_slug, q=keyword) }}">{{ page_num }}</a>
{% endif %}
{% endfor %}

{% if pagination.has_next %}
<a href="{{ url_for('main.index', page=pagination.next_num, category=category_slug, q=keyword) }}">Next page →</a>
{% endif %}
</div>
{% endif %}

{% else %}
<p class="empty-tip">No matching articles found.</p>
{% endif %}
{% endblock %}

Chapter summary

In this chapter, you mastered the practical features of the list page: ilike case-insensitive search, or_() multi-field combined search, paginate() pagination query, and rendering pagination navigation in templates while preserving filter parameters.

Search + category filtering + pagination can be combined in any way, and the conditions will not be lost.

other extensions