How to combine and match 2 tables in Flask

Question

The id is associated with the postID. How can I get the contact information and comment information of that id when I enter the id(/postComments/id)? I'm getting an internal error...

class posts(db.Model):
    __tablename__ = "posts"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100))
    postName = db.Column(db.String(100))
    postDescription = db.Column(db.String(500))
    postLike = db.Column(db.Integer)

class postComment(db.Model):
    __tablename__ = "postComment"
    id = db.Column(db.Integer, primary_key=True)
    postID = db.Column(db.Integer)
    senderName = db.Column(db.String(20))
    commentPost = db.Column(db.String(300))



@app.route('/postComments/<id>',methods=['GET'])
def get_comment_post(id):
    userList = posts.query\
    .join(postComment, posts.id == postComment.postID)\
    .add_columns(posts.id, posts.name, posts.postDescription, postComment.commentPost, postComment.senderName)\
    .filter(posts.id == id)

Yaakov Bressler Yaakov Bressler · Accepted Answer · 2020-12-22T04:11:40

Modify your models (and perform migrations) to allow reference for foreign keys:

class postComment(db.Model):
    __tablename__ = "postComment"
    id = db.Column(db.Integer, primary_key=True)
    postID = db.Column(db.Integer, db.ForeignKey("posts.id")) #  <--- Set to Foreign Key
    senderName = db.Column(db.String(20))
    commentPost = db.Column(db.String(300))

    # Establish relationship
    post = db.Relationship("posts", backref="postComment") #  <--- backref makes this relationship available in the other class


# This should get simpler...
@app.route('/postComments/<id>',methods=['GET'])
def get_comment_post(id):
    # Get the comment based on id
    my_comment = postComment.query.get(id)
    
    # Get the post associated with that comment
    my_post = my_comment.post

    # It looks like you're doing an intricate data transformation.
    # Do that here...
    # Also, for debugging, consider using the following print statements
    print(my_comment.__dict__)
    print(my_post.__dict__)

Consider reading this slightly more detailed explanation for establishing relationships.

How to combine and match 2 tables in Flask

1 Answers