title: Flask how to query with a filter that checks whether a value is present in a list?
body: <p>I'm making a bug tracker structured such that each user can have multiple projects, and each project can record multiple bugs. I'd like users to be able to see how many new bugs were reported across all their projects since their last login.</p>
  <p>I structured it such that there's a Users model, a Projects model, and Bugs model. I've included the three models with their relevant columns below:</p>
  <pre><code>class Users(db.Model, UserMixin):
      id = db.Column(db.Integer, primary_key = True, nullable = False)
      # ...
      last_login = db.Column(db.DateTime, nullable = False)
      # AS PARENT
      owned_projects = db.relationship('Projects', backref=&quot;project_owner&quot;)
  
  class Projects(db.Model):
      id  = db.Column(db.Integer, primary_key = True, nullable = False)
      # ...
      # AS CHILD
      owner = db.Column(db.Integer, db.ForeignKey('users.id'))
      # AS PARENT
      bugs = db.relationship('Bugs', backref = 'containing_project')
  
  class Bugs(db.Model):
      id = db.Column(db.Integer, primary_key = True)
      # ...
      status = db.Column(db.String(20))
      report_date = db.Column(db.DateTime, index = True, default = dt.datetime.now())
      # AS CHILD
      project = db.Column(db.Integer, db.ForeignKey('projects.id'))
  </code></pre>
  <p>To filter a Bugs query for bugs reported since the user's last login, I can do a filter like <code>Bugs.query.filter(Bugs.report_date &gt; current_user.last_login)</code>. This shows ALL bugs from ALL users, but I'm having trouble constructing a query that filters it down to only bugs in projects owned by the user.</p>
  <p><code>.filter(Bugs.containing_project in current_user.owned_projects)</code> returns &quot;&lt;flask_sqlalchemy.BaseQuery object at 0x04E06988&gt;&quot;, but I have no idea how to work with that. I read about <a href="https://stackoverflow.com/questions/4926757/sqlalchemy-query-where-a-column-contains-a-substring/19654702#19654702">.contains</a> but that goes in the wrong direction, <code>current_user.owned_projects.contains(Bugs.project)</code> does not work.</p>
  <p>I also tried <code>.filter(Bugs.containing_project.owner == current_user.id)</code>, but got an error &quot;AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object associated with Bugs.containing_project has an attribute 'owner'&quot;.</p>
  <p>The only option I can think of now is to iterate through every single bug to find the ones that belong to a project owned by the user, but that would be nightmarish in terms of performance. Surely there's a <code>.in</code> method or something similar?</p>
  <p>Please advise on how I can achieve this, thanks in advance!</p>