Flask loop through a db query in jinja2 with filter

I have a category table and a post table. For each category there are many posts. There is a foreign key relationship between these two tables so that each row in the post table also has a column for associate category id. Now, I'm querying category table and sending it to the jinja2 template. In the template, I am looping through each category and displaying all posts associated with it. Here is the code:

{% for val in cat_list %}
    {% for i in val.posts.all() %}
        <p>{{ i.msg }}</p>
    {% endfor %}
{% endfor %}

this gives me category wise list of all posts.

Now, I do not want to display posts that has been deleted i.e. their status is Deleted. So I'm trying the following:

{% for val in cat_list %}
    {% for i in val.posts.filter(val.posts.status != 'Deleted' ).all() %}
        <p>{{ i.msg }}</p>
    {% endfor %}
{% endfor %}

But it is not filtering out the deleted posts and is still giving me all posts, deleted and non-deleted.

What am I missing?

I tried using .filter() to filter out 'Deleted' posts, but it seems my query formation is incorrect.

Solution

I think you are trying to apply filter() to a predefined relationship(). However, this is not possible.
Furthermore, I think it makes more sense to formulate the query completely within the endpoint rather than completing it in the template.

The following example in SQLAlchemy 2.* uses an additionally defined relationship, which is filtered using a primaryjoin clause.

from flask import (
    Flask, 
    render_template, 
)
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import (
    DeclarativeBase, 
    Mapped 
)
from typing import List
import random

class Base(DeclarativeBase):
    pass

app = Flask(__name__)
app.config.from_mapping(
    SECRET_KEY='your secret here', 
    SQLALCHEMY_DATABASE_URI='sqlite:///example.db', 
)
db = SQLAlchemy(app, model_class=Base)

class Category(db.Model):
    id:Mapped[int] = db.mapped_column(db.Integer, primary_key=True)
    title:Mapped[str] = db.mapped_column(db.String(), nullable=False, unique=False)
    posts:Mapped[List['Post']] = db.relationship(back_populates='category')
    posts_published:Mapped[List['Post']] = db.relationship(
        primaryjoin="and_(Category.id == Post.category_id, Post.status != 'Deleted')",
        viewonly=True, 
    )

class Post(db.Model):
    id:Mapped[int] = db.mapped_column(db.Integer, primary_key=True)
    msg:Mapped[str] = db.mapped_column(db.String(), nullable=False)
    status:Mapped[str] = db.mapped_column(db.String(), nullable=False, default='')
    category_id:Mapped[int]= db.mapped_column(db.Integer, db.ForeignKey('category.id'), nullable=False)
    category:Mapped['Category'] = db.relationship(back_populates='posts')

with app.app_context():
    db.drop_all()
    db.create_all()

    categories = [Category(title=f'Category-{i}') for i in range(1,5)]
    db.session.add_all(categories)
    db.session.commit()

    posts = [Post(
        msg=f'Post-{i}', 
        category= random.choice(categories), 
        status='Deleted' if i % 2 == 0 else None, 
    ) for i in range(1, 21)]
    db.session.add_all(posts)
    db.session.commit()

@app.route('/')
def index():
    categories = db.session.scalars(db.select(Category)).all()
    return render_template('index.html', **locals())

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Index</title>
</head>
<body>
    {% for c in categories -%}
        <h1>{{ c.title }}</h1>
        <ul>
        {% for p in c.posts_published -%}
            <li>{{ p.msg }}</li>
        {% endfor -%}
        </ul>
    {% endfor -%}
</body>
</html>