title: SQLAlchemy query parents with ALL children matching a filter
body: <p>I'm trying to create a sqlalchemy query (flask-sqlalchemy) that only returns parent objects if ALL children match the query instead of ANY. IE</p>
  <pre><code>(Parent
   .query
   .join(Child)
   .filter(Child.column == True)
   ).all()
  </code></pre>
  <p>Will return all of the parent objects that have any child with <code>column == True</code>, how do I write it so that all children must have <code>column == True</code>?</p>
  <h2>------------------------------- update ------------------------------</h2>
  <p>The above portion was abstracted from this actual code. Where</p>
  <p>Parent == Student and Child == StudentApplication</p>
  <p>The model heirarchy goes:</p>
  <p>User ---&gt; Students ---&gt; StudentApplication</p>
  <p>I'm trying to retrieve users, who have students, who's application has not been submitted, while ignoring users who have at least 1 student with a submitted application.</p>
  <pre><code>def _active_student_query(statement=None):
      import sqlalchemy as sa
      statement = statement or _active_user_query()
      statement = statement.join(Student).filter(Student.suspended == sa.false())
      return statement
  
  def users_without_submitted_applications(exclude_contacted=False):
      import sqlalchemy as sa
      from sqlalchemy.orm.util import AliasedClass
  
  # AppAlias = AliasedClass(StudentApplication)
  
  has_any_approved_app = sa.exists(
      sa.select([])
          .select_from(Student)
          .where((StudentApplication.student_id == Student.id) &amp;
                 (StudentApplication.flags.op('&amp;')(AppFlags.SUBMITTED) &gt; 0),
                 )
  )
  
  statement = _active_student_query()
  statement = (statement
               # .join(StudentApplication)
               # .filter(User.students.any())
               # has a student and no submitted applications
               .filter(~has_any_approved_app)
               # .filter(StudentApplication.flags.op('&amp;')(AppFlags.SUBMITTED) == 0)
               )
  if exclude_contacted:
      statement = (statement
                   .join(AlertPreferences)
                   .filter(AlertPreferences.marketing_flags.op('&amp;')(MarketingFlags.NO_APP_PING) == 0)
                   )
  
  from lib.logger import logger
  logger.info(statement.sql)
  return statement
  </code></pre>
  <p>And here's the SQL that it produces</p>
  <pre><code>SELECT users.is_active, users.flags, users.created_on, users.updated_on, users.id
  FROM users JOIN student ON users.id = student.user_id 
  WHERE users.is_active = true AND student.suspended = false AND NOT (EXISTS (SELECT  
  FROM student_application 
  WHERE student_application.student_id = student.id AND (student_application.flags &amp; %(flags_1)s) &gt; %(param_1)s))
  </code></pre>