Querying with filter and filter_by in Flask - Step-by-Step Execution

Concept Flow - Querying with filter and filter_by

Start Query on Model

↓

Choose filter or filter_by

↓

filter_by: simple key=value

↓

Build SQL WHERE clause

↓

filter: complex expressions

↓

Build SQL WHERE clause

↓

Execute Query

↓

Return Results

This flow shows how a query starts on a model, then uses filter_by for simple key=value filters or filter for complex expressions, builds the SQL WHERE clause, executes the query, and returns results.

Execution Sample

Flask

user = User.query.filter_by(name='Alice').first()
posts = Post.query.filter(Post.user_id == user.id).all()

This code finds the first user named Alice, then finds all posts by that user.

Execution Table

Step	Action	Query Part	Result
1	Start query on User model	SELECT * FROM user	Query object created
2	Apply filter_by(name='Alice')	WHERE name = 'Alice'	Query updated with filter_by
3	Execute first()	LIMIT 1	Returns first User with name Alice
4	Start query on Post model	SELECT * FROM post	Query object created
5	Apply filter(Post.user_id == user.id)	WHERE user_id = user's id	Query updated with filter expression
6	Execute all()	Fetch all matching rows	Returns list of Posts by Alice

💡 Query executes after filters applied; first() returns one object, all() returns list

Variable Tracker

Variable	Start	After Step 3	After Step 6
user	null	User object with name='Alice'	User object unchanged
posts	null	null	List of Post objects by Alice

Key Moments - 2 Insights

Why use filter_by for simple filters and filter for complex ones?

filter_by accepts simple key=value pairs (see Step 2), while filter accepts expressions like Post.user_id == user.id (Step 5). Using the right one matches the query syntax.

What does first() do compared to all()?

first() returns the first matching record or null (Step 3), while all() returns a list of all matching records (Step 6). This affects the type of result you get.

Visual Quiz - 3 Questions

Look at the execution table, what SQL clause is added at Step 2?

A LIMIT 1

B WHERE name = 'Alice'

C WHERE user_id = Alice's id

D ORDER BY name

💡 Hint

Check the 'Query Part' column at Step 2 in the execution_table

At which step does the query return a list of posts?

A Step 3

B Step 4

C Step 6

D Step 2

💡 Hint

Look at the 'Result' column for Step 6 in the execution_table

If we replaced filter_by(name='Alice') with filter(User.name == 'Alice'), what changes in the execution?

A filter allows more complex expressions, so syntax changes

B No change, both produce the same SQL

C filter_by cannot be replaced by filter

D filter_by returns a list, filter returns one object

💡 Hint

Compare Step 2 and Step 5 'Query Part' columns and the explanation in key_moments

Concept Snapshot

Querying with filter and filter_by in Flask-SQLAlchemy:
- Use filter_by(key=value) for simple equality filters.
- Use filter(expression) for complex conditions.
- filter_by builds WHERE with key=value.
- filter builds WHERE with expressions like Model.field == value.
- Use first() to get one result, all() for list.
- Queries build SQL then execute to return results.

Full Transcript

In Flask-SQLAlchemy, querying data involves starting a query on a model. You can use filter_by for simple filters like key=value, which builds a WHERE clause with equality. For more complex conditions, use filter with expressions like Model.field == value. After applying filters, you execute the query with methods like first() to get one record or all() to get all matching records. This process builds the SQL query step-by-step and returns the results accordingly.