Django order by issue when used Q filter with multiple order by and distinct

Question

I am working on a photos project where a user can Download or Like a photo (do other operations as well) . I have two models to track this information. Below are the models used (Postgres the database used).

# Photo model stores photos
# download_count, like_count is stored in the same model as well for easier querying
class Photo(models.Model):
        name = models.CharField(max_length=100, null=True, blank=True)
        image = models.ForeignKey(Image, null=True, on_delete=models.CASCADE)
        download_count = models.IntegerField(default=0)
        like_count = models.IntegerField(default=0)
        views = GenericRelation(
            'Stat', related_name='photo_view',
            related_query_name='photo_view', null=True, blank=True)
        downloads = GenericRelation(
            'Stat', related_name='photo_download',
            related_query_name='photo_download', null=True, blank=True)
    
    
# Stat has generic relationship with photos. So that it can store any stats information
class Stat(models.Model):
        VIEW = 'V'
        DOWNLOAD = 'D'
        LIKE = 'L'
        STAT_TYPE = (
            (VIEW, 'View'),
            (DOWNLOAD, 'Download'),
            (LIKE, 'Like'),
        )
        user = models.ForeignKey(
            User, null=True, blank=True, on_delete=models.SET_NULL)
        content_type = models.ForeignKey(
            ContentType, on_delete=models.CASCADE, default=0)
        object_id = models.PositiveIntegerField()
        content_object = GenericForeignKey()
        stat_type = models.CharField(max_length=2, choices=STAT_TYPE, default=VIEW)
        created_at = models.DateTimeField(auto_now_add=True)
        updated_at = models.DateTimeField(auto_now=True)

My requirement is fetch the Photos that are popular this week. The popularity score should consider the likes count, download count.

I had written below query to get the popular photos this week which checks the likes or downloads created this week.

# week number
current_week = date.today().isocalendar()[1]

photos = Photo.objects.filter(Q(likes__created_at__week=current_week) | Q(downloads__created_at__week=current_week))\
            .order_by('id', 'download_count', 'like_count')\
            .distinct('id')

Problem: With the above query, the result set is always ordered by id even though other fields are mentioned.

Requirement: The photos should be ordered by sum of total likes and downloads so that they will be sorted by popularity.

Please suggest me a way to achieve this considering the database performance.

Thank You

Answer 1

Thanks to @biplove-lamichhane for suggesting me annotate function. I could able to achive the desired response by using the below query

photos = Photo.objects.filter(is_active=True)\
                .filter(Q(likes__created_at__week=current_week) | Q(downloads__created_at__week=current_week))\
                .annotate(score=F('download_count') + F('like_count'))\
                .order_by('-score')\
                .distinct()