Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Sqlalchemy complex NOT IN another table query

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)

like image 713
jmrueda Avatar asked May 20 '17 09:05

jmrueda


People also ask

How do I find data not in another table in SQL?

How to Select All Records from One Table That Do Not Exist in Another Table in SQL? We can get the records in one table that doesn't exist in another table by using NOT IN or NOT EXISTS with the subqueries including the other table in the subqueries.

How do you know if data exists in one table and not in another?

How do you check if data in one table exists in another table Excel? You can use the MATCH() function to check if the values in column A also exist in column B. MATCH() returns the position of a cell in a row or column.

Is there something better than SQLAlchemy?

Django, Pandas, Entity Framework, peewee, and MySQL are the most popular alternatives and competitors to SQLAlchemy.

What is aliased in SQLAlchemy?

Implementing alias in SQLAlchemy SQL alias is a method of giving a temporary name for a table that is more convenient and readable. SQL alias facilitates a simple name to be used in place of a complex table name when it has to be used multiple times in a query.


1 Answers

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())
like image 132
Bartek Jablonski Avatar answered Sep 20 '22 17:09

Bartek Jablonski