[Fixed]-How to annotate count filtered on datetime?

by

1👍

✅

I personally haven’t used RawSQL (still do things with .extra), but I think you don’t need to JOIN project_item in your RawSQL statement. Just try with: 

RawSQL("""SELECT COUNT(*) 
            FROM project_item 
            WHERE 
                project_item.cat_id = project_cat.id 
                AND project_item.end < NOW()
""")

And one more thing I think you should not use .values AFTER .annotate, but BEFORE the annotation. So your full QuerySet should look like this:

Cat.objects.values('title')\
    .annotate(
        item_count=Count('item'),
        future_item_count=RawSQL("""
            SELECT COUNT(*) 
            FROM project_item 
            WHERE 
                project_item.cat_id = project_cat.id
                AND project_item.end < NOW()
            """)
    )
👤Todor

Leave a comment