I have two models in Flask-SQLAlchemy (Post and Comment) that have many-to-many relationship that is manifested in the third model (post_mentions):
post_mentions = db.Table(
'post_mentions',
db.Column('post_id', db.Integer, db.ForeignKey('posts.id'), primary_key=True),
db.Column('comment_id', db.Integer, db.ForeignKey('comments.id'), primary_key=True),
)
class Post(db.Model):
__tablename__ = 'posts'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String, unique=True, nullable=False)
mentions = db.relationship('Comment', secondary=post_mentions, lazy='dynamic')
def __eq__(self, other):
return self.name.lower() == other.name.lower()
def __hash__(self):
return hash(self.name.lower())
class Comment(db.Model):
__tablename__ = 'comments'
id = db.Column(db.Integer, primary_key=True)
text = db.Column(db.Text, nullable=False)
created_at = db.Column(db.Integer, nullable=False)
There is also a /posts endpoint that triggers the following query:
# flask and other imports
@app.route('/posts')
def posts():
page_num = request.args.get('page', 1)
posts = models.Post.query.join(models.post_mentions)\
.group_by(models.post_mentions.columns.post_id)\
.order_by(func.count(models.post_mentions.columns.post_id).desc())\
.paginate(page=int(page_num), per_page=25)
return render_template('posts.html', posts=posts)
There are more than 14k+ posts and 32k+ comments stored in SQLite database. As you can see from the snippet above, when someone hits /posts endpoint, SQLAlchemy loads all data at once to the memory and then subsequent queries (e.g. retrieving posts, comments to that posts, etc..) take sub-millisecond time, since data is being served from the memory without hitting the database. Initial load takes 10s+ on my laptop, which is, to put it mildly, suboptimal.
So the question is: Considering that users won't view 97+% of posts, how can I both order posts by number of mentions in comments and load them on demand instead of doing it in one swoop?
