Flask_sqlalchemy many to many query-CodePudding

I have created the following models:

tag_post = db.Table('tag_post',
    db.Column('tag_id', db.Integer, db.ForeignKey('tag.id'), primary_key=True),
    db.Column('post_id', db.Integer, db.ForeignKey('post.id'), primary_key=True))

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(180))
    body = db.Column(db.Text, nullable=False)
    tags = db.relationship('Tag', secondary=tag_post, backref=db.backref('posts_associated', lazy="dynamic"))
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))

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

class User(UserMixin, db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64))
    username = db.Column(db.String(64), index=True, unique=True)
    email = db.Column(db.String(120), index=True, unique=True)
    password_hash = db.Column(db.String(128))
    posts = db.relationship('Post', backref='author', lazy='dynamic')

How can I query all the posts tagged with a tag, by using the tag.id?

Thanks in advance

CodePudding user response：

The solution is to use a join.

You want all posts that meet a condition. The query should therefore start with the table Post.

Post.query

Then you bind the table posts with your join table using the two columns that should fit. In this case the column post_id in the tag_post table and id in the Post table. Since you use your association table directly, the columns used are referenced using the name via the attribute c.

Post.query\
    .join(tag_post, tag_post.c.post_id == Post.id)

Then you filter the second column of your join table based on your condition.

tag_id = 1
Post.query\
    .join(tag_post, tag_post.c.post_id == Post.id)\
    .filter(tag_post.c.tag_id == tag_id)

Since you want all posts and not just one, close the request with all().

tag_id = 1
tagged_posts = Post.query\
    .join(tag_post, tag_post.c.post_id == Post.id)\
    .filter(tag_post.c.tag_id == tag_id)\
    .all()

The following is a detailed query with the same result, which also includes the third table.

tag_id = 1
tagged_posts = Post.query\
    .join(tag_post, tag_post.c.post_id == Post.id)\
    .join(Tag, tag_post.c.tag_id == Tag.id)\
    .filter(Tag.id == tag_id)\
    .all()