SQLAlchemy Many To Many relationship returning duplicate children

Question

I have 3 tables in sqlalchemy and I declare a many to many relationship

Parent:

class Parent(db.Model):
    __tablename__ = 'parent'
    id = db.Column(db.Integer, nullable=False, autoincrement=True, primary_key=True)
    name = db.Column(db.String(45))

    children = relationship('Child',
        secondary = parents_children,
        back_populates='parents')

Child

class Child(db.Model):
    __tablename__ = 'child'

    id = db.Column(db.Integer, nullable=False, autoincrement=True, primary_key=True)
    name = db.Column(db.String(45), nullable=False)

    parents = db.relationship('Parent',
        secondary=parents_children,
        back_populates='children')

and finally 1 table for relationship:

parents_children= db.Table('parents_children', db.metadata,
    db.Column('parent_id', db.Integer, ForeignKey('parent.id')),
    db.Column('student_id', db.Integer, ForeignKey('child.id')))

So, one child can have 1 or more parents and one parent can have 1 or more children.

Now let's say i want to take all children and return them as json file.I want to take child_id, child_name and parent_id so i make a query:

students = db.session.query(Child.id,Child.name,Parent.id).join('parents').all()

My json would be like:

{"children":[
{"name":"child1", "id":"1", "parent_id":"1"},
{"name":"child2", "id":"2", "parent_id":"2"}]}

My first problem is that if a child has more than one parent my json file will have the same child as many times as the parent id's.I want instead of this to have every child one time and in json, in parent_id field, i could have something like: "parent_id": ["1", "2"].

I can make a loop and before append a child, checking if this child exists and if exists then add the parent_id within the list in the field parent_id's. I can do that but my query will continue returning one row for the same child for each parent_id.My question is the following, can i take all children and for each children take all parents_id's?

Ilja Everilä Ilja Everilä · Accepted Answer · 2017-11-20T13:19:02

That's pretty much how SQL works. But: you can leverage SQLAlchemy's ORM relationships here and let SQLA handle the "deduplicating" for you:

students = db.session.query(Child).\
    options(joinedload("parents", innerjoin=True)).\
    all()

which will fetch the Child objects and prepopulate the parents relationship in the same query. To understand the difference between an explicit join and a joined load, read the zen of joined eager loading. The innerjoin=True is there since your original query performed one as well. Then you could simply:

students_json = {
    "children": [
        {
            "name": s.name,
            "id": s.id,
            "parent_id": [p.id for p in s.parents]
        }
        for s in students
    ]
}

If you're worried about the width of the returned rows, you can provide a suitable load_only() as options as well:

options(load_only("id", "name"),
        joinedload("parents", innerjoin=True).load_only("id"))

SQLAlchemy Many To Many relationship returning duplicate children

1 Answers