Passing raw SQL to Django template

Question

I need to display a large amount of data that I don't want paginated because I'm using a jQuery tablesorter, and using Person.objects.all() in a view is very expensive for the database. Takes too long to load, so I'm trying to perform raw SQL in my view.

I tried using Django's generic views, but they were just as slow as the objects.all() method.

Here are my models. Essentially, I want to display all persons while counting how many times they have appeared in, say, var1 or var2.

class Person(models.Model):
    name = models.CharField(max_length=64, blank=True, null=True)
    last_name = models.CharField(max_length=64,)
    slug = models.SlugField()

class Object(models.Model):
    title = models.ForeignKey(Title)
    number = models.CharField(max_length=20)
    var1 = models.ManyToManyField(Person, related_name="var1_apps", blank=True, null=True)
    var2 = models.ManyToManyField(Person, related_name="var2_apps", blank=True, null=True)
    var3 = models.ManyToManyField(Person, related_name="var3_apps", blank=True, null=True)
    # ...   
    slug = models.SlugField()

from django.db import connection
    def test (request):
        cursor = connection.cursor()
        cursor.execute('SELECT * FROM objects_person')
        persons = cursor.fetchall() # fetchall() may not be the right call here?
        return render_to_response('test.html', {'persons':persons}, context_instance=RequestContext(request))

Template:

           <table class="table tablesorter">
               <thead>
                   <tr>
                   <th>Name</th>
                   <th>Var1</th>
                   <th>Var2</th>
                   <th>Var3</th>
                   </tr>
               </thead>
               <tbody>
                       {% for person in persons %}
                   <tr>
                       <td><a href="{{ person.get_absolute_url }}">{{  person.last_name }}{% if person.name %}, {{ person.name }}{% endif %}</a></td>
                       <td>{{ person.var1_apps.count }}</td>
                       <td>{{ person.var2_apps.count }}</td>
                       <td>{{ person.var3_apps.count }}</td>
                   </tr>
               {% endfor %}
               </tbody>
           </table>

What it does it iterate blank lines, but if I just call {{ creator }} it will show the entire SQL table -- which I do not want. I must be doing something wrong with the query, so any help appreciated.

Answer 1

The problem isn't the Person.objects.all(). When you loop through that queryset, you are doing three queries for every item in the queryset to calculate the counts.

The answer is to annotate your queryset with the counts for each field.

# in the view
persons = Person.objects.annotate(num_var1_apps=Count('var1_apps'),
                                  num_var2_apps=Count('var2_apps'),
                                  num_var3_apps=Count('var3_apps'),
                                  )

# in the template
{% for person in persons %}
               <tr>
                   <td><a href="{{ person.get_absolute_url }}">{{  person.last_name }}{% if person.name %}, {{ person.name }}{% endif %}</a></td>
                   <td>{{ person.num_var1_apps }}</td>
                   <td>{{ person.num_var2_apps }}</td>
                   <td>{{ person.num_var3_apps }}</td>
               </tr>
{% end for %}