[Answered ]-Django: How to 'scrub' list of users

by

2👍

✅

I’d guess from the error message that you are missing parentheses:

 for editor in dashboard.dashboardeditor_set.iterator():

This is because the error message seems to imply that iterator is in instance method so you need to call it to get the actual iterator.

👤Mark Byers

0👍

Use a combination of values_list() and exclude() to get the results in a single query (with a nested SELECT):

dash = Dashboard.objects.get(slug=slug)
User.objects.exclude(id__in=dash.dashboardeditor_set.values_list('user', flat=True))

Generates the following SQL:

SELECT "auth_user"."id",
       "auth_user"."username",
       "auth_user"."first_name",
       "auth_user"."last_name",
       "auth_user"."email",
       "auth_user"."password",
       "auth_user"."is_staff",
       "auth_user"."is_active",
       "auth_user"."is_superuser",
       "auth_user"."last_login",
       "auth_user"."date_joined"
FROM "auth_user"
WHERE NOT ("auth_user"."id" IN
             (SELECT U0."user_id"
              FROM "dashboard_dashboardeditor" U0
              WHERE U0."dashboard_id" = 1)) LIMIT 21  [0.68ms]

Using iterator will trigger roughly N+2 queries, where N is the number of DashboardEditors for the given Dashboard:

In [24]: users = list(User.objects.all())
SELECT "auth_user"."id",
       "auth_user"."username",
       "auth_user"."first_name",
       "auth_user"."last_name",
       "auth_user"."email",
       "auth_user"."password",
       "auth_user"."is_staff",
       "auth_user"."is_active",
       "auth_user"."is_superuser",
       "auth_user"."last_login",
       "auth_user"."date_joined"
FROM "auth_user"  [0.40ms]


In [25]: for editor in dash.dashboardeditor_set.iterator():
   ....:     users.remove(editor.user)
   ....: 
SELECT "dashboard_dashboardeditor"."id",
       "dashboard_dashboardeditor"."user_id",
       "dashboard_dashboardeditor"."dashboard_id"
FROM "dashboard_dashboardeditor"
WHERE "dashboard_dashboardeditor"."dashboard_id" = 1  [0.15ms]

SELECT "auth_user"."id",
       "auth_user"."username",
       "auth_user"."first_name",
       "auth_user"."last_name",
       "auth_user"."email",
       "auth_user"."password",
       "auth_user"."is_staff",
       "auth_user"."is_active",
       "auth_user"."is_superuser",
       "auth_user"."last_login",
       "auth_user"."date_joined"
FROM "auth_user"
WHERE "auth_user"."id" = 2  [0.25ms]

SELECT "auth_user"."id",
       "auth_user"."username",
       "auth_user"."first_name",
       "auth_user"."last_name",
       "auth_user"."email",
       "auth_user"."password",
       "auth_user"."is_staff",
       "auth_user"."is_active",
       "auth_user"."is_superuser",
       "auth_user"."last_login",
       "auth_user"."date_joined"
FROM "auth_user"
WHERE "auth_user"."id" = 3  [0.24ms]

There were two DashboardEditors in this example, and the ORM ran 4 queries to get the resulting list of non-editor Users.

👤Chris Lawlor

Leave a comment