How to write a complex Mysql Query in Django

Question

I am new to Django and I am working on a small module of a Django application where I need to display the list of people who have common interest as that of any particular User. So Suppose if I am an user I can see the list of people who have similar interests like me.

For this I have 2 models :

models.py

class Entity(models.Model):
    name = models.CharField(max_length=255, unique=True)

    def __str__(self):
        return self.name


class UserLikes(models.Model):
    class Meta:
       unique_together = (('user', 'entity'),)

    user = models.ForeignKey(User)
    entity = models.ForeignKey(Entity)

    def __str__(self):
    return self.user.username + " : " + self.entity.name

So in the Entity Table I store the Entities in which user can be interested Eg : football, Music, Code etc.

and in the UserLikes I store the relation about which user likes which entity.

Now I have a Query to fetch details about which user has maximum interest like any particular user :

SELECT y.user_id, GROUP_CONCAT(y.entity_id) likes, COUNT(*) total
FROM likes_userlikes x
JOIN likes_userlikes y ON y.entity_id = x.entity_id AND y.user_id <> x.user_id
WHERE x.user_id = ?
GROUP BY y.user_id
ORDER BY total desc;

Problem is how do I write this Query using Django Querysets and change it into a function.

use raw? UserLikes.objects.raw('...')

Anzel
– Anzel

2015-10-30 15:06:42 +00:00
Commented Oct 30, 2015 at 15:06 — Anzel
– Anzel, Commented Oct 30, 2015 at 15:06

Shang Wang · Accepted Answer · 2015-10-30 15:58:39Z

2

# this gives you what are current user's interests
current_user_likes = UserLikes.objects.filter(user__id=user_id) \
                                      .values_list('entity', flat=True).distinct()
# this gives you who are the persons that shares the same interests
user_similar_interests = UserLikes.objects.filter(entity__id__in=current_user_likes) \
                                          .exclude(user__id=user_id) \
                                          .values('user', 'entity').distinct()
# finally the count
user_similar_interests_count = user_similar_interests.count()

Here the user_id is the user's id you want to query for.

One advice though, it's not good practice to use plural form for model names, just use UserLike or better, UserInterest for it. Django would add plural form when it needs to.

edited Oct 30, 2015 at 15:58

answered Oct 30, 2015 at 15:52

Shang Wang

25.7k21 gold badges77 silver badges95 bronze badges

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

Collectives™ on Stack Overflow

How to write a complex Mysql Query in Django

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related