flask: Get all items from parent in self referencing table: sqlalchemy.exc.ArgumentError: Expected mapped entity or selectable/table as join target

Question

I am trying to select all the child posts from the post id selected by the user. Below is my model definition for Posts and the query that gives the error in the title. I have tried a few things including aliasing which looked promising but this and several other options tried but all gave errors.

# Post model
class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100), nullable=False)
    date_posted = db.Column(db.DateTime(100), nullable=False, default=datetime.utcnow)
    content = db.Column(db.Text, nullable=False)
    topic = db.Column(db.String(100), nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    # thread structure using self-referencing see
    # https://docs.sqlalchemy.org/en/14/orm/self_referential.html
    parent_post = db.Column(db.Integer, db.ForeignKey('post.id'), nullable=True)
    child_posts = db.relationship('Post', backref='parent', lazy="joined", remote_side='Post.id') # background query

# display selected post thread route
@posts.route("/post_thread/<int:post_id>")
def post_thread(post_id):
    posts = Post.query.filter(Post.id==post_id).join(Post.id==Post.parent_post).all()
    # This works but to get only the selected post: posts = Post.query.where(Post.id==post_id)
    return render_template('post_thread.html', posts=posts, topic="Thread")

Answer 1

I have written a recursive function that works but this does not make use of the backref relationship so I guess is not the efficient way to do it.

# Recursive function to iterate down tree and return union of parent and children found
def union_children(post_id, posts):
    print("Looking for child posts")
    child_posts = Post.query.where(Post.parent_post==post_id)
    if child_posts:
        print("Child found")
        posts = posts.union(child_posts)
        for post in child_posts:
            posts = union_children(post.id, posts)
    return posts

@posts.route("/post_thread/<int:post_id>")
def post_thread(post_id):
    posts = Post.query.where(Post.id==post_id)
    posts = union_children(post_id, posts)
    return render_template('post_thread.html', posts=posts, topic="Thread")