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