CODEWITHSUNDEEP
pythonsqlalchemyflaskflask-sqlalchemy
Elliott
Elliott

Reputation: 2619

SQLAlchemy select with all many to many relationship objects as list

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.

Upvotes: 7

Views: 9436

Answers (2)

Audrius Kažukauskas
Audrius Kažukauskas

Reputation: 13543

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.

Upvotes: 7

Simon
Simon

Reputation: 12488

SQLAlchemy auto-loads the post.tags property when you access it, so you only need to load all the posts you want.

db.Column('tag_id', db.Integer, db.ForeignKey('quotes.id'),)

Is that quotes.id a typo?

Upvotes: 0

Related Questions