The example query is:
q = Playteam.objects.filter(
Q(moderator=request.user) |
Q(
playteammembership__user=request.user,
playteammembership__user_accepted=True,
playteammembership__playteam_accepted=True
)
).order_by('name')
The query I intended to make should return a list of Playteam objects where request.user is a moderator or a member with a membership object having confirmation from both sides (half-confirmed membership objects act as invitations).
The SQL result is a big surprise:
In [64]: p._get_sql_clause()
Out[64]:
(['"playarena_playteam"."id"',
'"playarena_playteam"."name"',
'"playarena_playteam"."slug"',
'"playarena_playteam"."description"',
'"playarena_playteam"."city"',
'"playarena_playteam"."province"',
'"playarena_playteam"."moderator_id"',
'"playarena_playteam"."active"',
'"playarena_playteam"."players_wanted"'],
' FROM "playarena_playteam" INNER JOIN "playarena_playteammembership" AS "playarena_playteam__playteammembership" ON "playarena_playteam"."id" = "playarena_playteam__playteammembership"."playteam_id" WHERE (("playarena_playteam"."moderator_id" = %s OR "playarena_playteam__playteammembership"."user_id" = %s OR "playarena_playteam__playteammembership"."user_accepted" = %s OR "playarena_playteam__playteammembership"."playteam_accepted" = %s))',
[1, 1, True, True])
All conditions in second Q have been connected with OR operator.
What't more interesting:
q = Playteam.objects.filter(
Q(moderator=request.user) &
Q(
playteammembership__user=request.user,
playteammembership__user_accepted=True,
playteammembership__playteam_accepted=True
)
).order_by('name')
Please note the operator between two Q, which has been changed to AND. The result SQL is:
In [73]: p._get_sql_clause()
Out[73]:
(['"playarena_playteam"."id"',
'"playarena_playteam"."name"',
'"playarena_playteam"."slug"',
'"playarena_playteam"."description"',
'"playarena_playteam"."city"',
'"playarena_playteam"."province"',
'"playarena_playteam"."moderator_id"',
'"playarena_playteam"."active"',
'"playarena_playteam"."players_wanted"'],
' FROM "playarena_playteam" INNER JOIN "playarena_playteammembership" AS "playarena_playteam__playteammembership" ON "playarena_playteam"."id" = "playarena_playteam__playteammembership"."playteam_id" WHERE (("playarena_playteam"."moderator_id" = %s AND "playarena_playteam__playteammembership"."user_id" = %s AND "playarena_playteam__playteammembership"."user_accepted" = %s AND "playarena_playteam__playteammembership"."playteam_accepted" = %s))',
[1, 1, True, True])
All operators are AND now.
Looks like the operator applied to conditions inside single Q is a result of context where the Q has been used. This is definitely not intuitive behaviour, inconsistent with what we have in .filter() and .exclude() methods.
The final query which gives expected results is:
q = Playteam.objects.filter(
Q(moderator=request.user) |
(
Q(playteammembership__user=request.user) &
Q(playteammembership__user_accepted=True) &
Q(playteammembership__playteam_accepted=True)
)
)
).order_by('name')
My suggestion is to make AND a default operator within single Q, in the same way as in .filter() or to forbid using multiple congitions in single Q at all.
