title: Sqlalchemy complex NOT IN another table query
body: <p>First of all, i would like to apologize as my SQL knowledge level is still very low. Basically the problem is the following: I have two distinct tables, no <em>direct</em> relationship between them, but they share two columns: storm_id and userid. </p>
  
  <p>Basically, i would like to query all posts from storm_id, that are not from a banned user and some extra filters.</p>
  
  <p>Here are the models:</p>
  
  <h3>Post</h3>
  
  <pre><code>class Post(db.Model):
      id = db.Column(db.Integer, primary_key = True)
      ...
      userid = db.Column(db.String(100))
      ...
      storm_id = db.Column(db.Integer, db.ForeignKey('storm.id'))
  </code></pre>
  
  <h3>Banneduser</h3>
  
  <pre><code>class Banneduser(db.Model):
      id = db.Column(db.Integer, primary_key=True)
      sn = db.Column(db.String(60))
      userid = db.Column(db.String(100))
      name = db.Column(db.String(60))
      storm_id = db.Column(db.Integer, db.ForeignKey('storm.id'))
  </code></pre>
  
  <p>Both Post and Banneduser are another table (Storm) children. And here is the query i am trying to output. As you can see, i am trying to filter:</p>
  
  <ul>
  <li>verified posts</li>
  <li>by descending order</li>
  <li><p>with a limit (i put it apart from the query as the elif has other filters) </p>
  
  <pre><code># we query banned users id
  bannedusers = db.session.query(Banneduser.userid)
  
  # we do the query except the limit, as in the if..elif there are more filtering queries
  joined = db.session.query(Post, Banneduser)\
                  .filter(Post.storm_id==stormid)\
                  .filter(Post.verified==True)\
                   # here comes the trouble
                  .filter(~Post.userid.in_(bannedusers))\
                  .order_by(Post.timenow.desc())\
  
  try:
      if contentsettings.filterby == 'all':
          posts = joined.limit(contentsettings.maxposts)
          print((posts.all()))
          # i am not sure if this is pythonic
          posts = [item[0] for item in posts]
  
          return render_template("stream.html", storm=storm, wall=posts)
      elif ... other queries
  </code></pre></li>
  </ul>
  
  <p>I got two problems, one basic and one underlying problem:</p>
  
  <p>1/ .filter(~Post.userid.in_(bannedusers))\ gives one output EACH TIME post.userid is not in bannedusers, so i get N repeated posts. I try to filter this with distinct, but it does not work</p>
  
  <p>2/ Underlying problem: i am not sure if my approach is the correct one (the ddbb model structure/relationship plus the queries)</p>