Django self reference (FK) change SQL result

[igor malinov]

Good day for Everyone.

Because I have self-reference, my queryset no work properly

I have a model

class Agency(Organization):
    organization_ptr = models.OneToOneField(to='Organization', parent_link=True, primary_key=True, related_name='%(class)s', serialize=False, on_delete=CASCADE)
    accreditation = models.OneToOneField('Accreditation', null=True, blank=True, on_delete=DO_NOTHING)
    parent = models.ForeignKey("self", null=True, blank=True, on_delete=SET_NULL)  

class Application(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    name = models.CharField(max_length=255)
    seers = models.ManyToManyField('Agency', blank=True, through='ApplicationAgencySeer')
    # and other


class ApplicationAgencySeer(models.Model):
    application = models.ForeignKey(Application, on_delete=models.CASCADE)
    agency = models.ForeignKey('Agency', on_delete=models.CASCADE)
    status = models.IntegerField('Status', choices=CHOICE)
    created = models.DateTimeField(auto_now_add=True)

Now I wanna exclude (simple query)

a = Application.objects.exclude(seers__agency__id='9e71cff4-443d-4c60-ac2d-9dcca2a9c147')
print(a.query)


" * "  change by myself

SELECT *
FROM "myapp_application"
WHERE NOT ("myapp_application"."id" IN (SELECT U1."application_id"
                                           FROM "myapp_applicationagencyseer" U1
                                                  INNER JOIN "myapp_agency" U2 ON (U1."agency_id" = U2."organization_ptr_id")
                                                  INNER JOIN "myapp_agency" U3 ON (U2."organization_ptr_id" = U3."parent_id")
                                           WHERE U3."organization_ptr_id" = '9e71cff4-443d-4c60-ac2d-9dcca2a9c147'))
ORDER BY "myapp_application"."created_date" DESC;

result

application_id
7d83d056-5a7d-4095-9037-98bde29a3d78    otherfields...    otherfields..
7cb60afc-109d-4570-ad24-6cad6b7ddd9a    otherfields...    otherfields..           <-- this row error


exclude - no really exclude
Invoked problem by `INNER JOIN "myapp_agency" U3 ON (U2."organization_ptr_id" = U3."parent_id")`

--return 0
(SELECT U1."application_id"
FROM "myapp_applicationagencyseer" U1
INNER JOIN "myapp_agency" U2 ON (U1."agency_id" = U2."organization_ptr_id")
INNER JOIN "myapp_agency" U3 ON (U2."organization_ptr_id" = U3."parent_id")
WHERE U3."organization_ptr_id" = '9e71cff4-443d-4c60-ac2d-9dcca2a9c147')

--althouth I have  myapp_applicationagencyseer
id       created                           agency_id                                   application_id                             status
1	2018-12-10 17:41:14.272684	9e71cff4-443d-4c60-ac2d-9dcca2a9c147	7cb60afc-109d-4570-ad24-6cad6b7ddd9a	1
2	2018-12-11 19:25:58.818000	9e71cff4-443d-4c60-ac2d-9dcca2a9c147	7cb60afc-109d-4570-ad24-6cad6b7ddd9a	0

-- myapp_agency
organization_ptr_id                        accreditation   parent
aff44d42-ce81-4c3e-b6e1-056ed9351adb	Null	       Null
9e71cff4-443d-4c60-ac2d-9dcca2a9c147	10АА71	       Null       <-- It have Null parent


Why Query have `INNER JOIN "myapp_agency" U3 ON (U2."organization_ptr_id" = U3."parent_id")` . 

[Ira Abbott]

A few questions:

You are posting this example because you believe you have found a bug in django, correct?  Otherwise, please post to the users group.

The behavioral problem described is "exclude - no really exclude", correct?  You perform an exclusion and do not see the exclusion and then show the queryset.

I am not an expert, but I believe the reason the exclude is not working might be that 'id' as a field of Agency is not a UUID.  The UUID primary key is in Applications.  Agency would  have an 'id' as a built in Primary key, but it will be an integer.  Since the primary key for Agency is a OneToOne (unique ForeignKey), I suspect, but do not know that id as a built-in member may follow.  Either way it is probably not a UUID and will not match for exclusion.

On initial inspection, I doubt this example is inappropriate behavior.

Regards,

Ira