Query optimizer doesn't work with relay connection

[jlgonzalez-martinez]

Describe the Bug

Example project could be found here.

The problem is that uncomment the students that use ListConnectionWithTotalCount and comment the other one seems to not optimize automatically. I try to put a specific prefetch in the connection but then don't optimize inner fields.

System Information

• Operating system: Macos
• Strawberry version (if applicable): strawberry-graphql 0.204.0 strawberry-graphql-django 0.16.0

Additional Context

types.py

from typing import List

import strawberry_django as gql
from strawberry import relay, auto
from strawberry_django.relay import ListConnectionWithTotalCount

from school.models import Career, Student, School


@gql.type(Career)
class CareerType(relay.Node):
    title: auto


@gql.type(Student)
class StudentType(relay.Node):
    name: auto
    nia: auto
    career: "CareerType"

    @gql.field
    def upper_name(self) -> str:
        """Return the name in uppercase."""
        return self.name.upper()


@gql.type(School)
class SchoolType(relay.Node):
    name: auto
    # students: List[StudentType]
    students: ListConnectionWithTotalCount[StudentType] = gql.connection(
         prefetch_related="students"
    )

Upvote & Fund

• We're using Polar.sh so you can upvote and help fund this issue.
• We receive the funding once the issue is completed & confirmed by you.
• Thank you in advance for helping prioritize & fund our backlog.

[bellini666]

This issue is the same as https://github.com/strawberry-graphql/strawberry-graphql-django/issues/340 .

This is happening because the optimizer currently is skipping any nested connections: https://github.com/strawberry-graphql/strawberry-graphql-django/blob/main/strawberry_django/optimizer.py#L320C29-L320C29

I'll try to create a meta issue about this to discuss how we can properly optimize nested connections. Because right now, since the nested query will be sliced, trying to optimize it will do the opposite of what we want the optimizer to do, which is do an extra prefetch without limit/offset.

[bellini666]

@jlgonzalez-martinez as I mentioned in this comment, I just noticed that you are also doing a prefetch_related in the connection.

For the reasons I mentioned there, can you test without it to see if it does work correctly?

[patrick91]

@bellini666 suggested this while on a call :D

WITH numbered_rows AS (
  SELECT 
     "_FilmToSpecies"."A" AS "_prefetch_related_val_a_id",
     "Species"."id",
     "Species"."name",
     ROW_NUMBER() OVER (
       PARTITION BY "_FilmToSpecies"."A"
       ORDER BY "Species"."id"
     ) row_num
   FROM "Species"
   INNER JOIN "_FilmToSpecies"
     ON ("Species"."id" = "_FilmToSpecies"."B")
   WHERE "_FilmToSpecies"."A" IN ('1', '2', '3')
)
SELECT "_prefetch_related_val_a_id", "id", "name" FROM numbered_rows WHERE row_num <= 10;

[bellini666]

@bellini666 suggested this while on a call :D

WITH numbered_rows AS (
  SELECT 
     "_FilmToSpecies"."A" AS "_prefetch_related_val_a_id",
     "Species"."id",
     "Species"."name",
     ROW_NUMBER() OVER (
       PARTITION BY "_FilmToSpecies"."A"
       ORDER BY "Species"."id"
     ) row_num
   FROM "Species"
   INNER JOIN "_FilmToSpecies"
     ON ("Species"."id" = "_FilmToSpecies"."B")
   WHERE "_FilmToSpecies"."A" IN ('1', '2', '3')
)
SELECT "_prefetch_related_val_a_id", "id", "name" FROM numbered_rows WHERE row_num <= 10;

@patrick91 wow, was testing here and just noticed that Django 4.2+ actually does this window function filtering when you slice a prefetch. e.g.

SomeModel.objects.prefetch_related(
    Prefetch(
        "some_relation",
        queryset=SomeRelation.objects.all()[:10],
    )
)

It will use the ROW_NUMBER() window function and filter by it

[catgirlinspace]

hiya! am running into this same issue :( the 11 similar queries are always queries like SELECT "battles_battle"."id", "battles_battle"."uploader_id" FROM "battles_battle" WHERE "battles_battle"."id" = (some int) LIMIT 21

[stygmate]

Hi, any progress on this one ?