SQLAlchemy select with all many to many relationship objects as list

Question

Given this Many to Many relationship:

tagmap = db.Table('tagmap', db.Model.metadata,
    db.Column('post_id', db.Integer, db.ForeignKey('posts.id'),
    db.Column('tag_id', db.Integer, db.ForeignKey('tags.id'),)

class Post(db.Model):
    __tablename__ = 'posts'
    id    = db.Column(db.Integer, primary_key=True)
    text  = db.Column(db.Text)
    tags  = db.relationship('Tag', secondary=tagmap, backref='posts')

class Tag(db.Model):
    __tablename__ = 'tags'
    id    = db.Column(db.Integer, primary_key=True)
    name  = db.Column(db.String)

How would you construct a query to select all the Posts including an iterable list of associated Tag objects?

Desired query result structure:

[ <Post 1, "Blah",      [<Tag "Goober">, <Tag "Mexico">]>,
  <Post 2, "Daft Punk", [<Tag "French">]>,
  <Post 3, "Slurpee",   [<Tag "Diabetes">, <Tag "Tasty">, <Tag "Sugary">]>,
  <Post 4, "Lasers",    []> ]

Example desired result usage in a Jinja template:

{% for post in posts %}
    {{ post.text }}
    <hr>
    {% for tag in post.tags %}
        {{ tag.name }}, 
    {% endfor %}
{% endfor %}

Edit:

When trying to extend the query, the tags stop being included. See failed query below:

Post.query.join(User)\
    .options(db.joinedload(Post.tags))\
    .order_by(Post.create_date.desc()).limit(5)\
    .values(User.name.label('author'),
            Post.create_date,
            Post.text,)

Edit 2:

So this query works great:

posts = db.session.query(Post, User.name.label('author')).join(User)\
            .options(db.joinedload(Post.tags))\
            .order_by(Post.created.desc()).limit(5)

but if I want to be selective about the columns in Post I select:

# I only need the post text, nothing else
posts = db.session.query(Post.text, User.name.label('author')).join(User)\
            .options(db.joinedload(Post.tags))\
            .order_by(Post.created.desc()).limit(5)

It begins to fall apart quickly:

ArgumentError: Query has only expression-based entities - can't find property named 'tags'.

So I try to add the tags back:

# Yes, I know this is wrong
posts = db.session.query(Post.text, Post.tags, User.name.label('author'))\
            .join(User)\               
            .options(db.joinedload(Post.tags))\
            .order_by(Post.created.desc()).limit(5)

Which still fails.

So if you only want specific columns from the parent object (Post), where to you say in the expression, "I still want the tags"?

It's not really that important now, but I figured it would be useful to know. Or even know that it's not possible.

Answer 1

I assume that you want to use eager loading for relationships. You can achieve that either by setting lazy keyword argument in your relationship to 'joined' or 'subquery':

class Post(db.Model):
    # ...
    tags = db.relationship('Tag', secondary=tagmap, backref='posts',
                           lazy='joined')    # Or lazy='subquery'

Or you can set it per query:

q = Post.query.options(db.joinedload(Post.tags)).all()
# Or db.subqueryload(Post.tags)

Refer to Eager Loading part of ORM tutorial and Relationship Loading Techniques for more details.