CODEWITHSUNDEEP
sqldatabasedjangodjango-queryset
flo bee
flo bee

Reputation: 840

Django sql order by

I'm really struggling on this one. I need to be able to sort my user by the number of positive vote received on their comment.

I have a table userprofile, a table comment and a table likeComment. The table comment has a foreign key to its user creator and the table likeComment has a foreign key to the comment liked. To get the number of positive vote a user received I do : 

LikeComment.objects.filter(Q(type = 1), Q(comment__user=user)).count()

Now I want to be able to get all the users sorted by the ones that have the most positive votes. How do I do that ? I tried to use extra and JOIN but this didn't go anywhere.

Thank you

Upvotes: 1

Views: 313

Answers (4)

Igor
Igor

Reputation: 3179

I believe this can be achieved with Django's queryset:

User.objects.filter(comments__likes__type=1)\
    .annotate(lks=Count('comments__likes'))\
    .order_by('-lks')

The only problem here is that this query will miss users with 0 likes. Code from @gareth-rees, @timmy-omahony and @Catherine will include also 0-ranked users.

Upvotes: 0

Gareth Rees
Gareth Rees

Reputation: 65854

Timmy's suggestion to use a subquery is probably the simplest way to solve this kind of problem, but subqueries almost never perform as well as joins, so if you have a lot of users you may find that you need better performance.

So, re-using Timmy's models:

class User(models.Model):
     pass

class Comment(models.Model):
    user = models.ForeignKey(User, related_name="comments")

class Like(models.Model):
    comment = models.ForeignKey(Comment, related_name="likes")
    type = models.IntegerField()

the query you want looks like this in SQL:

SELECT app_user.id, COUNT(app_like.id) AS total_likes
FROM app_user
LEFT OUTER JOIN app_comment
             ON app_user.id = app_comment.user_id
LEFT OUTER JOIN app_like
             ON app_comment.id = app_like.comment_id AND app_like.type = 1
GROUP BY app_user.id
ORDER BY total_likes DESCENDING

(If your actual User model has more fields than just id, then you'll need to include them all in the SELECT and GROUP BY clauses.)

Django's object-relational mapping system doesn't provide a way to express this query. (As far as I know—and I'd be very happy to be told otherwise!—it only supports aggregation across one join, not across two joins as here.) But when the ORM isn't quite up to the job, you can always run a raw SQL query, like this:

sql = '''
    SELECT app_user.id, COUNT(app_like.id) AS total_likes
    # etc (as above)
'''
for user in User.objects.raw(sql):
    print user.id, user.total_likes

Upvotes: 0

Timmy O'Mahony
Timmy O'Mahony

Reputation: 53981

It sounds like you want to perform a filter on an annotation:

class User(models.Model):
     pass

class Comment(models.Model):
    user = models.ForeignKey(User, related_name="comments")

class Like(models.Model):
    comment = models.ForeignKey(Comment, related_name="likes")
    type = models.IntegerField()

users = User \
    .objects \
    .all()
    .extra(select = {
        "positive_likes" : """
        SELECT COUNT(*) FROM app_like
        JOIN app_comment on app_like.comment_id = app_comment.id
        WHERE app_comment.user_id = app_user.id AND app_like.type = 1 """})
    .order_by("positive_likes")

Upvotes: 2

catherine
catherine

Reputation: 22808

models.py

class UserProfile(models.Model):
    .........

    def like_count(self):
        LikeComment.objects.filter(comment__user=self.user, type=1).count()

views.py

def getRanking( anObject ):
    return anObject.like_count()

def myview(request):
    users = list(UserProfile.objects.filter())
    users.sort(key=getRanking, reverse=True)       
    return render(request,'page.html',{'users': users})

Upvotes: 1

Related Questions