Confused by N+1 problem in my view

Using Django Using the ORM
1

Hi! I am new to Django and trying to understand the ORM and templates. I wonder if someone can help me solve a basic N+1 problem I can’t seem to solve or understand?

I have User, Material, and Category models in a basic CRUD app used for managing material data for Architecture/Construction 3D modeling.

# models.py
class User(AbstractUser):
    pass

class MaterialCategory(models.Model):
    category = models.CharField(max_length=2, null=False, blank=False)

class Material(models.Model):
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    category = models.ForeignKey(MaterialCategory, on_delete=models.CASCADE)

I have a simple view that gets the unique Materials associated with its User, as well as any ‘shared’ materials associated with the admin user (user__id==1) of the app:

# views.py
def get_materials(request):
    materials = Material.objects.select_related("category", "user")
        .filter(Q(user=request.user) | Q(user__id=1))
    context = {
        "materials": materials,
        "current_user": request.user,
    }
    return render(request, "table.html", context)

Which is rendered in a simple HTML document. I want to show all the Materials, but if the material belongs to the user, they get some extra buttons on the row (edit, delete, etc…). **The problem seems to occur on the if material.user ... line

<-- table.html -->
...
{% for material in materials %}
    {% if material.user == current_user %}
        <-- ... some display logic... -->
    {% endif %}
{% endfor %}
...

which, according to the Django debug toolbar, results in duplicated selects, one for each item in the table:

SELECT "myapp_user"."id",
       "myapp_user"."password",
       "myapp_user"."last_login",
       "myapp_user"."is_superuser",
       "myapp_user"."username",
       "myapp_user"."first_name",
       "myapp_user"."last_name",
       "myapp_user"."email",
       "myapp_user"."is_staff",
       "myapp_user"."is_active",
       "myapp_user"."date_joined"
  FROM "myapp_user"
 WHERE "myapp_user"."id" = 1
 LIMIT 21
 16 similar queries.  Duplicated 15 times.

Screenshot 2024-12-10 at 11.35.49 AM
Screenshot 2024-12-10 at 11.35.49 AM1567×879 201 KB

So… I guess the question is: can you tell what I am doing wrong here? I would have though using the select_related(...) would avoid this? Am I using it wrong here? Or am I wrong that the problem is with the if... in the template and is instead something wrong with my query in the view?

Any input or thoughts are much appreciated!
thank you!
@ed-p-may

Environment:

  • Django 5.1.3
  • Python 3.13.0
  • SQLite
2

Yes, select_related should prevent this. The error is likely something very unexpected.

Did you confirm that the select above the one you have highlighted in your image is pulling from the user table in the SELECT portion?

And is it possible that the running application isn’t running your latest version of code?

If those areas both seem reasonable, then the next step may be to provide the exact code of the view and template.

1 Like
3

Hi @CodenameTim ,

Thanks for the suggestion: you were correct, for some reason that select was not doing the join in the actual code - I must have mixed something up there - when I fixed that, it now works as expected (no extra selects). Thank you!

@ed-p-may

4

Glad to hear you solved it! Nice work

5

I might be wrong but filter() method returns a new queryset so your previously evaluated queryset with select_related() cleared unused.

From docs:

Remember that, as always with QuerySets , any subsequent chained methods which imply a different database query will ignore previously cached results, and retrieve data using a fresh database query.

To avoid such behaviour shift your select_related() method after filter() call.

6

You’re wrong indeed, but just because the queryset is not evaluated until its iterated over. The order of the method calls does not matter, in that scenario.

1 Like