django annotation and filtering

Question

Hi,

Hopefully this result set is explanatory enough:

title             text            total_score    already_voted
-------------     ------------    -----------    -------------
BP Oil spi...     Recently i...   5              0
J-Lo back ...     Celebrity ...   7              1
Don't Stop...     If there w...   9              0
Australian...     The electi...   2              1

My models file describes article (author, text, title) and vote (caster, date, score). I can get the first three columns just fine with the following:

articles = Article.objects.all().annotate(total_score=Sum('vote__score'))

but calculating the 4th column, which is a boolean value describing whether the current logged in user has placed any of the votes in column 3, is a bit beyond me at the moment! Hopefully there's something that doesn't require raw sql for this one.

Cheers, Dave

--Trindaz on Fedang #django

Answer 1

I cannot think of a way to include the boolean condition. Perhaps others can answer that better.

How about thinking a bit differently? If you don't mind executing two queries you can filter your articles based on whether the currently logged in user has voted on them or not. Something like this:

all_articles  = Article.objects.all()

articles_user_has_voted_on = all_articles.filter(vote__caster  = 
         request.user).annotate(total_score=Sum('vote__score'))

other_articles = all_articles.exclude(vote__caster  = 
         request.user).annotate(total_score=Sum('vote__score'))

Update

After some experiments I was able to figure out how to add a boolean condition for a column in the same model (Article in this case) but not for a column in another table (Vote.caster).

If Article had a caster column:

Article.objects.all().extra(select = {'already_voted': "caster_id = %s" % request.user.id})

In the present state this can be applied for the Vote model:

Vote.objects.all().extra(select = {'already_voted': "caster_id = %s" % request.user.id})