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.