Django self reference (FK) change SQL result

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

Django self reference (FK) change SQL result

madjardi
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")` . 

--
You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" 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 https://groups.google.com/group/django-developers.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/de52c7d8-b05e-4266-8ab8-146f098ac5d4%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Django self reference (FK) change SQL result

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


On Wednesday, December 12, 2018 at 2:42:05 PM UTC-5, igor malinov wrote:
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")` . 

--
You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" 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 https://groups.google.com/group/django-developers.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/59d98393-0e96-4fd7-9a73-cfea2c83f447%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.