CODEWITHSUNDEEP
djangosqlitedjango-models
szaman
szaman

Reputation: 6756

Annotate in SQLite

I have the following model:

class Model(...):
  date = DateField()
  user = ForeignKey()
  data = ForeignKey()
  time = IntegerField()

I'd like to make sum of time field for every user for a single data, so I do:

Model.objects.filter(date=..., data=...).values('user_id').annotate(time=Sum('time'))

but I receive result which looks like:

[{'user_id': 1, 'time': 20},{'user_id': 1, 'time': 10}, {'user_id': 2, 'time': 20}]

So the grouping does not work. I checked query generated by django and I don't know why django uses date and data for grouping as well, not only user. Am I doing something wrong or this is only SQLite issue?

Upvotes: 0

Views: 729

Answers (1)

dani herrera
dani herrera

Reputation: 51655

You should append .order_by() to your query set to clear default model ordering.

For your code:

(Model.objects.filter(date=…, data=…)
              .values('user_id')
              .annotate(time=Sum('time'))
              .order_by())                        # <---- Here

This is full explained in default ordering doc warning:

"Except that it won't quite work. The default ordering by name will also play a part in the grouping ... you should ... clearing any ordering in the query."

Upvotes: 5

Related Questions