How do you join two tables using Django without using raw sql?

Is is possible to programatically join two tables using Django's ORM? I have two models: Topics and Votes. On my template I have a list of topics that users can up/down vote like Reddit. Everything works as it should except for sorting the results. I cannot figure out how to sort an objects list based off the score which is a sum of each object's vote count. I can retrieve the desired data from postgres without any issue:

select, i.title, i.date_created, s.object_id,, Sum(vote) 
from topic_topic i, votes s 
where = s.object_id 
group by 1, 2, 3, 4, 5 
order by sum DESC;

It returns the desired results:

id | title  |         date_created          | object_id | vote | sum 

11 | sdfg   | 2012-06-04 23:30:17.805671-07 |        11 |    1 |   2

 1 | test   | 2012-05-13 17:03:24.206092-07 |         1 |    1 |   2

 3 | asdf   | 2012-05-13 19:23:15.059135-07 |         3 |    1 |   2

 2 | adsf   | 2012-05-13 19:21:34.180905-07 |         2 |    1 |   2

12 | 11     | 2012-06-04 23:30:54.759158-07 |        12 |    1 |   2

 9 | asfd   | 2012-05-24 00:26:26.705843-07 |         9 |   -1 |  -1

 4 | asdf   | 2012-05-14 19:59:52.450693-07 |         4 |   -1 |  -2

The problem is, I am not sure how to retrieve this as a queryset. At the moment I am using the following to display objects:

topic_list = Topic.objects.all()

Everything displays as I would like it to, except for the sort order. I would like the highest score to display first.

Resources I have already looked at: How to query as GROUP BY in django?

And many more, but as a new user, anti-spam prevents me from adding them.

Thing's I've tried:


listed_links = list(chain(topic, score))

Unfortunately, if I tried to add a sorted value this broke.

Combining object lists:

topic = Topic.objects.all().values_list('user','id', 'title','slug', 'date_created', 'date_updated',)

score = Vote.objects.values('object_id').annotate(total=Sum('vote')).order_by('-total')

results = []

for topic in topic:

for score in score:

This resulted in all the objects I wanted in one list, but I could not figure out how to link to score.object_id.

I've also tried inserting raw SQL, but I don't feel like I am doing it correctly, and could lead to SQL injection by a third party.

I would love to share the results of this back to the django-voting project. Like I said, everything works as it should, except I cannot figure out how to sort by score desc.


from django.contrib.contenttypes import generic

from django.contrib.contenttypes.models import ContentType

from django.contrib.auth.models import User

from django.db import models

from voting.managers import VoteManager

