Hello, I have a two models (User, Project) in m2m relationship with intermediate (Role) table.
When I’m selecting all users in project and I also want to select corresponding
role, I simply annotate one field from Role table using F expression:
Users.objects.all().filter(projects__name=‘Django’).annotate(role=F(‘roles__role’))
Annotate in this case doesn’t create new join, because `roles` table is already
joined to filter on data from `projects` table. This works well.
However, I tried to same in `prefetch_related` and I’m getting duplicate rows,
because there’s a new JOIN statement added. (Usecase: Selecting all projects in
DB with all users per project)
The SQL statement with `prefetch_related`, but without `annotate` looks like
this:
roles = Prefetch(
‘users',
queryset=User.objects.all()
)
qs = Project.objects.prefetch_related(roles)
SELECT
("users_role"."project_id") AS "_prefetch_related_val_project_id",
— other fields here
FROM "users_user"
INNER JOIN "users_role" ON ("users_user"."id" = "users_role"."user_id")
WHERE "users_role"."project_id” IN (1, 2, 3, 4, 5)
As you can see, the table `users_role` is already joined, so I’m basically
looking for Django ORM expression which generates following SQL query:
SELECT
("users_role"."project_id") AS "_prefetch_related_val_project_id”,
“users_role_.”role”,
— other fields here
FROM "users_user"
INNER JOIN "users_role" ON ("users_user"."id" = "users_role"."user_id")
WHERE "users_role"."project_id” IN (1, 2, 3, 4, 5)
Unfortunatelly, following expression generates incorrect SQL:
roles = Prefetch(
‘users',
queryset=User.objects.all().annotate(role=F('roles__role'))
)
qs = Project.objects.prefetch_related(roles)
SELECT
("users_role"."project_id") AS "_prefetch_related_val_project_id",
"users_role"."role" AS “role”,
— other fields here
FROM "users_user"
LEFT OUTER JOIN "users_role" ON ("users_user"."id" = "users_role"."user_id")
INNER JOIN "users_role" T3 ON ("users_user"."id" = T3."user_id")
WHERE T3."project_id" IN
(1, 2, 3, 4, 5)
The extra `left outer join` causes duplicate entries.
I’ve found one ticket (https://code.djangoproject.com/ticket/27144
<https://code.djangoproject.com/ticket/27144>) which seems to be relevant, but
it’s old and closed.
Any ideas? Is it bug or is there really a reason to include extra JOIN? I’m not
very skilled in relational algebra.
Thank you in advance!
Cheers,
Tom
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/FCD600AD-E1DE-495D-9C6B-B3E3A3F693D9%40gmail.com.
For more options, visit https://groups.google.com/d/optout.
signature.asc
Description: Message signed with OpenPGP
