So I have this query which I would like to use as a filter:
select * from api_document ad
where not exists (
select 1
from api_documentactionuser ad2
where ad2.user_id=4 and ad2.action_id=3 and ad.id = ad2.document_id
limit 1
)
What I've tried with django is:
q = queryset.exclude(
Q(documentactionuser__action__id=3)
& Q(documentactionuser__user=current_user),
)
while queryset is a queryset on the api_document table. When I print the generated query however, django keeps separating the two conditions into two queries instead of simply using and, which in turn gives me the wrong data back:
select * FROM "api_document"
WHERE NOT (
EXISTS(SELECT 1 AS "a" FROM "api_documentactionuser" U1 WHERE (U1."action_id" = 3 AND U1."document_id" = ("api_document"."id")) LIMIT 1)
AND EXISTS(SELECT 1 AS "a" FROM "api_documentactionuser" U1 WHERE (U1."user_id" = 1 AND U1."document_id" = ("api_document"."id")) LIMIT 1)
)
I've tried chaining exclude().exclude(), filter(~@()).filter(~@()) and the above variant and it all returns nearly the same query, with the same data output
CodePudding user response:
Use ~Exists
and specify the queryset you want to check
queryset.filter(
~Exists(DocumentationActionUser.objects.filter(action__id=3).filter(user=current_user)
)
SHould do the trick (untested - just typed this directly here, so beware of typos! :) )