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:
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'))
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:
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)
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 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.
Django, Pandas, Entity Framework, peewee, and MySQL are the most popular alternatives and competitors to 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.
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())
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With