CODEWITHSUNDEEP
pythonsqlsqlalchemy
dzz
dzz

Reputation: 329

SELECT conditions on relations with SQLAlchemy

Having this SQLAlchemy model:

class Post(db.Model):
    __tablename__ = 'posts'

    post_id = db.Column(db.Integer, primary_key=True)
    post_parent = db.Column(db.Integer, db.ForeignKey('posts.post_id'))

    children = db.relationship('Post')

As you can see, Posts can have children (i.e. Posts objects whose post_parent is not 0). For each Post I retrieve from the database, a SELECT is made to retrieve its children along with them. That's OK.

But, in my database design, Posts whose post_parent is not 0 can't have children (i.e. children from parent posts can't have any more children under them). But the SELECT to retrieve them is made. How can I avoid that?

Upvotes: 2

Views: 253

Answers (1)

zzzeek
zzzeek

Reputation: 75127

In relational terms, any Post here can have child Post objects, as all Post objects have a primary key so there can be any number of Post objects with that key for post_parent. So saying somepost.children has to emit SQL otherwise SQLA will not provide the correct results. if your model has some special rule some Post objects are known not to have children without querying the DB, you'd need to conditionally access "somepost.children". However your assertion that "post objects whose post_parent is not 0 can't have children" is not correct from a relational standpoint. This relationship is configured as a one-to-many, so any Post can have children which has nothing to do with its post_parent.

If on the other hand you really mean Post.children to be a singular, many-to-one to a particular Post, then you need to set remote_side=post_id on that relationship(), and you won't get the lazyload if parent_post is None in that case (though not zero, that is a non-NULL value).

Upvotes: 2

Related Questions