Performing PostgreSQL LEFT OUTER JOINS and CASEs in Django.

Question

So I have a fairly involved sql query here.

SELECT links_link.id, links_link.created, links_link.url, links_link.title, links_category.title, SUM(links_vote.karma_delta) AS karma, SUM(CASE WHEN links_vote.user_id = 1 THEN links_vote.karma_delta ELSE 0 END) AS user_vote
FROM links_link
LEFT OUTER JOIN auth_user ON (links_link.user_id = auth_user.id)
LEFT OUTER JOIN links_category ON (links_link.category_id = links_category.id) 
LEFT OUTER JOIN links_vote ON (links_vote.link_id = links_link.id)
WHERE (links_link.id = links_vote.link_id)
GROUP BY links_link.id, links_link.created, links_link.url, links_link.title, links_category.title
ORDER BY links_link.created DESC
LIMIT 20

All my relations are good (I think) and this query works perfectly when I run it in my navicat for postgresql but turning it into something Django can use has been quite the challenge. I am using the pre-alpha 1.2 development verison (from the subversion repositories) so I have full range of tools from the docs.

Here are my models for grins:

class Category (models.Model):
    created = models.DateTimeField(auto_now_add = True)
    modified = models.DateTimeField(auto_now = True)
    title = models.CharField(max_length = 128)

    def __unicode__(self):
        return self.title

class Link (models.Model):
    category = models.ForeignKey(Category)
    user = models.ForeignKey(User)
    created = models.DateTimeField(auto_now_add = True)
    modified = models.DateTimeField(auto_now = True)
    fame = models.PositiveIntegerField(default = 1)
    url = models.URLField(max_length = 2048)
    title = models.CharField(max_length = 256)
    active = models.BooleanField(default = True)

    def __unicode__(self):
        return self.title

class Vote (models.Model):
    link = models.ForeignKey(Link)
    user = models.ForeignKey(User)
    created = models.DateTimeField(auto_now_add = True)
    modified = models.DateTimeField(auto_now = True)
    karma_delta = models.SmallIntegerField(default = 1)

    def __unicode__(self):
        return str(self.karma_delta)

How I am able to turn

def latest(request):
    links = Link.objects.all().order_by('-created')[:20]
    return render_to_response('links/list.html', {'links': links})

Into the above query?

I've only been able to make some progress using things like Aggregation but how to tackle my use of CASE is beyond me. Any help would be much appreciated. I always prefer to work in a framework's built in ORM but if raw SQL is necessary...

Answer 1

I don't have time at the moment to attempt a full translation of that query, but if the CASE is your main stumbling block, I can tell you right now it isn't supported natively, you'll need to use a call to .extra() with some raw SQL for that. Something like:

.extra(select={'user_vote': 'SUM(CASE WHEN links_vote.user_id = 1 THEN links_vote.karma_delta ELSE 0 END')})

But if this query works well as-is, why bother translating it into the ORM? Just grab a cursor and run it as a SQL query. Django's ORM is intentionally not a 100% solution, there's a reason it exposes the raw cursor API.