Query friends of an user

[Arthur Silva]

 I want to query the friends of an User but I'm struggling to get the correct query.

Models:

class User(AbstractUser, TimestampedModel, SerializeMixin):
    city = models.CharField(max_length=60, blank=True)
    picture = models.URLField(blank=True)
    cover_picture = models.URLField(blank=True)
    phone = models.CharField(max_length=20, blank=True)
    access_token = models.CharField(max_length=32, blank=True, null=True, db_index=True)
    facebook_token = models.CharField(max_length=1024, blank=True, null=True)
    facebook_id = models.BigIntegerField(blank=True, null=True, db_index=True)
    twitter_token = models.CharField(max_length=1024, blank=True, null=True)
    twitter_id = models.BigIntegerField(blank=True, null=True, unique=True)
    friends = models.ManyToManyField("self", through="Friendship", symmetrical=False)


class Friendship(TimestampedModel, SerializeMixin):
    PENDING = "pending"
    FRIEND = "friend"
    FAVORITE = "favorite"
    STATUS_CHOICES = (
        (PENDING, "Pending"),
        (FRIEND, "Friend"),
        (FAVORITE, "Favorite")
    )
    user = models.ForeignKey(User, related_name="friendships")
    friend = models.ForeignKey(User, related_name="friendships_reverse")
    status = models.CharField(max_length=20, default=PENDING, choices=STATUS_CHOICES)


Query I'm trying to make

        id = 32 # I want friends from this user
        friends = User.objects.extra(select={"friendship_status": "status"}). \
            filter(Q(friendships_reverse__user_id=id), ~Q(friendships_reverse__status=Friendship.PENDING))


But the generated query is quite weird:

SELECT (status) AS `friendship_status`,
       `myapp_user`.`id`,
       `myapp_user`.`password`,
       `myapp_user`.`last_login`,
       `myapp_user`.`is_superuser`,
       `myapp_user`.`username`,
       `myapp_user`.`first_name`,
       `myapp_user`.`last_name`,
       `myapp_user`.`email`,
       `myapp_user`.`is_staff`,
       `myapp_user`.`is_active`,
       `myapp_user`.`date_joined`,
       `myapp_user`.`created_at`,
       `myapp_user`.`updated_at`,
       `myapp_user`.`city`,
       `myapp_user`.`picture`,
       `myapp_user`.`cover_picture`,
       `myapp_user`.`phone`,
       `myapp_user`.`access_token`,
       `myapp_user`.`facebook_token`,
       `myapp_user`.`facebook_id`,
       `myapp_user`.`twitter_token`,
       `myapp_user`.`twitter_id`
FROM `myapp_user`
INNER JOIN `myapp_friendship` ON (`myapp_user`.`id` = `myapp_friendship`.`friend_id`)
WHERE (`myapp_friendship`.`user_id` = 32
       AND NOT ((`myapp_user`.`id` IN
                   (SELECT U1.`friend_id`
                    FROM `myapp_friendship` U1
                    WHERE (U1.`status` = 'pending'
                           AND U1.`friend_id` IS NOT NULL))
                 AND `myapp_user`.`id` IS NOT NULL)))


Where clause should be as simple as:
`myapp_friendship`.`user_id` = 32 AND `myapp_friendship` != 'pending'

Can someone shed some light? This kind of issue is appearing all over my application.

[C. Kirby]

Why not just Friendship.objects.filter(user__pk = 32, status__in = [Friendship.FRIEND, Friendship.FAVORITE]) That will give you a QuerySet of all the friendships a user has. If you just want the list of friends then extend that query with a values_list like so:

Friendship.objects.filter(user__pk = 32, status__in = [Friendship.FRIEND, Friendship.FAVORITE]) .values_list('friend', flat=True)

[Arthur Silva]

Thanks for your reply.
Your suggestion works but I need the friends (User models) of a given user (User model) in this case.

This join (friends of an user) is also used at several other places to get stuff (comments for example) made by friends of an user.

--
Arthur Pires Ribeiro Silva

Universidade Federal de Uberlândia

(34) 9187-4731

--
You received this message because you are subscribed to a topic in the Google Groups "Django users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/django-users/g5ZRNeXXjWg/unsubscribe.
To unsubscribe from this group and all its topics, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/4c0d9664-a4b7-41cc-bca0-e4055a1e0cc0%40googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

[C. Kirby]

Oh, ok. You can do it from the other side then.

#32 is replaced with the User id you are interested in
User.object.filter(friendship__friend__pk = 32, friendship__status__in = [Friendship.FRIEND, Friendship.FAVORITE])