# HG changeset patch
# User Brian Neal <bgneal@gmail.com>
# Date 1301530474 0
# Node ID a404f93a68d3cb3278b235e71a87a96c86fae5c0
# Parent  c185fcf5817fa53d85e0683a5676f15499960c8d
Decrease by 2 orders of magnitude the number of queries needed to generate the gigs page for the Madeira.

diff -r c185fcf5817f -r a404f93a68d3 mysite/band/views.py
--- a/mysite/band/views.py	Wed Mar 30 01:19:40 2011 +0000
+++ b/mysite/band/views.py	Thu Mar 31 00:14:34 2011 +0000
@@ -1,8 +1,12 @@
 #######################################################################
 #
-# PyBand Copyright (C) 2008 by Brian Neal
+# PyBand Copyright (C) 2008 - 2011 by Brian Neal
 #
 #######################################################################
+import collections
+import datetime
+import random
+
 from django import forms
 from django.core.urlresolvers import reverse
 from django.http import HttpResponse
@@ -12,9 +16,11 @@
 from django.template import RequestContext
 from django.template.loader import render_to_string
 from django.core.mail import send_mail
-from django.db.models import Q
+from django.db import connection
+
 from mysite.band.models import Article
 from mysite.band.models import Album
+from mysite.band.models import Band
 from mysite.band.models import Fan
 from mysite.band.models import Gear
 from mysite.band.models import Gig
@@ -27,8 +33,6 @@
 from mysite.band.models import Video_Set
 from mysite.photologue.models import Gallery
 from mysite.photologue.models import Photo
-import datetime
-import random
 
 #######################################################################
 
@@ -40,11 +44,6 @@
 
    upcomingDates = Gig.objects.filter(date__gte = datetime.date.today).order_by('date')[:5]
 
-   #tourPhotos = Photo.objects.filter(
-   #   Q(slug = 'flyer-aug-1-2008-san-diego') |
-   #   Q(slug = 'flyer-aug-2-2008-hermosa-beach') 
-   #   ).order_by('id')
-
    return render_to_response('band/index.html',
          { 
            'config' : config, 
@@ -68,47 +67,64 @@
 #######################################################################
 
 def gigs(request):
-   upcoming = Gig.objects.select_related().filter(date__gte = datetime.date.today).order_by('date')
-   previous = Gig.objects.select_related().filter(date__lt = datetime.date.today)
-   #upcoming = Gig.objects.filter(date__gte = datetime.date.today).order_by('date')
-   #previous = Gig.objects.filter(date__lt = datetime.date.today)
+   today = datetime.date.today()
+   gigs = Gig.objects.select_related('venue', 'flyer', 'venue__city',
+           'venue__city__state', 'venue__city__country')
+   upcoming = []
+   previous = []
+
+   # To avoid many, many database hits in the template, we get all the
+   # bands out at once. We also get the many-to-many intermediate table
+   # that Django generated for us so we can associate bands to gigs.
+   # Since we don't know about this table we drop into raw SQL to get
+   # the contents.
+
+   bands = dict((band.id, band) for band in Band.objects.all())
+   cursor = connection.cursor()
+   cursor.execute('SELECT * FROM band_gig_bands')
+   gig_bands = collections.defaultdict(list)
+   for row in cursor.fetchall():
+      gig_bands[row[1]].append(bands[row[2]])
+
+   for gig in gigs:
+      gig.bands_ = gig_bands[gig.id]
+      if gig.date >= today:
+         upcoming.append(gig)
+      else:
+         previous.append(gig)
+
+   upcoming.reverse()
 
    stats = {}
-   venues = set([])
-   cities = set([])
-   states = set([])
-   countries = set([])
-   bands = set([])
+   venues = set()
+   cities = set()
+   states = set()
+   countries = set()
    for gig in previous:
-      if gig.venue.id not in venues:
-         venues.add(gig.venue.id)
-      if gig.venue.city.id not in cities:
-         cities.add(gig.venue.city.id)
-      if gig.venue.city.state and gig.venue.city.state.id not in states:
+      venues.add(gig.venue.id)
+      cities.add(gig.venue.city.id)
+      if gig.venue.city.state:
          states.add(gig.venue.city.state.id)
-      if gig.venue.city.country and gig.venue.city.country.id not in countries:
-          countries.add(gig.venue.city.country.id)
-      for band in gig.bands.all():
-         if band.id not in bands:
-            bands.add(band.id)
+      if gig.venue.city.country:
+         countries.add(gig.venue.city.country.id)
 
-   stats['count'] = previous.count()
+   stats['count'] = len(previous)
    stats['venues'] = len(venues)
    stats['cities'] = len(cities)
    stats['states'] = len(states)
    stats['countries'] = len(countries)
    stats['bands'] = len(bands)
 
-   flyerGigs = Gig.objects.exclude(flyer__isnull = True).order_by('-date')
+   flyerGigs = Gig.objects.exclude(flyer__isnull = True).select_related(
+       'venue', 'flyer').order_by('-date')
 
-   return render_to_response('band/gigs.html',
-         { 
-           'upcoming' : upcoming,
-           'previous' : previous,
-           'stats' : stats,
-           'flyerGigs' : flyerGigs,
-         },
-         context_instance = RequestContext(request))
+   return render_to_response('band/gigs.html', {
+       'upcoming' : upcoming,
+       'previous' : previous,
+       'stats' : stats,
+       'flyerGigs' : flyerGigs,
+       },
+       context_instance = RequestContext(request))
 
 #######################################################################
 
diff -r c185fcf5817f -r a404f93a68d3 mysite/templates/band/gigs.html
--- a/mysite/templates/band/gigs.html	Wed Mar 30 01:19:40 2011 +0000
+++ b/mysite/templates/band/gigs.html	Thu Mar 31 00:14:34 2011 +0000
@@ -61,9 +61,9 @@
          {% endif %}
       {% endif %}
 
-      {% if show.bands.all %}
+      {% if show.bands_ %}
          With:
-         {% for band in show.bands.all %}
+         {% for band in show.bands_ %}
             {% if band.url %}
                <a href="{{ band.url }}" target="_blank">{{ band.name }}</a>
             {% else %}
@@ -134,7 +134,7 @@
             {% endifnotequal %}
          </td>
          <td width="40%">
-            {% for band in show.bands.all %}
+            {% for band in show.bands_ %}
                {% if band.url %}
                   <a href="{{ band.url }}" target="_blank">{{ band.name }}</a>
                {% else %}