如何在Jinja循环中区分不同健身计划的关联训练动作？

我有三个数据库表：存储训练动作的exercise表、记录健身计划实例的routine_exercise表，以及关联计划和动作的routine表。现在通过SQL查询能拿到需要的字段，但在Jinja模板里无法正确过滤，导致所有训练动作（哪怕属于不同计划）都被归到第一个健身计划下。

数据库表结构 #

CREATE TABLE routine (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    routine_id INTEGER NOT NULL,
    exercise_id INTEGER NOT NULL,
    weight REAL,
    sets INTEGER NOT NULL,
    reps INTEGER NOT NULL,
    FOREIGN KEY (user_id) REFERENCES user (id),
    FOREIGN KEY (exercise_id) REFERENCES exercise (id),
    FOREIGN KEY (routine_id) REFERENCES routine_exercise (id)
);

CREATE TABLE routine_exercise (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    routine_name TEXT NOT NULL,
    created DATE NOT NULL DEFAULT CURRENT_DATE,
    FOREIGN KEY (user_id) REFERENCES user (id)
);

CREATE TABLE exercise (
    id  INTEGER PRIMARY KEY AUTOINCREMENT,
    exercise_name   TEXT NOT NULL,
    target  TEXT NOT NULL
);

后端代码 #

@bp.route('/', methods=('GET', 'POST'))
def index():
    db = get_db()
    routine = db.execute("SELECT * FROM routine_exercise WHERE user_id = ?", (g.user['id'],))
    routine_exercise = db.execute("SELECT name, weight, sets, reps FROM exercise, routine WHERE exercise.id = routine.exercise_id AND user_id = ?", (g.user['id'],))
    if routine is None:
        return render_template("routine/index.html")
    return render_template("routine/index.html", routine=routine, routine_exercise=routine_exercise)

Jinja模板代码 #

{% for n in routine %}     
        <h5>{{ n['routine_name'] }} ({{ n['created'] }})</h5>
            {% for e in routine_exercise %}
                # 希望将训练动作按不同计划分类
                {% if n['routine_id'] == e['id'] %}
                    <li>{{ e['name'] }} {{ e['weight'] }}kg {{ e['sets'] }}x{{ e['reps'] }}</li>
                {% endif %}
            {% endfor %}    
            {% if not n.last %}
                <br>
            {% endif %}
    {% endfor %}

我试过多种if语句写法，都没解决问题。

解决方案 #

1. 修正后端查询逻辑，提前按计划分组数据

原查询的核心问题是动作数据和计划数据没有绑定关系，且缺少匹配所需的计划ID。需要关联三个表查询，并在后端将数据按计划分组：

@bp.route('/', methods=('GET', 'POST'))
def index():
    db = get_db()
    # 关联三表，获取计划及对应动作的完整数据
    query = """
        SELECT re.id as routine_id, re.routine_name, re.created, 
               ex.exercise_name as name, r.weight, r.sets, r.reps
        FROM routine_exercise re
        JOIN routine r ON re.id = r.routine_id
        JOIN exercise ex ON r.exercise_id = ex.id
        WHERE re.user_id = ?
        ORDER BY re.created DESC
    """
    results = db.execute(query, (g.user['id'],)).fetchall()
    
    # 将结果按计划ID分组，整理成模板易使用的结构
    routines = {}
    for row in results:
        rid = row['routine_id']
        if rid not in routines:
            routines[rid] = {
                'name': row['routine_name'],
                'created': row['created'],
                'exercises': []
            }
        routines[rid]['exercises'].append({
            'name': row['name'],
            'weight': row['weight'],
            'sets': row['sets'],
            'reps': row['reps']
        })
    
    # 转成列表传给模板
    return render_template("routine/index.html", routines=list(routines.values()))

2. 简化模板代码，直接遍历分组后的数据

后端已经完成分组，模板无需再做过滤判断：

{% for r in routines %}     
    <h5>{{ r['name'] }} ({{ r['created'] }})</h5>
    <ul>
        {% for e in r['exercises'] %}
            <li>{{ e['name'] }} {{ e['weight'] }}kg {{ e['sets'] }}x{{ e['reps'] }}</li>
        {% endfor %}
    </ul>
    {% if not loop.last %}
        <br>
    {% endif %}
{% endfor %}

问题根源说明

• 原模板中n['routine_id']实际是routine_exercise表的主键id，但第二个查询返回的e['id']是exercise表的主键，两者完全不匹配，导致过滤逻辑失效。
• 原SQL查询未正确关联计划和动作的绑定关系，动作数据没有携带所属计划的ID，自然无法在模板中匹配分类。

内容的提问来源于stack exchange，提问作者hunchak