How to get Flask-SQLAlchemy object to load relationship children for Jinja template?

Question

I have basic models for User and Post. In my User model, I have

posts = db.relationship('Post', backref='user', lazy='dynamic')

However, when I do something like

return render_template('user.html', users=users)

I want to do stuff like

{% for user in users %}
    <tr>
        <td>{{ user.id }}</td>
        <td>{{ user.posts|length }}</td>
    </tr>
{% endfor %}

Unfortunately, this doesn't work. Posts is a query, not an object b/c of lazy='dynamic'. I can do the above if I change lazy='joined', but then it would be loading all posts for users anytime I query for a User.

I tried adding .options(joinedload('posts')) to my query, but it said that

InvalidRequestError: 'User.posts' does not support object population - eager loading cannot be applied.

Any help is appreciated.

Answer 1

Simply remove the lazy="dynamic" argument and you will be able to use a joinedload without issues. (Remember, when you do this you open yourself up to hard-to-diagnose N+1 query issues. If you will always need the posts, use joined instead).

If you need all behaviors (queriable, joinable, and lazy-loaded), I suggest looking at the answer to this question, which suggests adding another attribute for dynamic queries:

class User(db.Model):
    posts = db.relationship('Post', backref='user')

class Post(db.Model):
    # etc.

User.posts_query = db.relationship(Post, lazy='dynamic')