CODEWITHSUNDEEP
sqlalchemyflask-sqlalchemy
Ican
Ican

Reputation: 129

Get sum of fields using filter

AllNews        Article 
 ------       -----------
|id    | --> | article_id|
 ------       -----------
| date |     | views     |
 ------       -----------
             | id        |
              -----------

All I want is to get sum of views for all articles between two dates for each date

articles = []
all_news = AllNews.query.filter(AllNews.date.between(date1, date2)).all()
for i in all_news:
    articles += Article.query.filter_by(article_id=i.id).all()

Dunno what to do after this. How to filter articles by date and get sum of views for each date?

Upvotes: 0

Views: 204

Answers (1)

Petr Blahos
Petr Blahos

Reputation: 2433

Firstly, think it out in SQL. That would be a simple join, like 

SELECT AllNews.id, SUM(Article.views) FROM AllNews, Article WHERE
AllNews.id = Article.article_id GROUP BY AllNews.id

right?

Now, I am not familiar with flask-sqlalchemy, but from what I gather from the docs you would do something like:

from yourapplication import db
q = db.session.query(AllNews.id, func.sum(Article.views)).filter(
    AllNews.id == Article.article_id,
).group_by(AllNews.id)

for (article_id, count) in q:
    pass

Upvotes: 1

Related Questions