Django ORM generating the wrong query for recent friends of an user

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

Django ORM generating the wrong query for recent friends of an user

Arthur Pires
Here're my models


    class PingUser(AbstractUser):
        friends = models.ManyToManyField("self", through="Friendship", symmetrical=False)
   
    class Friendship(TimestampedModel, SerializeMixin):
        STATUS_CHOICES = (
            ("pending", "Pending"),
            ("friend", "Friend"),
            ("favorite", "Favorite")
        )
        user = models.ForeignKey(PingUser, related_name="friendships")
        friend = models.ForeignKey(PingUser, related_name="friendships_reverse")
        status = models.CharField(max_length=20, default=STATUS_CHOICES[0][0], choices=STATUS_CHOICES)

        class Meta:
                unique_together = [('user', 'friend')]



The code I'm using to get the most recent friends of user 4


    id = 4
    friendships = Friendship.objects.exclude(status="pending").filter(
                ~Q(user__friendships__status="pending"), user__friendships__friend_id=id).order_by("-created_at")


I also tried several combinations like grouping all filtering inside one filter() but the query is the same


Generated query


    SELECT `sample_app_friendship`.`id`,
           `sample_app_friendship`.`created_at`,
           `sample_app_friendship`.`updated_at`,
           `sample_app_friendship`.`user_id`,
           `sample_app_friendship`.`friend_id`,
           `sample_app_friendship`.`status`
    FROM `sample_app_friendship`
    INNER JOIN `sample_app_pinguser` ON (`sample_app_friendship`.`user_id` = `sample_app_pinguser`.`id`)
    INNER JOIN `sample_app_friendship` T5 ON (`sample_app_pinguser`.`id` = T5.`user_id`)
    WHERE (NOT (`sample_app_friendship`.`status` = 'pending')
           AND NOT ((`sample_app_friendship`.`user_id` IN
                       (SELECT U2.`user_id`
                        FROM `sample_app_friendship` U2
                        WHERE (U2.`status` = 'pending'
                               AND U2.`user_id` IS NOT NULL))
                     AND `sample_app_pinguser`.`id` IS NOT NULL))
           AND T5.`friend_id` = 4)



The desired query is actually


    SELECT `sample_app_friendship`.`id`,
           `sample_app_friendship`.`created_at`,
           `sample_app_friendship`.`updated_at`,
           `sample_app_friendship`.`user_id`,
           `sample_app_friendship`.`friend_id`,
           `sample_app_friendship`.`status`
    FROM `sample_app_friendship`
    INNER JOIN `sample_app_pinguser` ON (`sample_app_friendship`.`user_id` = `sample_app_pinguser`.`id`)
    INNER JOIN `sample_app_friendship` T5 ON (`sample_app_pinguser`.`id` = T5.`user_id`)
    WHERE (NOT (`sample_app_friendship`.`status` = 'pending')
           AND (NOT T5.`status` = 'pending')
           AND T5.`friend_id` = 4)

Someone more experienced can shed some light?

Thanks!

--
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.
For more options, visit https://groups.google.com/groups/opt_out.
Reply | Threaded
Open this post in threaded view
|

Re: Django ORM generating the wrong query for recent friends of an user

Arthur Pires
I still can't find the problem.  Django isn't using the same "friendship" joined table for both conditions which makes me wonder if it's related to this https://docs.djangoproject.com/en/1.5/topics/db/queries/#spanning-multi-valued-relationships

Please help.

On Tuesday, September 24, 2013 10:50:54 AM UTC-3, Arthur Silva wrote:
Here're my models


    class PingUser(AbstractUser):
        friends = models.ManyToManyField("self", through="Friendship", symmetrical=False)
   
    class Friendship(TimestampedModel, SerializeMixin):
        STATUS_CHOICES = (
            ("pending", "Pending"),
            ("friend", "Friend"),
            ("favorite", "Favorite")
        )
        user = models.ForeignKey(PingUser, related_name="friendships")
        friend = models.ForeignKey(PingUser, related_name="friendships_reverse")
        status = models.CharField(max_length=20, default=STATUS_CHOICES[0][0], choices=STATUS_CHOICES)

        class Meta:
                unique_together = [('user', 'friend')]



The code I'm using to get the most recent friends of user 4


    id = 4
    friendships = Friendship.objects.exclude(status="pending").filter(
                ~Q(user__friendships__status="pending"), user__friendships__friend_id=id).order_by("-created_at")


I also tried several combinations like grouping all filtering inside one filter() but the query is the same


Generated query


    SELECT `sample_app_friendship`.`id`,
           `sample_app_friendship`.`created_at`,
           `sample_app_friendship`.`updated_at`,
           `sample_app_friendship`.`user_id`,
           `sample_app_friendship`.`friend_id`,
           `sample_app_friendship`.`status`
    FROM `sample_app_friendship`
    INNER JOIN `sample_app_pinguser` ON (`sample_app_friendship`.`user_id` = `sample_app_pinguser`.`id`)
    INNER JOIN `sample_app_friendship` T5 ON (`sample_app_pinguser`.`id` = T5.`user_id`)
    WHERE (NOT (`sample_app_friendship`.`status` = 'pending')
           AND NOT ((`sample_app_friendship`.`user_id` IN
                       (SELECT U2.`user_id`
                        FROM `sample_app_friendship` U2
                        WHERE (U2.`status` = 'pending'
                               AND U2.`user_id` IS NOT NULL))
                     AND `sample_app_pinguser`.`id` IS NOT NULL))
           AND T5.`friend_id` = 4)



The desired query is actually


    SELECT `sample_app_friendship`.`id`,
           `sample_app_friendship`.`created_at`,
           `sample_app_friendship`.`updated_at`,
           `sample_app_friendship`.`user_id`,
           `sample_app_friendship`.`friend_id`,
           `sample_app_friendship`.`status`
    FROM `sample_app_friendship`
    INNER JOIN `sample_app_pinguser` ON (`sample_app_friendship`.`user_id` = `sample_app_pinguser`.`id`)
    INNER JOIN `sample_app_friendship` T5 ON (`sample_app_pinguser`.`id` = T5.`user_id`)
    WHERE (NOT (`sample_app_friendship`.`status` = 'pending')
           AND (NOT T5.`status` = 'pending')
           AND T5.`friend_id` = 4)

Someone more experienced can shed some light?

Thanks!

--
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/e38830e9-51a6-4ed2-afa9-dc2b44c8110f%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.