CODEWITHSUNDEEP
pythonsqlalchemyflaskjinja2flask-sqlalchemy
Patrick Yan
Patrick Yan

Reputation: 2987

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

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.

Upvotes: 11

Views: 6571

Answers (3)

Eric Dong
Eric Dong

Reputation: 9

You must perform .first() or .all() to your query object to get the actual object/list. e.g
users = User.query.all()

Upvotes: -1

Sean Vieira
Sean Vieira

Reputation: 159955

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')

Upvotes: 9

DazWorrall
DazWorrall

Reputation: 14210

It is a query object yes, but those query objects have methods which might help you. Specifically for this use case, you can do:

{% for user in users %}
    <tr>
        <td>{{ user.id }}</td>
        <td>{{ user.posts.count() }}</td>
    </tr>
{% endfor %}

Upvotes: 0

Related Questions