Fund: Nested Perms Results in unecessary queries (N+1?)

Describe the Bug

Nesting permissions i.e private_details checked by HasSourcePerm yields unnecessary queries that could be optimized. Current code yields 6 queries, while the workaround below results in 2.

query = """
    {
        notes {
            id
            publicDetails
            privateDetails
        }
    }
"""

@strawberry.type
class Query:
    notes: List[NoteType] = strawberry_django.field(
        extensions=[HasRetvalPerm(NotePermissions.VIEW)],
    )

@strawberry_django.type(models.Note, pagination=True)
class NoteType:
    id: auto
    public_details: auto
    private_details: Optional[str] = strawberry_django.field(
        extensions=[HasSourcePerm(PrivateNotePermissions.VIEW)],
    )

    SELECT "accounts_user"."id", "accounts_user"."password", "accounts_user"."username", "accounts_user"."first_name", "accounts_user"."last_name", "accounts_user"."date_joined", "accounts_user"."last_login", "accounts_user"."email", "accounts_user"."is_superuser", "accounts_user"."is_staff", "accounts_user"."is_active" FROM "accounts_user" WHERE "accounts_user"."id" = 5 LIMIT 21
    SELECT "notes_note"."id", "notes_note"."public_details", "notes_note"."private_details" FROM "notes_note" WHERE (EXISTS(SELECT 1 AS "a" FROM "auth_permission" U0 INNER JOIN "accounts_user_user_permissions" U1 ON (U0."id" = U1."permission_id") WHERE (U1."user_id" = 5 AND U0."content_type_id" = 2 AND U0."codename" = 'view_note') LIMIT 1) OR EXISTS(SELECT 1 AS "a" FROM "auth_group" U0 INNER JOIN "accounts_user_groups" U1 ON (U0."id" = U1."group_id") INNER JOIN "auth_group_permissions" U3 ON (U0."id" = U3."group_id") INNER JOIN "auth_permission" U4 ON (U3."permission_id" = U4."id") WHERE (U1."user_id" = 5 AND U4."content_type_id" = 2 AND U4."codename" = 'view_note') LIMIT 1) OR "notes_note"."id" IN ((SELECT DISTINCT (U0."content_object_id")::bigint AS "cast1" FROM "notes_noteuserobjectpermission" U0 INNER JOIN "auth_permission" U2 ON (U0."permission_id" = U2."id") WHERE (U0."user_id" = 5 AND U2."content_type_id" = 2 AND U2."codename" = 'view_note')) UNION (SELECT DISTINCT (U0."content_object_id")::bigint AS "cast1" FROM "notes_notegroupobjectpermission" U0 INNER JOIN "auth_group" U1 ON (U0."group_id" = U1."id") INNER JOIN "accounts_user_groups" U2 ON (U1."id" = U2."group_id") INNER JOIN "auth_permission" U4 ON (U0."permission_id" = U4."id") WHERE (U2."user_id" = 5 AND U4."content_type_id" = 2 AND U4."codename" = 'view_note'))))
    SELECT "django_content_type"."app_label", "auth_permission"."codename" FROM "auth_permission" INNER JOIN "accounts_user_user_permissions" ON ("auth_permission"."id" = "accounts_user_user_permissions"."permission_id") INNER JOIN "django_content_type" ON ("auth_permission"."content_type_id" = "django_content_type"."id") WHERE "accounts_user_user_permissions"."user_id" = 5
    SELECT "django_content_type"."app_label", "auth_permission"."codename" FROM "auth_permission" INNER JOIN "auth_group_permissions" ON ("auth_permission"."id" = "auth_group_permissions"."permission_id") INNER JOIN "auth_group" ON ("auth_group_permissions"."group_id" = "auth_group"."id") INNER JOIN "accounts_user_groups" ON ("auth_group"."id" = "accounts_user_groups"."group_id") INNER JOIN "django_content_type" ON ("auth_permission"."content_type_id" = "django_content_type"."id") WHERE "accounts_user_groups"."user_id" = 5
    SELECT "auth_permission"."codename" FROM "auth_permission" INNER JOIN "django_content_type" ON ("auth_permission"."content_type_id" = "django_content_type"."id") INNER JOIN "notes_noteuserobjectpermission" ON ("auth_permission"."id" = "notes_noteuserobjectpermission"."permission_id") WHERE ("auth_permission"."content_type_id" = 2 AND "notes_noteuserobjectpermission"."content_object_id" = 1 AND "notes_noteuserobjectpermission"."user_id" = 5) ORDER BY "django_content_type"."app_label" ASC, "django_content_type"."model" ASC, "auth_permission"."codename" ASC
    SELECT "auth_permission"."codename" FROM "auth_permission" INNER JOIN "django_content_type" ON ("auth_permission"."content_type_id" = "django_content_type"."id") INNER JOIN "notes_notegroupobjectpermission" ON ("auth_permission"."id" = "notes_notegroupobjectpermission"."permission_id") INNER JOIN "auth_group" ON ("notes_notegroupobjectpermission"."group_id" = "auth_group"."id") INNER JOIN "accounts_user_groups" ON ("auth_group"."id" = "accounts_user_groups"."group_id") WHERE ("auth_permission"."content_type_id" = 2 AND "notes_notegroupobjectpermission"."content_object_id" = 1 AND "accounts_user_groups"."user_id" = 5) ORDER BY "django_content_type"."app_label" ASC, "django_content_type"."model" ASC, "auth_permission"."codename" ASC

Additional Context

