3

I have a Django model that defines a TimeSlot. Each TimeSlot can hold a certain number of users (TimeSlot.spots). Each TimeSlot also has a certain number of users already held in it (a many to many field, TimeSlot.participants.

When I pass to the template that displays the available TimeSlots to the user, I annotate with TimeSlot.objects.annotate(Count('participants')),which gives the number of users currently held by the TimeSlot as participants__count.

However, what I really want is the number of spots remaining, the capacity (TimeSlot.spots) minus the number currently held (participants__count). How can I annotate another field with this new number, so I can pass it to the template?

stillinbeta
  • 1,977
  • 3
  • 17
  • 23
  • possible duplicate of [Subtracting two annotated columns](http://stackoverflow.com/questions/17374467/subtracting-two-annotated-columns) – Louis Oct 28 '14 at 10:38

2 Answers2

3

It's still not possible with annotation (though it is planned to implement in Django). But you can do it with an .extra() query. See my answer to another question for details.

Upd.:

Essentially, you need somethig like this query:

items = MyModel.objects.extra(
    select = {'variance': 'Model.someField - SUM(relatedModel__someField)'},
)
Community
  • 1
  • 1
Ivan Anishchuk
  • 487
  • 3
  • 16
1

Not possible with only an annotation. I'd create a method on the model which does the annotation, and then subtract that from your TimeSlot.spots value. This will use more database queries, but thats your only option. Or I guess you could drop down to raw SQL...

priestc
  • 33,060
  • 24
  • 83
  • 117
  • Thanks, I was worried that might be the case. I think that what I'll end up doing is set up the ManyToManyManager on the `TimeSlot.participants` so that whenever it adds a person it decrements `TimeSlot.spots,` which is a little less elegant but definetly cheaper in the long run. – stillinbeta Jul 01 '10 at 15:10