from voting.VotedObjectsManager import VotedObjectsManager

    SCORES = (
    (+1, u'+1'),
    (-1, u'-1'),

class Vote(models.Model):

    A vote on an object by a User.

    user         = models.ForeignKey(User)

    content_type = models.ForeignKey(ContentType)

    object_id    = models.PositiveIntegerField()

    object       = generic.GenericForeignKey('content_type', 'object_id')

    vote         = models.SmallIntegerField(choices=SCORES)

    objects      = VoteManager()

    class Meta:
        db_table = 'votes'
        # One vote per user per object
        unique_together = (('user', 'content_type', 'object_id'),)

    def __unicode__(self):
        return u'%s: %s on %s' % (self.user,, self.object)

    def is_upvote(self):
        return == 1

    def is_downvote(self):
        return == -1

=============Topic Model========================

from django.db import models

from datetime import datetime

from tinymce import models as tinymce_models

from django.forms import ModelForm

from django.template.defaultfilters import slugify

from tagging.fields import TagField

from tagging.models import Tag

from django.contrib.auth.models import User

from django.utils.translation import ugettext_lazy as _

from django.contrib.contenttypes.models import ContentType

from django.contrib.contenttypes import generic

from django.core import urlresolvers

    class Topic(models.Model):

    title           = models.CharField(max_length=50)

    slug            = models.SlugField(max_length=50, editable=False)

    topic         = tinymce_models.HTMLField()

    date_created    = models.DateTimeField(editable=False)

    date_updated    = models.DateTimeField(editable=False)

    tags            = TagField()

    def set_tags(self, tags):
        Tag.objects.update_tags(self, tags)    

    def __unicode__(self):
        return self.tags

    def __unicode__(self):

    def __unicode__(self):
        return self.title


I was able to figure out the solution using a patch as described here:

The difference was that I extracted the following lines:

    def select_score(self):
    """ Add vote scores for objects in resoultset """
    from django.contrib.contenttypes.models import ContentType
    model_type = ContentType.objects.get_for_model(self.model)
    table_name = self.model._meta.db_table
    print type(model_type)
    return self.extra(select={'score': 'SELECT SUM(vote) FROM votes WHERE content_type_id=%i AND' % (int(, table_name)})

And added them to the voting/ file as so:

class VoteManager(models.Manager):
def get_score(self, obj):
    Get a dictionary containing the total score for ``obj`` and
    the number of votes it's received.
    ctype = ContentType.objects.get_for_model(obj)
    result = self.filter(object_id=obj._get_pk_val(),
            'score': 'COALESCE(SUM(vote), 0)',
            'num_votes': 'COALESCE(COUNT(vote), 0)',
    }).values_list('score', 'num_votes')[0]

    return {
        'score': int(result[0]),
        'num_votes': int(result[1]),

Then In my I added the following:

from voting.managers import VoteManager
def index(request):
queryset = Topic.objects.select_score().order_by('-score')
paginator = Paginator(queryset, 3) # Show 25 contacts per page

page = request.GET.get('page')
    topic_list =
except PageNotAnInteger:
    # If page is not an integer, deliver first page.
    topic_list =
except EmptyPage:
    #If page is out of range (e.g. 9999), deliver last page of results.
    topic_list =

c = Context({
'topic_list': topic_list,
'request': request
return render_to_response('idea/index.html', c, context_instance=RequestContext(request))

Lastly, in my index.html I added the following lines slightly deviating from the original example provided for users:

{% load voting_tags %}
{% votes_by_user user on topic_list as vote_dict %}
{% scores_for_objects topic_list as score_dict %}

<table id="voting_table" class="list">
  {% for link in topic_list %}
<td class="vote">

{% dict_entry_for_item link from vote_dict as vote %}
{% dict_entry_for_item link from score_dict as score %}

 <form class="linkvote" id="linkup{{ }}"{% if vote and vote.is_upvote %} action="{% url link_vote, direction="clear" %}"{% else %} action="{% url link_vote, direction="up" %}"{% endif %} method="POST">
    <input type="image" id="linkuparrow{{ }}" src="{{ STATIC_URL }}images/aup{% if vote and vote.is_upvote %}mod{% else %}grey{% endif %}.png">
    {% csrf_token %}
    <input type="hidden" name="next" value="{{ request.get_full_path }}"/>
    {% else %}


       <div id="link_score">{{ score.score|default:0 }}</div>

  <form class="linkvote" id="linkdown{{ }}" {% if vote and vote.is_downvote %} action="{% url link_vote, direction="clear" %}"{% else %} action="{% url link_vote, direction="down" %}"{% endif %} method="POST">
  {% csrf_token %}
    <input type="image" id="linkdownarrow{{ }}" src="{{ STATIC_URL }}images/adown{% if vote and vote.is_downvote %}mod{% else %}grey{% endif %}.png">
     <input type="hidden" name="next" value="{{ request.get_full_path }}"/>

<td class="item">
  <a id="link_title" href="{{ }}">{{ link.title|escape }}</a></h2>
  <p class="details">
    <span class="score" id="linkscore{{ }}"
          title="after {{ score.num_votes|default:0 }} vote{{ score.num_votes|default:0|pluralize }}">
    posted {{ link.date_created|timesince }} ago by
    <span class="user"><a href="../users/{{ }}/">{{ link.owner|escape }}</a></span>
{% get_comment_count for link as comment_count %}
    <span id="comment_score" class="comment_details"> {{ comment_count }} comment{{ comment_count|pluralize }}</span>
</tr>{% endfor %}
    <div id="paginator" class="pagination">
    <span class="step-links">
        {% if topic_list.has_previous %}
            <a href="?page={{ topic_list.previous_page_number }}">previous</a>
    {% endif %}
    {% if topic_list.has_next %}
        <a href="?page={{ topic_list.next_page_number }}">next</a>
    {% endif %}


I almost forgot! If you would like the list to sort in an order like 2,1,0,-1,-2 make sure to set the vote object value upon submitting whatever object you are creating. The example below is from my

def submit_topic(request):

if request.method == 'POST':
    post_topic = PosttopicForm(request.POST)
    owner = request.user
    if post_topic.is_valid():
        topic =
        topic.owner = request.user
        vote = Vote(vote='0', user = request.user, content_type_id=10,
        url = reverse('topic', args=[, topic.slug])
        return HttpResponseRedirect(url)
    post_topic = PosttopicForm()

c = Context({
    'form': post_topic,
    'user': request.user,
    'request': request,


return render_to_response('topic/submit.html', c, context_instance=RequestContext(request))

I really hopes this helps someone else. Sorry for not posting the solution sooner. Hopefully someone can improve this by getting rid of the SQL all together from the VoteManager, but I need to move forward.

You could try annotating the Topic queryset to contain the sum of the votes:

topic_list = Topic.objects.all().annotate(total=Sum('vote__vote')).order_by('-total')

Note: Without seeing your models, I'm not sure what to put in the Sum() function. It should be the child model name (which I assume is vote) followed by the field name on the model.

Need Your Help

imaplib python with ssl certificate

python ssl openssl ssl-certificate imaplib

I'm trying to use imaplib.IMAP4_SSL function but I'm stuck with this error:

Is there a generic method for cloning CLOS objects?

object clone common-lisp clos

I'm looking for a way to clone CLOS objects in a shallow manner, so the created object would be of the same type with the same values in each slot, but a new instance. The closest thing I found is a