Optimization error: lookup was already seen with a different queryset. You may need to adjust the ordering of your lookups

[cpontvieux-systra]

I’m having this error (from django.db) with my actual schema for some queries, but only if the DjangoOptimizerExtension is in place.

I narrowed down my project to a minimum reproducible scenario.

My exact error message is:

"steps" lookup was already seen with a different queryset. You may need to adjust the ordering of your lookups

How to reproduce it ?

• Get the mini project archive: bug-prefetch-strawberry-django-plus.tar.gz
• Extract it to a folder of your choice
• Install dependencies with poetry and enter shell
• Prepare database (sqlite) and run a dev server:

./manage.py migrate
./manage.py runscript app.import
./manage.py runserver_plus

• Open your browser to /graphql/ to open GraphiQL and try this query:

query {
  allProjects {
    id
    name
    isPublished
    workflowTemplates {
      id
      name
      sequence
      steps {
        sequence
        type
        label
        days0
        daysX
      }
    }
  }
}

• Constat the error message about prefetch clashing.
• Open app/schema.py file and change with_optim = False ⇒ the bug disappear.

Site nodes

I know the optimizer is not a silver bullet and dynamically prefetching is hard.
But I expect the query to complete, even if not correctly optimized.

• What I can do to my code to help the optimizer?
• How can I forsee when (and why) I need to help the optimizer (if it’s possible)?

If any information is missing, please ask. I also can be contacted directly by email at [email protected].

[bellini666]

Hey @cpontvieux-systra ,

I just tested your reproduction example and it can be fixed by removing the .order_by from both methods. Although I'll further investigate why this usage caused the issue... It shouldn't because it should have merged both prefetches into one.

Having said that, some tips for you:

1. Note that if you do self.some_related_items.all(), the optimizer has already that cached as a list. When you call .order_by() on it it will trigger another db request, causing n+1 problems. I would advise to use .all() only and, if you want to define a custom order, use it on your model's meta, which will make the results always be returned in that order unless overridden by another .order_by()

2. If you use the tip above, you can define the field as workflow_templates: list[Annotated["WorkflowTemplate", gql.lazy("..workflow_template.types")]], without needing to define a resolver, and strawberry-django-plus will resolve it correctly

3. If you really need to define a custom ordering there, you can use a prefetch object. E.g.:

    @gql.django.field(
        prefetch_related=[
            Prefetch(
                "workflow_templates",
                WorkflowTemplate.objects.all().order_by("sequence"),
                to_attr="workflow_templates_ordered"
            )
        ]
    )
    def workflow_templates(
        self: models.Project,
    ) -> list[Annotated["WorkflowTemplate", gql.lazy("..workflow_template.types")]]:
        return self.workflow_templates_ordered

This is a simple example and would actually not work in your case because you don't have WorkflowTemplate imported. But you can pass a lambda function to prefetch_related and import WorkflowTemplate at runtime to solve that.

[cpontvieux-systra]

Fabulous, thank you for your quick, helpful, and complete answer.

I will probably go for option 1. and 2. in this specific case, but may end up in case 3. for other types/objects

P.S. Oh one quick note on this mini project: if I name the method steps2 instead of steps the query complete (but maybe because the optimizer cannot figure out the link between the prefetch and the field name then). I don’t know if this helps you investigate, but just in case…