Huh, yes, that seems to work now. I'm going to confirm real quick, but I
don't think that worked back in v1.10. I was attempting to do the
subquery-ing in v1.10 for the past few days, because it was slow to do it
as 2 separate queries, and then just happened upon the release notes of
v1.11 and the new Subquery type, so I assumed it must be the new "correct"
way. I will update this thread with my findings.
On Friday, 12 May 2017 13:25:55 UTC-4, Simon Charette wrote:
>
> Hi Murray,
>
> That looks like a bug to me.
>
> Do you get similar results if you don't use the Subquery expression and
> pass the query
> directly instead? Something along the lines of
>
>
> Permit.objects.filter(~Q(uuid__in=activeSuspensionPermitUuidsQuery.values('permit__uuid'))
>
> Simon
>
> Le vendredi 12 mai 2017 11:02:22 UTC-4, Murray Christopherson a écrit :
>>
>> Not sure if I should raise this as a bug, because I'm not sure if I'm
>> using it correctly. I am writing code conceptually similar to the code I am
>> actually working with, I hope it suffices.
>>
>> Within my app, there is the concept of permits and suspensions:
>> class Permit(models.Model):
>> class Meta:
>> db_table = 'permits'
>>
>> uuid = models.UUIDField(primary_key=True, db_column='uuid')
>>
>> class Suspension(models.Model):
>> class Meta:
>> db_table = 'suspensions'
>>
>> uuid = models.UUIDField(primary_key=True, db_column='uuid')
>> permit = models.ForeignKey(Permit, db_column='permits_uuid')
>> startDate = models.DateField(db_column='start_date')
>> endDate = models.DateField(null=True, db_column='end_date')
>>
>> Within the app, I am try to get a set of permits that are not currently
>> expired, so I attempted to generate the query like this.
>>
>> activeSuspensionPermitUuidsQuery = Suspension.objects.filter(Q(
>> startDate__lte=Now()) & (Q(endDate__isnull=True) | Q(endDate__gt=Now
>> ()))).distinct('permit__uuid')
>>
>> activeSuspensionPermits = Permit.objects.filter(~Q(uuid__in=Subquery(
>> activeSuspensionPermitUuidsQuery.values('permit__uuid')))
>>
>> The SQL generated by this is (for PostgreSQL 9.4):
>> SELECT "permits"."uuid" FROM "permits" WHERE (NOT ("permits"."uuid" IN (
>> CAST(SELECT DISTINCT ON (U0."permits_uuid") U0."permits_uuid" FROM
>> "suspensions" U0 INNER JOIN "permits" U1 ON (U0."permits_uuid" = U1.
>> "uuid") WHERE (U0."start_date" <= (STATEMENT_TIMESTAMP()) AND (U0.
>> "end_date" IS NULL OR U0."end_date" > (STATEMENT_TIMESTAMP()))) AS uuid
>> ))));
>>
>> This generates the following error:
>> ERROR: syntax error at or near "SELECT"
>> LINE 1: ... FROM "permits" WHERE (NOT ("permits"."uuid" IN (CAST(SELECT
>> DIS...
>>
>> If I edit and run the SQL myself, like this:
>> SELECT "permits"."uuid" FROM "permits" WHERE (NOT ("permits"."uuid" IN
>> (SELECT
>> DISTINCT ON (U0."permits_uuid") U0."permits_uuid" FROM "suspensions" U0
>> INNER JOIN "permits" U1 ON (U0."permits_uuid" = U1."uuid") WHERE (U0.
>> "start_date" <= (STATEMENT_TIMESTAMP()) AND (U0."end_date" IS NULL OR U0.
>> "end_date" > (STATEMENT_TIMESTAMP()))))));
>>
>> It works fine. So the problem is the
>> CAST(... as uuid)
>> that's being added. Can anyone suggest if I'm doing it wrong, a
>> workaround, or if this is indeed a bug?
>>
>> 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 https://groups.google.com/group/django-users.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/804b9f84-8f9c-4121-8558-204ac5b7d6ea%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
