CODEWITHSUNDEEP
pythonsqldjangodjango-models
dev9
dev9

Reputation: 2382

How to add additional column to Django QuerySet

I have a QuerySet with Books and I would like to add a score field to every Book result.

qs = Book.objects.all()

In raw SQL I would write:

SELECT
    *,
    (
        (SELECT COUNT(*) FROM votes WHERE value=1 AND book=b.id) - 
        (SELECT COUNT(*) FROM votes WHERE value=-1 AND book=b.id)
    ) AS score
FROM
    Book b;

How can I achieve it in Django? I tried annotate(), but it seems it's not meant for this kind of stuff.

Upvotes: 23

Views: 54665

Answers (2)

Joseph
Joseph

Reputation: 13178

Raw SQL is not the only way. You can use a Value() expression (see docs here):

from django.db.models import CharField, Value

MyModel.objects.all().annotate(mycolumn=Value('xxx', output_field=CharField()))

Upvotes: 80

ambi
ambi

Reputation: 1316

If votes possible values are only 1 and -1 you can just sum them using mentioned annotate: Book.objects.annotate(score=Sum('votes__value')).

If there is more possible values you can filter annotation by adding .filter(votes__value__range=(1,1)) to the above query.

If it's more complex you would have to use extra with select.

Upvotes: 6

Related Questions