id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
6986	Multiple conditions in Q objects are connected with differrent operators, depending on context	Michał Sałaban	nobody	"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."		closed	Database layer (models, ORM)	dev		fixed	qsrf-cleanup Q, operator, context		Unreviewed	0	0	0	0	0	0