django complex queryset annotation

I want to add to a queryset some statistic data calculated at each user's page request to display in a large table. The annotate method might be the best choice but I'm stuck at merging created querysets to a single one for easier manipulation in the template. The queryset type is preferred to sort data.

Following is the extremely simplified principle of my application. The template and models must not be touched because it's clearly the result I want. The data sorting by column is not implemented in this example.

Here are the models:

class Poll(models.Model):
    question = models.CharField(max_length=200, unique=True)

class Vote(models.Model):
    poll = models.ForeignKey(Poll)
    accept = models.BooleanField()
    comment = models.CharField(max_length=200, unique=True)
    censored = models.BooleanField()

Here's the view:

def summaryView(request):
    …
    contexte['poll_list'] = «insert code here»
    …
    return render_to_response('summary.html, contexte)

Here's the template:

<table>
  <thead>
    <tr>
      <th>
        Poll question
      </th>
      <th>
        Number of votes
      </th>
      <th>
        Number of uncensored "yes" votes
      </th>
      <th>
        Number of censored votes
      </th>
    </th>
  </thead>
  <tbody>
    {% for poll in poll_list %}
      <tr>
        <td>
          {{ poll.question }}
        </td>
        <td>
          {{ poll.numUncensoredYesVotes }}
        </td>
        <td>
          {{ poll.numCensoredVotes }}
        </td>
      </tr>
    {% endfor %}
  </tbody>
</table>

The difficulty is to create the number of uncensored "yes" votes annotation. The Count() aggregation function doesn't accept filters.

Answers


For this requirement I would add two more fields in the Poll models it will make faster sql queries. Normally in theses cases selection will be more frequent than insertion. So this will give more performance improvement to your project.

from django.db import models


class Poll(models.Model):
    question = models.CharField(max_length=200, unique=True)
    num_censored_votes = models.PositiveIntegerField(default=0)
    num_uncensored_yes_votes = models.PositiveIntegerField(default=0)


class Vote(models.Model):
    poll = models.ForeignKey(Poll)
    accept = models.BooleanField()
    comment = models.CharField(max_length=200, unique=True)
    censored = models.BooleanField()

    def save(self, force_insert=False, force_update=False, using=None, update_fields=None):
        models.Model.save(self, force_insert, force_update, using, update_fields)
        poll = self.poll

        if self.censored:
            poll.num_censored_votes += 1
            poll.save()

        elif self.accept:
            poll.num_uncensored_yes_votes += 1
            poll.save()

This can be also implemented using signals.


Need Your Help

What event fires when an element is added to the DOM and has been styled and loaded?

javascript html5 events dom dom-events

I am dynamically adding an element (created by document.createElement( "div" )) and I want to know when the element is fully visible, styled and added. I know there's the event DOMNodeInserted, but...

Migration From Datatable to Linq to Sql

asp.net linq-to-sql datatable

In past I use dynamic sql and datatable to get data from database.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.