Querying, Filtering & Pagination

Book.query.all() is fine for a handful of rows, but Bookshelf's real "my currently reading books" or "everything tagged sci-fi, newest first" screens need to filter, order, and page through results rather than loading every row and sorting them in Python. This lesson covers the query-building tools Flask-SQLAlchemy provides for exactly that.

filter_by() for Simple Equality

When a condition is a straightforward "this column equals this value," .filter_by() is the shortest way to write it, using plain keyword arguments:

currently_reading = Book.query.filter_by(status='reading').all()

filter() for Comparisons and Complex Conditions

.filter_by() only handles equality against a bare keyword, so anything beyond "equals" — comparisons, OR logic, joins across relationships — needs .filter() instead, which takes a real expression built from the model's columns:

long_books = Book.query.filter(Book.pages > 300).all()

The distinction to keep straight: reach for .filter_by(field=value) when the check is a plain equality on one column, and reach for .filter(Model.field > value) the moment you need a comparison operator, a combination of conditions, or a condition that spans a relationship.

Combining Conditions with and_() and or_()

For multiple conditions, and_() and or_() from the sqlalchemy package combine them explicitly. Here's Bookshelf pulling every book that is either currently being read, or already rated highly:

from sqlalchemy import or_

notable_books = Book.query.filter(
    or_(Book.status == 'reading', Book.rating >= 4)
).all()

and_() works the same way for conditions that must all be true at once — and is worth knowing about even though chaining multiple .filter() calls back to back already combines them with AND implicitly:

from sqlalchemy import and_

finished_long_books = Book.query.filter(
    and_(Book.status == 'finished', Book.pages > 300)
).all()

Ordering Results

.order_by() sorts a query's results, ascending by default:

alphabetical = Book.query.order_by(Book.title).all()

For descending order, call .desc() on the column itself:

highest_rated_first = Book.query.order_by(Book.rating.desc()).all()

Querying Through the Many-to-Many Tag Relationship

Recall the Book.tags many-to-many relationship from Models & Relationships. Filtering by a related collection uses .any(), which matches any Book that has at least one related Tag satisfying the condition inside it:

sci_fi_books = Book.query.filter(Book.tags.any(name='sci-fi')).all()

Book.tags.any(name='sci-fi') reads naturally as "any of this book's tags has name == 'sci-fi'" — SQLAlchemy translates that into the appropriate join through the book_tags association table behind the scenes, with no manual join required.

Pagination with .paginate()

Loading every matching Book at once works for a small personal library, but a shelf with hundreds of books needs paging. Flask-SQLAlchemy's .paginate() method handles that directly on a query:

@app.route('/books')
def book_list():
    page_number = request.args.get('page', 1, type=int)
    pagination = Book.query.order_by(Book.title).paginate(page=page_number, per_page=10)

    return render_template(
        'books/list.html',
        books=pagination.items,
        pagination=pagination
    )

request.args.get('page', 1, type=int) — covered in Request & Response Objects — reads the current page number straight out of the URL's query string (/books?page=2), defaulting to page 1 if it's missing. .paginate(page=page_number, per_page=10) returns a pagination object with everything a template needs to render the current page and its navigation:

A Jinja2 template can then render Previous/Next links directly off those properties:

{% if pagination.has_prev %}
  <a href="{{ url_for('book_list', page=pagination.prev_num) }}">Previous</a>
{% endif %}

{% if pagination.has_next %}
  <a href="{{ url_for('book_list', page=pagination.next_num) }}">Next</a>
{% endif %}

Other Query-Ending Methods Worth Knowing

Alongside .all(), two more methods commonly finish off a query:

• .first() — returns just the first matching row, or None if nothing matched, without loading the rest
• .count() — returns how many rows match, without loading any of the actual row data

most_recent = Book.query.order_by(Book.id.desc()).first()
total_finished = Book.query.filter_by(status='finished').count()