GitLab

Before:

(	"lava_scheduler_app_testjob"."id" IN (
 			SELECT U0."id"
 			FROM "lava_scheduler_app_testjob" U0
 			INNER JOIN  "lava_scheduler_app_testjob_viewing_groups" U1
 			ON (U0."id" = U1."testjob_id")
 			WHERE U1."group_id" IS NOT NULL)
 		AND NOT ("lava_scheduler_app_testjob"."id" IN (
 			SELECT V1."testjob_id"
 			FROM "lava_scheduler_app_testjob_viewing_groups" V1
 			WHERE V1."group_id" IN 
 				(SELECT U0."id" FROM "auth_group" U0 WHERE NOT (U0."id" IN (121))))))

Execution Time: 7015.967 ms

After:

(
	NOT EXISTS (
		(SELECT "group_id" FROM "lava_scheduler_app_testjob_viewing_groups" WHERE testjob_id = lava_scheduler_app_testjob."id")
		EXCEPT
		(SELECT "group_id" FROM auth_user_groups WHERE user_id = 2)))

Execution Time: 71.505 ms

The issue is I can't make Django actually generate the better query.

This is my best attempt:

test = TestJob.viewing_groups.through.objects.filter(
          testjob_id=OuterRef('id')).values('group_id')
test2 = test.difference(user.groups.values('id'))
filters |= Q(~Exists(test3))

The issue is that Exists cannot be used as a part of the filter in Django < 3.

Debian Bullseye comes with Django 2.2. Attempt to install version 3.2 from backports was not successful as it conflicted with python3-django-tables2.

There are 2 solutions I can think of:

1. Upgrade Django but there is no guarantee that generated SQL can be any good.
2. Use raw SQL. Will be very ugly.