CODEWITHSUNDEEP
pythonsqlalchemy
Corey
Corey

Reputation: 826

How would I query using sqlalchemy to retrieve all objects from an adjacency list?

class Node(Base):
    __tablename__ = 'node'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('node.id'))
    data = Column(String(50))
    children = relationship("Node",
                backref=backref('parent', remote_side=[id])
            )

When using this type of model, how could I query for all linked objects of a parent?

In other words, I need a list that contains all the children and children of children, and children of children of children...

Upvotes: 3

Views: 388

Answers (2)

Corey
Corey

Reputation: 826

This is what I ended up doing, probably not the best, but it worked.

pks = []

def get_pk(o):
    pks.append(o.pk)
    for a in o.attachments:
        get_pk(a)

get_pk(parent)

Upvotes: 2

Jay Jodiwal
Jay Jodiwal

Reputation: 121

Query children on your node object directly. Check out lazy parameter in sqlalchemy documentation. That will determine, when exactly your list will be queried (with object itself or separately)

See below code for reference

parent = Node.query.filter_by(id=1).first()
children_list = n.children

P.S.: children_list will have immediate children only. To get grand-children and further, query again and again as needed, check lazy parameter for more information

Upvotes: 1

Related Questions