Django Nested Query with Aggregate.Sum()

mab.mo...@gmail.com

unread,

Sep 6, 2018, 11:42:36 AM9/6/18

to Django users

QUESTION

I have an application that will make on-line reservations for a series of weekly events. I would like to display the list of upcoming events in an html template with the number of remaining seats available in a single html page. I was able to create views to display the list of upcoming events and another view to display the number of remaining seats available but I am unable to figure out how to nest the two into a single view. Example below...

HTML OUTPUT

Event Title Week 1 - x amount of seats remaining for this event

Event Title Week 2 - x amount of seats remainign for this event

Event Title Week 3 - x amount of seats remaining for this event

and so on ....

MODELS

class Events(models.Model):

event_date = models.DateField(auto_now_add=False)

event_time = models.TimeField(auto_now_add=False)

event_type = models.CharField(max_length=20, choices=EVENT_TYPE)

seating = models.IntegerField(default=0)

title = models.CharField(max_length=200)

description = models.TextField()

menu = models.TextField()

price = models.DecimalField(max_digits=6, decimal_places=2)

publish = models.CharField(max_length=1, choices=PUBLISH_CHOICE)

def __int__(self):

return self.title

class Reservations(models.Model):

user_id = models.IntegerField(default=0)

event_id = models.IntegerField(default=0)

reservations = models.IntegerField(default=0)

def __int__(self):

return self.event

VIEWS

def events_view(request):

events=Events.objects.filter(publish='Y').filter(event_date__gte=datetime.now()).order_by('event_date') reservation_count = Reservations.objects.aggregate(Sum('reservations'))

return render(request, 'restaurant/events.html',{"events":events, "reservation_count":reservation_count, })

def make_reservation_view(request, pk):

event = Events.objects.get(id=pk)

seating_available = Events.objects.get(id=pk)

seating_available = seating_available.seating

reservation_count = Reservations.objects.filter(event_id=pk).aggregate(res_sum=Sum('reservations'))

res = reservation_count['res_sum']

seats_remaining = seating_available - res

return render(request, 'restaurant/make_reservation.html', {"event":event, \

"seats_remaining":seats_remaining,})

Simon Charette

unread,

Sep 6, 2018, 1:04:36 PM9/6/18

to Django users

Hello there,

You should be able to achieve what you're after by using annotations[0].

In your case you'll want to declare your Event and Reservation relationship explicitly by using

a ForeignKey

class Event(models.Model):

...

seating = models.PositiveIntegerField(default=0)

class Reservation(models.Model):

...

event = models.ForeignKey(Event, related_name='reservations')

seats = models.PositiveIntegerField(default=0)

And perform the following query

Event.objects.annotate(

seats_remaining=F('seating') - Sum('reservations__seats').

)

Cheers,

Simon

[0] https://docs.djangoproject.com/en/2.1/topics/db/aggregation/#generating-aggregates-for-each-item-in-a-queryset

mab.mo...@gmail.com

unread,

Sep 7, 2018, 8:38:05 AM9/7/18

to Django users

Thank you Simon.

I will take a look at annotations.

How would I represent seats remaining in the template for loop? Here is what I have so far.....

#####################

{% for i in events %}

<h3 style='display:inline;'>{{ i.event_date|date:"l M j, Y" }} {{ i.event_time|date:"g:i A" }}</h3><br>

<p style='display:inline;'>{{i.event_type|title}} - {{i.title|title}}</p><br>

<a style='display:inline;' href="{% url 'make_reservation' pk=i.pk %}">Purchase Reservation ${{i.price}}</a> <p style='display: inline;'>Seats Remaining ### VARIABLE HERE ###</p>

<p> {{ i.description|safe}} </p>

</div>

</div>

</div>

</div>

{% endfor %}

#####################

Simon Charette

unread,

Sep 7, 2018, 3:08:07 PM9/7/18

to Django users

The annotate(seats_remaining=...) will add a `seats_remaining` attribute to Event

instances returned from the queryset so you should be able to use {{i.seats_remaining }}

Cheers,

mab.mo...@gmail.com

unread,

Sep 10, 2018, 7:21:59 PM9/10/18

to Django users

Hello Simon,

I tried implementing your recommendations today and all appeared to go well except that I was not getting any results from the query for remaining seating on the html template. I took a look at my reservation table and the data was gone. I had the data in an original table I copied from and tried to do the same but now have the following error....

INSERT INTO restaurant_reservation (id, seats_reserved, event_id, user_id) SELECT winery_event_reservation_id, winery_event_adult_seats_reserved, event_id, user_id FROM winery_event_reservations;

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`campodibella`.`restaurant_reservation`, CONSTRAINT `restaurant_reservati_event_id_d13deeb7_fk_restauran` FOREIGN KEY (`event_id`) REFERENCES `restaurant_wineryevents` (`id`))

