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

[Arthur Silva]

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 django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
For more options, visit https://groups.google.com/groups/opt_out.