1

I have these tables from one previous question i made: 3 base models and 2 secondary for many to many relationship.One parent can have more than one sons, more than one daughters and children can have more than one parent too.

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

 sons= db.relationship('Son',
                       secondary=parent_has_son,
                       back_populates='parents')

 daughters = db.relationship('Daughter',
                           secondary=parent_has_daughter,
                           back_populates='parents')


Class Son(db.Model):
 id=db.Column(db.Integer, nullable=False, autoincrement=True, primary_key=True)
 name = db.Column(db.String(45), nullable=False)
 age = db.Column(db.Integer)
 son_other_col = .Column(db.String(45))

 parents =  db.relationship('Parent',
                           secondary=parent_has_son,
                               back_populates='sons')


Class Daughter(db.Model):
     id=db.Column(db.Integer, nullable=False, autoincrement=True, primary_key=True)
     name = db.Column(db.String(45), nullable=False)
     age = db.Column(db.Integer)
     daughter_other_col1 = .Column(db.String(45))
     daughter_other_col2 = .Column(db.String(45))

     parents =  db.relationship('Parent',
                               secondary=parent_has_daughter,
                               back_populates='daughters')

parent_has_son = db.Table('parent_has_son ', db.metadata,
                                db.Column('parent_id', db.Integer, ForeignKey('Parent.id')),
                                db.Column('son_id', db.Integer, ForeignKey('Son.id')))

parent_has_daughter = db.Table('parent_has_daughter ', db.metadata,
                                    db.Column('parent_id', db.Integer, ForeignKey('Parent.id')),
                                    db.Column('daughter_id', db.Integer, ForeignKey('Daughter.id')))

To get a parent sons or daughters i can use joinedload:

parent = db.session.query(Parent).options(joinedload('sons')).filter(Parent.id == 3).first()

and then i can use :

sons = parent.sons

But i can't filter sons.For example if i want to take sons whose age is >15 and daughters whose age is >10 i can query like :

db.session.query(Parent).options(joinedload('sons')).options(joinedload('daughters')).filter(Paret.id == 3).filter(Son.age > 15).filter(Daughter.age > 10).first()

But the problem is that if there is not son with age > 15 or daughter with age >10 it will not return me any result.If i try to join with outerjoin it will return me all daughters and sons whose age is >15 and >10 even if they are not parent's sons and daughters.

So i need to query Parent with join Son and Daughter with condition(age).If there is not son or daughter i need to return the parent without children or with children who accept this condition.

I think my problem is not solved with the eager_loading. If i use contains_eager i can query:

parent = db.session.query(Parent).
join(Parent.sons).options(contains_eager(Parent.sons)).
filter(and_(Parent.id == 3, Son.age > 15)).first()

if there is not son with the age > 15 this query will return None which is something i don't want.

if i query like:

parent = db.session.query(Parent).
    join(Parent.sons).options(contains_eager(Parent.sons)).
    filter(Son.age > 15).all()

This query will return me all parents with sons whose age > 15, which i don't want.

I need as i say a query to return me a parent with sons or daughters if they accept the given condition.Correct me if i am wrong.

nick
  • 43
  • 8
  • Possible duplicate of [How to filter by joinloaded table in SqlAlchemy?](https://stackoverflow.com/questions/7793842/how-to-filter-by-joinloaded-table-in-sqlalchemy), https://stackoverflow.com/questions/47243397/sqlalchemy-joinedload-filter-column, https://stackoverflow.com/questions/18268960/sqlalchemy-filter-many-to-many-joinedload – Ilja Everilä Feb 11 '18 at 10:39
  • Relevant example from the docs: http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#explicit-join-eagerload – Ilja Everilä Feb 11 '18 at 10:44

0 Answers0