I went through all the migrate/makemigrations/migrate operations too.

Here is my FROM table structure...

mysql> describe winery_event_reservations;

+------------------------------------+------------------+------+-----+-------------------+-----------------------------+

+------------------------------------+------------------+------+-----+-------------------+-----------------------------+

| event_id | int(10) | YES | | NULL | |

| user_id | int(10) | YES | | NULL | |

| winery_event_adult_seats_reserved | int(10) | YES | | NULL | |

| winery_event_child_seats_reserved | int(10) | YES | | NULL | |

+------------------------------------+------------------+------+-----+-------------------+-----------------------------+

Copied TO table...

mysql> describe restaurant_reservation;

+----------------+------------------+------+-----+---------+----------------+

+----------------+------------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| user_id | int(11) | NO | | NULL | |

| event_id | int(11) | NO | MUL | NULL | |

+----------------+------------------+------+-----+---------+----------------+

4 rows in set (0.00 sec)

I did rename some fields and tables here is my current setup..

MODELS

class WineryEvents(models.Model):

event_date = models.DateField(auto_now_add=False)

event_time = models.TimeField(auto_now_add=False)

event_type = models.CharField(max_length=20, choices=EVENT_TYPE)

seating = models.PositiveIntegerField(default=0)

title = models.CharField(max_length=200)

description = models.TextField()

menu = models.TextField()

price = models.DecimalField(max_digits=6, decimal_places=2)

publish = models.CharField(max_length=1, choices=PUBLISH_CHOICE)

def __int__(self):

return self.title

class Reservation(models.Model):

user_id = models.IntegerField(default=0)

event = models.ForeignKey(WineryEvents, related_name='reservations', on_delete=models.PROTECT)

seats_reserved = models.PositiveIntegerField(default=0)

def __int__(self):

return self.event

VIEW

def winery_events_view(request):

winery_events = WineryEvents.objects.filter(publish='Y').filter(event_date__gte=datetime.now()).order_by('event_date')

WineryEvents.objects.annotate(

seats_remaining = F('seating')-Sum('reservations__seats')

)

return render(request, 'restaurant/winery_events.html',{"winery_events":winery_events, })

TEMPLATE

....

{% for i in winery_events %}

<h3 style='display:inline;'>{{ i.event_date|date:"l M j, Y" }} {{ i.event_time|date:"g:i A" }}</h3><br>

<p style='display:inline;'>{{i.event_type|title}} - {{i.title|title}}</p><br>

<a style='display:inline;' href="{% url 'make_reservation' pk=i.pk %}">Purchase Reservation ${{i.price}}</a> <p style='display: inline;'>Seats Remaining {{i.seats_remaining}}</p>

<p> {{ i.description|safe}} </p>

</div>

....

Any further help would be greatly appreciated. Thanks in advance.

Marc

mab.mo...@gmail.com

unread,

Sep 10, 2018, 9:37:21 PM9/10/18

to Django users

Ok, data transfer error fixed and data now in table. However, I am still not getting a value for {{i.seats_remaining}}. It comes out blank.

I had an error in the view that I corrected as below but still does not give results.

def winery_events_view(request):

winery_events = WineryEvents.objects.filter(publish='Y').filter(event_date__gte=datetime.now()).order_by('event_date')

WineryEvents.objects.annotate(

seats_remaining = F('seating')-Sum('reservations__seats_reserved')

)

return render(request, 'restaurant/winery_events.html',{"winery_events":winery_events, })

---------------------

mab.mo...@gmail.com

unread,

Sep 11, 2018, 3:55:00 PM9/11/18

to Django users

FINAL SOLUTION / SOLVED

A bit of a more complex single query...

VIEW

def winery_events_view(request):

events = WineryEvents.objects.filter(publish='Y').filter(event_date__gte=datetime.now()).order_by('event_date').annotate(seats_remaining = F('seating')-Sum('reservations__seats_reserved'))

return render(request, 'restaurant/winery_events.html',{"events":events, })

TEMPLATE

{% for i in events %}

<h3 style='display:inline;'>{{ i.event_date|date:"l M j, Y" }} {{ i.event_time|date:"g:i A" }}</h3><br>

<p style='display:inline;'>{{i.event_type|title}} - {{i.title|title}}</p><br>

<a style='display:inline;' href="{% url 'make_reservation' pk=i.pk %}">Purchase Reservation ${{i.price}}</a> <p style='display: inline;'>Seats Remaining {{i.seats_remaining}} </p>

<p> {{ i.description|safe}} </p>

</div>

{% endfor %}

--------------------------------------

Reply all

Reply to author

Forward