Query friends of an user

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Query friends of an user

Arthur Pires
 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.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
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/9a418a08-9183-477f-a8fe-e848d200f022%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
Reply | Threaded
Open this post in threaded view
|

Re: Query friends of an user

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)


On Tuesday, December 17, 2013 8:50:09 AM UTC-6, Arthur Silva wrote:
 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.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
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.
Reply | Threaded
Open this post in threaded view
|

Re: Query friends of an user

Arthur Pires
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


On Tue, Dec 17, 2013 at 2:51 PM, C. Kirby <[hidden email]> wrote:
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)



On Tuesday, December 17, 2013 8:50:09 AM UTC-6, Arthur Silva wrote:
 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.

--
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 [hidden email].
To post to this group, send email to [hidden email].
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.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
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/CAO_YK0WeoCaYT%2But51nvwiY6iFUXvFM5cW1FXdaWnf4hfBHP_w%40mail.gmail.com.
For more options, visit https://groups.google.com/groups/opt_out.
Reply | Threaded
Open this post in threaded view
|

Re: Query friends of an user

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])

On Tuesday, December 17, 2013 11:53:11 AM UTC-6, Arthur Silva wrote:
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


On Tue, Dec 17, 2013 at 2:51 PM, C. Kirby <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="T2-sSqtZ9xkJ" onmousedown="this.href='javascript:';return true;" onclick="this.href='javascript:';return true;">mis...@...> wrote:
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)



On Tuesday, December 17, 2013 8:50:09 AM UTC-6, Arthur Silva wrote:
 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.

--
You received this message because you are subscribed to a topic in the Google Groups "Django users" group.
To unsubscribe from this topic, visit <a href="https://groups.google.com/d/topic/django-users/g5ZRNeXXjWg/unsubscribe" target="_blank" onmousedown="this.href='https://groups.google.com/d/topic/django-users/g5ZRNeXXjWg/unsubscribe';return true;" onclick="this.href='https://groups.google.com/d/topic/django-users/g5ZRNeXXjWg/unsubscribe';return true;">https://groups.google.com/d/topic/django-users/g5ZRNeXXjWg/unsubscribe.
To unsubscribe from this group and all its topics, send an email to <a href="javascript:" target="_blank" gdf-obfuscated-mailto="T2-sSqtZ9xkJ" onmousedown="this.href='javascript:';return true;" onclick="this.href='javascript:';return true;">django-users...@googlegroups.com.
To post to this group, send email to <a href="javascript:" target="_blank" gdf-obfuscated-mailto="T2-sSqtZ9xkJ" onmousedown="this.href='javascript:';return true;" onclick="this.href='javascript:';return true;">django...@....
Visit this group at <a href="http://groups.google.com/group/django-users" target="_blank" onmousedown="this.href='http://groups.google.com/group/django-users';return true;" onclick="this.href='http://groups.google.com/group/django-users';return true;">http://groups.google.com/group/django-users.
To view this discussion on the web visit <a href="https://groups.google.com/d/msgid/django-users/4c0d9664-a4b7-41cc-bca0-e4055a1e0cc0%40googlegroups.com" target="_blank" onmousedown="this.href='https://groups.google.com/d/msgid/django-users/4c0d9664-a4b7-41cc-bca0-e4055a1e0cc0%40googlegroups.com';return true;" onclick="this.href='https://groups.google.com/d/msgid/django-users/4c0d9664-a4b7-41cc-bca0-e4055a1e0cc0%40googlegroups.com';return true;">https://groups.google.com/d/msgid/django-users/4c0d9664-a4b7-41cc-bca0-e4055a1e0cc0%40googlegroups.com.

For more options, visit <a href="https://groups.google.com/groups/opt_out" target="_blank" onmousedown="this.href='https://groups.google.com/groups/opt_out';return true;" onclick="this.href='https://groups.google.com/groups/opt_out';return true;">https://groups.google.com/groups/opt_out.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
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/9265ab59-f236-43af-bcdf-362513998229%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.