CODEWITHSUNDEEP
pythonsqlsqlalchemy
user3481672
user3481672

Reputation: 45

How to retrieve data from tables with relationships - Many To Many (SQLAlchemy)?

I have two models with a many to many relationship (SQLAlchemy):

association_table = Table('association', Base.metadata,
    Column('left_id', Integer, ForeignKey('left.id')),
    Column('right_id', Integer, ForeignKey('right.id'))
)

class Parent(Base):
    __tablename__ = 'left'
    id = Column(Integer, primary_key=True)
    children = relationship("Child",
                    secondary="association",
                    backref="parents")

class Child(Base):
    __tablename__ = 'right'
    id = Column(Integer, primary_key=True)

Get "all parents of one (second on the list) the child" I can that way:

parents = session.query(Parent).filter(Parent.children.any(id=2))

And how to get "all the children of a parent"?

Upvotes: 2

Views: 3543

Answers (1)

van
van

Reputation: 76972

Any of the below should do:

# 1.
children = session.query(Child).filter(Child.parents.any(Parent.id==??))
# 2.
children = session.query(Child).join(Parent, Child.parents).filter(Parent.id == 99)
# 3.
my_parent = session.query(Parent).get(2)
children = session.query(Child).with_parent(my_parent).all()

Upvotes: 1

Related Questions