A more efficient Django (SQL) query

Question

Working on a Django project, I have a database of users and artworks, the latter of which can be liked. I would like to query, to get how many likes on all their pictures a singular user has together. I am able to write that over two for loops in views.py, but that is slow. I've also written it as a separate (terrible) SQL query, but I'm not certain how to then use it properly, since it's not a query-set anymore (I guess I'd have to query in a way where I get all the required data?).

The end idea is to simply have a table consisting of users, their emails, the number of likes their pictures have received, and the number of images posted. Here are the relevant models (I'm using the default Django auth_user table for users) and the SQL query.

class Arts(models.Model):
user_id = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE, unique=False)
title = models.CharField(max_length=100)
description = models.TextField(unique=False, null=False, blank=True)
timestamp = models.DateTimeField(default=timezone.now)
url = models.URLField()
likes = models.IntegerField(default=0)

Artwork liked by user:

class Like(models.Model):                                  
artwork = models.ForeignKey(Arts, on_delete=models.CASCADE)
user = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE)

my views.py for loop:

def all_users(request):
...
liked = defaultdict()
for user in users_all:
    liked[user.id] = 0
    for artwork in Arts.objects.filter(user_id=user.id):
        liked[user.id] += artwork.likes
...

SQL query

    with connection.cursor() as cursor:
    sql = """
WITH lajki AS 
(SELECT user_id_id, likes FROM artists_arts ORDER BY likes) 
SELECT user_id_id, SUM(likes) AS suma 
FROM lajki 
GROUP BY user_id_id 
ORDER BY suma DESC
"""
    cursor.execute(sql)
    def namedtuplefetchall(cursor):
        desc = cursor.description
        nt_result = namedtuple('Result', [col[0] for col in desc])
        return [nt_result(*row) for row in cursor.fetchall()]
    liked = namedtuplefetchall(cursor)

Is there a way to do this more efficiently?

willeM_ Van Onsem · Accepted Answer · 2020-08-28 22:20:46Z

1

There is no need to use raw queries at all. You can query with:

from django.contrib.auth import get_user_model

def all_users(request):
    users = get_user_model().objects.annotate(
        suma=Sum('arts__likes')
    ).order_by('-suma')
    # …

The user objects that arise from this queryset will have an extra attribute .suma that will sum the likes of the related Arts objects of that user. This will normally avoid the temporary table, and just make one query.

I'm however not sure if it is a good idea to store the likes in the Arts object. This is basically data duplication (you store an aggregated form of data in the object). It turns out that keeping data in sync, even on the same database is harder. It thus might be better to count the likes for the Arts objects by the Like object.

answered Aug 28, 2020 at 22:20

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Escapade Over a year ago

.annotate is exactly what I was looking for. I figured there was a good way to group the two models, but simply couldn't figure it out. Thank you very much. As for likes on arts, yeah, we noticed that when we were working on the forms, but by then we just left it as it was. Will try and fix it, though.

Collectives™ on Stack Overflow

A more efficient Django (SQL) query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related