django annotate count filter

Question

I am trying to count daily records for some model, but I would like the count was made only for records with some fk field = xy so I get list with days where there was a new record created but some may return 0.

class SomeModel(models.Model):
    place = models.ForeignKey(Place)
    note = models.TextField()
    time_added = models.DateTimeField()

Say There's a Place with name="NewYork"

data = SomeModel.objects.extra({'created': "date(time_added)"}).values('created').annotate(placed_in_ny_count=Count('id'))

This works, but shows all records.. all places.

Tried with filtering, but it does not return days, where there was no record with place.name="NewYork". That's not what I need.

Can you show the code with filtering, show the output, and say what you think is wrong with it? — Gareth Rees
– Gareth Rees, Commented Feb 16, 2012 at 19:32
@GarethRees data = SomeModel.objects.extra({'created': "date(time_added)"}).values('created').annotate(placed_in_ny_count=Count('id')) len(data) gives 153. Adding .filter(place=Place.objects.get(name='NewYork')) returns less as it only returns days when SomeModel record with the 'place' was created. I want days without such records returned too. I would like to do something like ny_counts=Count('id' where place=Place.objects.get(name='NewYork')) and get [{'placed_in_ny_count': 23, 'created': datetime.date(2012,1,12)}, {'placed_in_ny_count': 0, 'created': datetimedate(2012,1,13)}...] — Robert
– Robert, Commented Feb 17, 2012 at 8:29

Gareth Rees · Accepted Answer · 2012-02-17 10:53:09Z

It looks as though you want to know, for each day on which any object was added, how many of the objects created on that day have a place whose name is New York. (Let me know if I've misunderstood.) In SQL that needs an outer join:

SELECT m.id, date(m.time_added) AS created, count(p.id) AS count
  FROM myapp_somemodel AS m
  LEFT OUTER JOIN myapp_place AS p
       ON m.place_id = p.id
       AND p.name = 'New York'
  GROUP BY created

So you can always express this in Django using a raw SQL query:

for o in SomeModel.objects.raw('SELECT ...'):   # query as above
    print 'On {0}, {1} objects were added in New York'.format(o.created, o.count)

Notes:

I haven't tried to work out if this is expressible in Django's query language; it may be, but as the developers say, the database API is "a shortcut but not necessarily an end-all-be-all.")
The m.id is superfluous in the SQL query, but Django requires that "the primary key ... must always be included in a raw query".
You probably don't want to write the literal 'New York' into your query, so pass a parameter instead: raw('SELECT ... AND p.name = %s ...', [placename]).

Collectives™ on Stack Overflow

django annotate count filter

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related