CODEWITHSUNDEEP
djangodjango-queryset
wilbbe01
wilbbe01

Reputation: 1971

Django inner join on self. Is there a more efficient approach?

I have a model to track followers. This is a similar idea to every other social network site out there.

Right now my model looks like this.

class Follow(models.Model):
    follower = models.ForeignKey(UserProfile, related_name='follower')
    following = models.ForeignKey(UserProfile, related_name='following')
    follow_date = models.DateTimeField(auto_now_add=True)

Today I went to add some functionality that requires a mutual relationship. Person a follows b and b follows a. I wanted the current logged in user included as well so I used Q.

mutual_follows_qs = Follow.objects.raw('select o.* from follow o ' + \
                                       'inner join follow f ' + \
                                       'on o.following_id = f.follower_id' + \
                                       ' and o.follower_id = f.following_id' + \
                                       ' where o.follower_id = %s', [str(request.user.pk)])
mutual_follows = []
for mutual_follow in mutual_follows_qs:
    mutual_follows.append(mutual_follow.following.pk)
possible_users = User.objects.filter(Q(pk__in=mutual_follows) \
                                      | Q(pk=request.user.pk)).order_by('username')

I am executing more than one query. Yuck. I am building a list from my raw to use in Q(pk__in=mutual_follows). Yuck. I am curious what suggestions people have to improve the efficiency of this logic.

Here is the UserProfile model

class UserProfile(models.Model):
    user = models.OneToOneField(User)
    ...
    follow = models.ManyToManyField('self', blank = True, 
                                    symmetrical=False, through='Follow')
    ...

Upvotes: 0

Views: 2435

Answers (1)

Bernhard Vallant
Bernhard Vallant

Reputation: 50796

I'm not totally sure what you are trying to achieve, but if I'm getting it right you want all users for which Follow object exists where request.use is the follower and one where request.user is followed (I think the related_names are a bit confusing).

In you source code you use anUserProfile and a User model, not sure if that's a typo or intentional, but assuming there's just the User model this might work:

user = request.user
users = User.objects.filter(
    Q(Q(follower__follower=user) & Q(following__following=user)) |
    Q(pk=user.pk))).distinct()

Upvotes: 1

Related Questions