Workaround

@strawberry_django.type(models.Note, pagination=True)
class NoteType:
    id: auto
    public_details: auto

    @strawberry_django.field(
        annotate={
            "_private_details": lambda info: Coalesce(
                Subquery(
                    # Needs Fix In #498 
                    filter_for_user(
                        models.Note.objects.all(),
                        info.context.request.user,
                        [PrivateNotePermissions.VIEW],
                    )
                    .filter(id=OuterRef("pk"))
                    .values("private_details")[:1],
                    output_field=CharField(),
                ),
                Value(None),
            ),
        },
    )
    def private_details(self, root: models.Note) -> Optional[str]:
        return root._private_details  # type: ignore

    SELECT "accounts_user"."id", "accounts_user"."password", "accounts_user"."username", "accounts_user"."first_name", "accounts_user"."last_name", "accounts_user"."date_joined", "accounts_user"."last_login", "accounts_user"."email", "accounts_user"."is_superuser", "accounts_user"."is_staff", "accounts_user"."is_active" FROM "accounts_user" WHERE "accounts_user"."id" = 5 LIMIT 21
    SELECT "notes_note"."id", "notes_note"."public_details", "notes_note"."private_details", COALESCE((SELECT W0."private_details" FROM "notes_note" W0 WHERE ((EXISTS(SELECT 1 AS "a" FROM "auth_permission" U0 INNER JOIN "accounts_user_user_permissions" U1 ON (U0."id" = U1."permission_id") WHERE (U1."user_id" = 5 AND U0."content_type_id" = 2 AND U0."codename" = 'view_note_private_details') LIMIT 1) OR EXISTS(SELECT 1 AS "a" FROM "auth_group" U0 INNER JOIN "accounts_user_groups" U1 ON (U0."id" = U1."group_id") INNER JOIN "auth_group_permissions" U3 ON (U0."id" = U3."group_id") INNER JOIN "auth_permission" U4 ON (U3."permission_id" = U4."id") WHERE (U1."user_id" = 5 AND U4."content_type_id" = 2 AND U4."codename" = 'view_note_private_details') LIMIT 1) OR W0."id" IN ((SELECT DISTINCT (U0."content_object_id")::bigint AS "cast1" FROM "notes_noteuserobjectpermission" U0 INNER JOIN "auth_permission" U2 ON (U0."permission_id" = U2."id") WHERE (U0."user_id" = 5 AND U2."content_type_id" = 2 AND U2."codename" = 'view_note_private_details')) UNION (SELECT DISTINCT (U0."content_object_id")::bigint AS "cast1" FROM "notes_notegroupobjectpermission" U0 INNER JOIN "auth_group" U1 ON (U0."group_id" = U1."id") INNER JOIN "accounts_user_groups" U2 ON (U1."id" = U2."group_id") INNER JOIN "auth_permission" U4 ON (U0."permission_id" = U4."id") WHERE (U2."user_id" = 5 AND U4."content_type_id" = 2 AND U4."codename" = 'view_note_private_details')))) AND W0."id" = ("notes_note"."id")) LIMIT 1), NULL) AS "_private_details" FROM "notes_note" WHERE (EXISTS(SELECT 1 AS "a" FROM "auth_permission" U0 INNER JOIN "accounts_user_user_permissions" U1 ON (U0."id" = U1."permission_id") WHERE (U1."user_id" = 5 AND U0."content_type_id" = 2 AND U0."codename" = 'view_note') LIMIT 1) OR EXISTS(SELECT 1 AS "a" FROM "auth_group" U0 INNER JOIN "accounts_user_groups" U1 ON (U0."id" = U1."group_id") INNER JOIN "auth_group_permissions" U3 ON (U0."id" = U3."group_id") INNER JOIN "auth_permission" U4 ON (U3."permission_id" = U4."id") WHERE (U1."user_id" = 5 AND U4."content_type_id" = 2 AND U4."codename" = 'view_note') LIMIT 1) OR "notes_note"."id" IN ((SELECT DISTINCT (U0."content_object_id")::bigint AS "cast1" FROM "notes_noteuserobjectpermission" U0 INNER JOIN "auth_permission" U2 ON (U0."permission_id" = U2."id") WHERE (U0."user_id" = 5 AND U2."content_type_id" = 2 AND U2."codename" = 'view_note')) UNION (SELECT DISTINCT (U0."content_object_id")::bigint AS "cast1" FROM "notes_notegroupobjectpermission" U0 INNER JOIN "auth_group" U1 ON (U0."group_id" = U1."id") INNER JOIN "accounts_user_groups" U2 ON (U1."id" = U2."group_id") INNER JOIN "auth_permission" U4 ON (U0."permission_id" = U4."id") WHERE (U2."user_id" = 5 AND U4."content_type_id" = 2 AND U4."codename" = 'view_note'))))

Strawberry GraphQL/strawberry-django

Nested Perms Results in unecessary queries (N+1?)

Describe the Bug

Additional Context

How does funding with Polar work?

Backer

Contributor

Maintainer

Strawberry GraphQL/strawberry-django

Nested Perms Results in unecessary queries (N+1?)

Describe the Bug

Additional Context

How does funding with Polar work?

Backer

Why does "Fund on completion" require GitHub login?

When is the invoice due for "Fund on completion"?

What happens if the issue is never completed?

Do I get any extra benefits by funding?

Do I get progress updates?

Contributor

Do I get a reward?

Is rewards guaranteed?

Maintainer

How can I get funding like this for my open source initiatives?