CODEWITHSUNDEEP
pythonsqlalchemyflask-sqlalchemy
Anas Aldrees
Anas Aldrees

Reputation: 465

How to access child of child from parent in sqlalchemy?

Lets suppose i have a gift, each gift has a sub-category 'i.e perfumes' each sub-category is inside a category 'i.e female gifts' (in other words that will look like in the URL: "/female-gifts/perfumes/foo-gift" OR "male-gifts/perfumes/bar-gift")

class Category(db.Model):
    __tablename__ = 'category'

    id = db.Column(db.String(32), primary_key=True)
    name = db.Column(db.String(40), unique=True)
    sub_categories = db.relationship("SubCategory", backref="category")

class SubCategory(db.Model):
    __tablename__ = 'sub_category'

    id = db.Column(db.String(32), primary_key=True)
    name = db.Column(db.String(40), primary_key=True)
    category_id = db.Column(
        db.String(32), db.ForeignKey('category.id'), primary_key=True)

class Gift(db.Model):
    __tablename__ = 'gift'

    id = db.Column(db.String(5), primary_key=True)
    sub_category_id = db.Column(
        db.String(32), db.ForeignKey('sub_category.id'))
    sub_category = db.relationship(SubCategory, uselist=False, backref='gifts')
    category = # WHAT SHOULD I WRITE HERE???

I want to get all gifts in the category 'not the sub category' by these statements:

Gift.query.filter_by(category_id='1234').all()

Category.query.filter_by(id='1234').first().gifts

The above statements will return the same results

My question is: how to configure that relationships "inside the classes" to make the above statements work?

Thanks :)

Upvotes: 0

Views: 6237

Answers (1)

van
van

Reputation: 76992

Option-1: simple queries

Although you can do it quite easily, I would not recommend using this jump-relationship, because it is readonly, and you or your users might forget about this fact.

It is very easy to create these both as queries:

# parameters
category_id = '1234'

# query-1:
q = Gift.query.join(SubCategory).filter(SubCategory.category_id == category_id)

# query-2:
category = session.query(Category).get(category_id)
q = Gift.query.join(SubCategory).filter(SubCategory.category == category)

These will generate the same SQL which is most efficient. For the second case you can wrap this loading in a simple property:

class Category(Base):
    __tablename__ = 'category'

    @property
    def gifts(self):
        q = Gift.query.join(SubCategory).filter(SubCategory.category == self)
        return q.all()

and use it like:

Category.query.get('1234').gifts

Option-2: relationship

If you really want a relationship, the code below should do it:

class Gift(Base):
    # ...
    category = relationship(
        Category,
        uselist=False,
        secondary=SubCategory.__table__,
        primaryjoin=sub_category_id == SubCategory.id,
        secondaryjoin=SubCategory.category_id == Category.id,
        viewonly=True,
        backref="gifts",
    )

and usage:

# one side
gifts = Gift.query.filter(Gift.category == category).all()
# other side
gifts = Category.query.get(category_id).gifts

Upvotes: 2

Related Questions