I completely forgot about `extra` method and it seems it does solve the problem:
roles = Prefetch(
‘users',
queryset=User.objects.extra(select={'role': 'users_role.role'})
)
qs = Project.objects.prefetch_related(roles))
However, I’m still curious why `annotate` doesn’t work when used inside
`prefetch_related`.
1. Is it bug? Should it work in the same way as when used outside
`prefetch_related`?
2. Is there a way to avoid `extra` method? It should be avoided at all cost,
after all.
Thank you in advance!
Tom
> 20. 5. 2018 v 11:41, Tomáš Ehrlich <tomas.ehrl...@gmail.com>:
>
> 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/EFFACF5E-06B4-4F68-B388-83E30D8A3453%40gmail.com.
For more options, visit https://groups.google.com/d/optout.
signature.asc
Description: Message signed with OpenPGP
