joinedload one to many relationship causes duplicate parent result for each child

Question

Schemas

class Parent:
    relationship(ChildA)                  #One-to-Many
    relationship(ChildB, lazy="joined")   #One-to-Many
    relationship(ChildC, lazy="joined")   #One-to-Many


class ChildA:
    parent_id
    array_of_enums
    id

class ChildB:
    parent_id

class ChildC:
    parent_id

Goal

Query for Parent, ChildA pairs in which ChildA.array_of_enums contains a subset of enum values.

Query

session.query(Parent, ChildA.array_of_enums).filter(
    Parent.attr == specified_value,
    Parent.id == ChildA.parent_id,
    ChildA.array_of_enums.contains(enums)
    ChildA.attr == specified_value_2
).all()

Question

SQLAlchemy attaches joinedload options for ChildB & ChildC and results in returning (Parent, ChildA.array_of_enums) for each child of ChildB & Child C. As a result, I'm getting too many of Parent, ChildA pairs (extra for each of children B & children C).

Is there a way to build the query, such that all children B and children C come in with Parent all together without a separate SQL statement?

Also interestingly, querying for ChildA.id (or any other column) as opposed to ChildA.array_of_enums does not result in "duplicated" results.

Answer 1

I finaly found a different approch

I define my Parent/Child relationship like that in my Parent Class:

 child = relationship('Child', back_populates="parent")

I define my Child/Parent relationship like that in my Child Class:

parent = relationship("Parent", back_populates="child")

I use this query:

parents = session.query(Parent).outerjoin(Child,Parent.child).options(contains_eager(Parent.child)

The contains_eager is use to dig in parent to find associate child like:

for parent in parents:
   mychilds = parent.child

So I have all my Parents with or without child (as many they have) and Parent are not duplicate.

Answer 2

It's been a while, but I believe this worked for me. The latter 2 conditions in the join can probably be in the filter. I think something else I tried that got me a reasonable solution was separating the array contains check in a subquery and selecting from that subquery.

session.query(
    Parent,
    Child.array_of_enums
).join(
    Child, and_(
        Child.parent_id == Parent.id,
        Child.array_of_enums.contains(enums),
        ChildA.attr == specified_value_2
    )
).filter(
    Parent.attr == specified_value,
).all()