Annotate in SQLite

Question

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?

Krzysztof Szularz · Accepted Answer · 2012-12-11 09:25:04Z

5

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."

edited Dec 11, 2012 at 9:25

Krzysztof Szularz

5,26926 silver badges35 bronze badges

answered Dec 11, 2012 at 8:18

dani herrera

52k11 gold badges133 silver badges188 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Krzysztof Szularz Over a year ago

Great answer, but where did you get this wrapping from?

dani herrera Over a year ago

@KrzysiekSzularz, are you talking about code syle formatting? Yea! sorry, is for avoid scroll bars. Please, can you rewraping it better? Thanks a lot!!

Krzysztof Szularz Over a year ago

Put whole command in brackets and wrap after inner ). Do you like it better that way?

dani herrera Over a year ago

Better now!!! Thanks! My style is close to: PEP8 python.org/dev/peps/pep-0008/#maximum-line-length ... in the broad sense of the 'close' word ;) (sorry just a joke)

Collectives™ on Stack Overflow

Annotate in SQLite

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related