python - SQLAlchemy - Filter query, exclude parent where one of many children meet criteria -

my sql skills pretty lacking, can't figure out how form query need.

i've got 2 db models 1 many relationship, defined this:

class parent(db.model):   __tablename__ = 'parent'    id = db.column(db.integer, primary_key = true)    children = db.relationship('child',                               backref = 'parent',                               lazy = 'joined')  class child(db.model):   __tablename__ = 'child'    id = db.column(db.integer, primary_key = true)   parent_id = db.column(db.integer, db.foreignkey('parent.id'))    value = db.column(db.string(140)) 

i'd able form query return parents meet 3 conditions:

1: have 1 or more children value contains 'value1'

2: have 1 or more children value contains 'value2'

3: have no children value contains 'value3' or 'value4'

for example data:

parents: id | 1  | 2  | 3  | 4  |  children: id | parent_id | value 1  | 1         | 'value1' 2  | 1         | 'value2' 3  | 1         | 'value3' 4  | 1         | 'value5'  5  | 2         | 'value1' 6  | 2         | 'value2' 7  | 2         | 'value4' 8  | 2         | 'value5'  9  | 3         | 'value1' 10 | 3         | 'value2' 11 | 3         | 'value5' 12 | 3         | 'value6'  13 | 4         | 'value1' 14 | 4         | 'value7' 

i'd want parent #3 returned.

this far i've gotten:

from sqlalchemy import not_, and_  conditions = []  conditions.append(parent.children.any(child.value.ilike('%'+value1+'%')) conditions.append(parent.children.any(child.value.ilike('%'+value2+'%'))  conditions.append(parent.children.any(not_(child.value.ilike('%'+value3+'%')))  condition = and_(*conditions)  q = db.session.query(parent).filter(condition) 

this first 2 conditions work fine. setting relationship lazy='join' allows me call .any() on relationship, , results i'm after.

condtion 3, however, isn't working is. it's returning parents have single child doesn't meet criteria, rather having children not meet criteria.

i've messed around outer joins , other ways of performing query, i've realized don't know enough sql figure out direction head. can point me in right direction? knowing sql need generate big step in right direction, getting working in sqlalchemy awesome.

query below should it:

q = (session.query(parent)      .filter(parent.children.any(child.value.ilike('%{}%'.format(value1))))      .filter(parent.children.any(child.value.ilike('%{}%'.format(value2))))      .filter(~parent.children.any(          db.or_(child.value.ilike('%{}%'.format(value3)),                 child.value.ilike('%{}%'.format(value4)),                 )      )) ) 

few points:

• you need or condition-3
• you should use not has children... (which done using ~) instead or not have inside.

filter condition-3 should be: parents not have children satisfy bla-bla, whereas code implies parents have @ least 1 child not satisfy bla-bla.