I have a model that I created to store users comments, and because of the nature of comments sections in apps I tried to create my model it in a way such that comments can be under other comments, so I tried to use a unary relationship on the comments model
class Comments(db.Model):
id = db.Column(db.Integer, primary_key=True)
comment = db.Column(db.Text, nullable=False)
user_id = db.Column(db.Integer, db.ForeignKey("user.id"), nullable=False)
user = db.relationship("User", backref=db.backref("comment", lazy=True))
meme_id = db.Column(db.Integer, db.ForeignKey("memedata.meme_id"), nullable=False)
meme = db.relationship("MemeData", backref=db.backref("comment"), lazy=True)
parent_comment_id = db.Column(db.Integer, db.ForeignKey("comments.id"), nullable=True)
parent_comment = db.relationship("Comments")
where the parent_comment is a recursive relationship to the model and parent_comment_id is the id of the parent comment. My problem is it doesn't work how I expect it to work.
Basically if the comment has no parent comment (not under any comment) then I pass in Null but when I try to store a comment that has a parent comment (under another comment), it overwrites the parent_comment_id for the PARENT COMMENT to that of the child comment and leaves the CHILD COMMENT parent_comment_id as NULL
Example, we have an initial comment in the database that has no comment under it
id | comment | user_id | meme_id | parent_comment_id
1 | blah... | 1 | 3 | NULL
assuming I want to store a comment under this I would do something like
parent=Comments.query.get(1)
comment = Comments(
comment="another comment",
user=user,
meme=meme,
parent_comment=[parent]
)
Now what I would expect is...
id | comment | user_id | meme_id | parent_comment_id
1 | blah... | 1 | 3 | NULL
2 | another... | 5 | 3 | 1
...however what I get is
id | comment | user_id | meme_id | parent_comment_id
1 | blah... | 1 | 3 | 2
2 | another... | 5 | 3 | NULL
I would appreciate any comment or method on how I can get my desired result