SQLAlchemy Many to One Join

0
votes

I have a many to one relationship between two SQL tables using SQLAlchemy. For example:

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    child_id = Column(Integer, ForeignKey('child.id'))
    child = relationship("Child")

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    name = Column(String(100))

What I would like to be able to do is be able to add information from the Child class to the parent. I tried a join query:

result = session.query(Parent).join(Child).all()

While this query adds the appropriate Child object to the Parent object at parent.child it only returns the first parent for each child, i.e. I have four parents and two children in my database and this query only returns parents 1 and 3. How do I fix the query to return all four parents? The second I have is if I wanted to just add the child's name to the parent, not the entire child object, as parent.child_name how would I go about doing that?

1
pythonmysqlsqlalchemy
To get all of the entries for the many category of a many to one relationship, you need to use outerjoin: session.query(Parent).outerjoin(Child).all() Still not sure about the second question. - Michael

1 Answers

0
votes

How to get all parents when joining to children

The issue is that some parents do not have children, so using a normal join will exclude them. Use an outer join instead. Also, just adding a join won't actually load the children. You should specify contains_eager or joinedload to load the child with the parent.

# use contains_eager when you are joining and filtering on the relationship already
session.query(Parent).join(Parent.child).filter(Child.name == 'Max').options(contains_eager(Parent.child))

# use joinedload when you do not need to join and filter, but still want to load the relationship
session.query(Parent).options(joinedload(Parent.child))

How to add child_name to the parent

You want to use an association proxy.

from sqlalchemy.ext.associationproxy import association_proxy

class Parent(Base):
    child = relationship('Child')
    child_name = association_proxy('child', 'name')

# you can filter queries with proxies:
session.query(Parent).filter(Parent.child_name == 'Min')

There are some cool things you can do with association proxies, be sure to read the docs for more information.