CODEWITHSUNDEEP
djangodjango-modelsdjango-queryset
JacobF
JacobF

Reputation: 2485

Django query aggregate upvotes in backward relation

I have two models:

Base_Activity:
    some fields

User_Activity:
    user = models.ForeignKey(settings.AUTH_USER_MODEL)
    activity = models.ForeignKey(Base_Activity)
    rating = models.IntegerField(default=0) #Will be -1, 0, or 1

Now I want to query Base_Activity, and sort the items that have the most corresponding user activities with rating=1 on top. I want to do something like the query below, but the =1 part is obviously not working.

activities = Base_Activity.objects.all().annotate(
    up_votes = Count('user_activity__rating'=1),
).order_by(
    'up_votes'
)

How can I solve this?

Upvotes: 3

Views: 159

Answers (1)

janos
janos

Reputation: 124666

You cannot use Count like that, as the error message says:

SyntaxError: keyword can't be an expression

The argument of Count must be a simple string, like user_activity__rating.

I think a good alternative can be to use Avg and Count together:

activities = Base_Activity.objects.all().annotate(
    a=Avg('user_activity__rating'), c=Count('user_activity__rating')
).order_by(
    '-a', '-c'
)

The items with the most rating=1 activities should have the highest average, and among the users with the same average the ones with the most activities will be listed higher.

If you want to exclude items that have downvotes, make sure to add the appropriate filter or exclude operations after annotate, for example:

activities = Base_Activity.objects.all().annotate(
    a=Avg('user_activity__rating'), c=Count('user_activity__rating')
).filter(user_activity__rating__gt=0).order_by(
    '-a', '-c'
)

UPDATE

To get all the items, ordered by their upvotes, disregarding downvotes, I think the only way is to use raw queries, like this:

from django.db import connection

sql = '''
SELECT o.id, SUM(v.rating > 0) s
FROM user_activity o
JOIN rating v ON o.id = v.user_activity_id
GROUP BY o.id ORDER BY s DESC
'''
cursor = connection.cursor()
result = cursor.execute(sql_select)
rows = result.fetchall()

Note: instead of hard-coding the table names of your models, get the table names from the models, for example if your model is called Rating, then you can get its table name with Rating._meta.db_table.

I tested this query on an sqlite3 database, I'm not sure the SUM expression there works in all DBMS. Btw I had a perfect Django site to test, where I also use upvotes and downvotes. I use a very similar model for counting upvotes and downvotes, but I order them by the sum value, stackoverflow style. The site is open-source, if you're interested.

Upvotes: 1

Related Questions