Python Sqlalchemy access many to many data in view

Question

I have a many to many relation. Inserting data is no problem, but I dont know how to correctly get the data in jinja2 / view. I have a working approach but it feels like a workaround.

class Post(Base):
    __tablename__ = 'posts'
    id = Column(Integer, primary_key=True)
    title = Column(Text, nullable=False)
    description = Column(Text, nullable=True)
    children_PostBelongsCategory = relationship("PostBelongsCategory")
    # etc.

class Category(Base):
    __tablename__ = 'categories'
    category_name = Column(Text, primary_key=True)
    # etc.

Many to Many relation of Post and Category:

class PostBelongsCategory(Base):
    __tablename__ = 'postbelongstocategories'
    post_id = Column(Integer, ForeignKey('posts.id'), primary_key=True)
    category_id = Column(Text, ForeignKey('categories.category_name'), primary_key=True)  
    child = relationship("Category")

The way I am doing it right now is query all Post items, query all PostBelongsCategory items and then make two nested for loops with an if loop comparing the id's:

{% for post in posts %}
<div class="row">
    <div class="col-xs-12">
        <div class="post-item">
            <h1> {{ post.title }} </h1>
            <p> {{ post.description }} </p>
            {% for cat in categories %}
                {% if cat.post_id == post.id %}
                    <p> {{ cat.category_id }} </p>
                {% endif %}
            {% endfor %}
        </div>
    </div>
</div>
{% endfor %}

I think my approach could lead to performance issues if the tables are very large (but I am not sure here, maybe someone can help me here). Also as mentioned before my approach feels like a workaround. Is there a better way to get the data from many to many?

What I tried so far is using lazyload:

Post.query.options(lazyload('children_PostBelongsCategory')).order_by(desc("created_on")).all()

This gave no errors, but also nothing new. I am currently reading about join/load options but I am not sure whether I am on the correct path.

Answer 1

Use the defined ORM relationship for accessing related objects:

{% for post in posts %}
    ...
            {% for assoc in post.children_PostBelongsCategory %}
                <!-- do something with assoc.child here -->
            {% endfor %}
    ...
{% endfor %}

Relationships are by default lazy loading, so there's no need to pass that option in your case. Your original approach is a bit like a manual nested loop join, but performed in Python, and might have inferior performance, if you have a lot of categories.

In practice lazy loading means that each relationship attribute access will issue a new SELECT in order to fetch the related objects. If you have many posts, this can become an issue. A solution is to eager load related objects in the same query that fetches the posts. A good primer to that is joined eager loading:

# Note the chaining in the passed options
Post.query.\
    options(joinedload('children_PostBelongsCategory')
            .joinedload('child')).\
    order_by(Post.created_on.desc()).\
    all()