Sqlalchemy complex NOT IN another table query

Question

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 direct relationship between them, but they share two columns: storm_id and userid.

Basically, i would like to query all posts from storm_id, that are not from a banned user and some extra filters.

Here are the models:

Post

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'))

Banneduser

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'))

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:

• verified posts
• by descending order

• with a limit (i put it apart from the query as the elif has other filters)

  # 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
  

I got two problems, one basic and one underlying problem:

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

2/ Underlying problem: i am not sure if my approach is the correct one (the ddbb model structure/relationship plus the queries)

Answer 1

Use SQL EXISTS. Your query should be like this:

db.session.query(Post)\
  .filter(Post.storm_id==stormid)\
  .filter(Post.verified==True)\
  .filter(~ exists().where(Banneduser.storm_id==Post.storm_id))\
  .order_by(Post.timenow.desc())