Reverse a query condition with M2M relation

Using Django Using the ORM
1

I have a query that traverses a M2M relation and queries several fields on the M2M side, like this:

query = Personne.objects.filter(
    Q(famille__prestationcipe__type_consult='hors_stats') &
    Q(famille__prestationcipe__date__lte=date_end) &
    Q(famille__prestationcipe__date__gte=date_start)
).distinct().values('pk', 'pays_origine')

I know the Q is not necessary here, but I added it for better comparison with the next query. The resulting SQL is exactly what I want:

SELECT DISTINCT "personne"."id", "personne"."pays_origine", "personne"."nom", "personne"."prenom"
FROM "personne"
INNER JOIN "famille" ON ("personne"."famille_id" = "famille"."id")
INNER JOIN "cipe_prestationcipe" ON ("famille"."id" = "cipe_prestationcipe"."famille_id")
WHERE ("cipe_prestationcipe"."type_consult" = hors_stats AND "cipe_prestationcipe"."date" <= 2022-07-31 AND "cipe_prestationcipe"."date" >= 2022-01-01)
ORDER BY "personne"."nom" ASC, "personne"."prenom" ASC

Now the problem is I want to reverse one of the condition, so I tried with this (the only difference being the ~ before the first filter condition):

query = Personne.objects.filter(
    ~Q(famille__prestationcipe__type_consult='hors_stats') &
    Q(famille__prestationcipe__date__lte=date_end) &
    Q(famille__prestationcipe__date__gte=date_start)
).distinct().values('pk', 'pays_origine')

And now the result is NOT what I want, because the query is producing an EXISTS expression with a subselect which will exclude too many rows from the expected results:

SELECT DISTINCT "personne"."id", "personne"."pays_origine", "personne"."nom", "personne"."prenom"
FROM "personne"
INNER JOIN "famille" ON ("personne"."famille_id" = "famille"."id")
INNER JOIN "cipe_prestationcipe" ON ("famille"."id" = "cipe_prestationcipe"."famille_id")
WHERE (NOT (EXISTS(SELECT 1 AS "a" FROM "cipe_prestationcipe" U2 WHERE (U2."type_consult" = hors_stats AND U2."famille_id" = ("personne"."famille_id")) LIMIT 1)) 
  AND "cipe_prestationcipe"."date" <= 2022-07-31 AND "cipe_prestationcipe"."date" >= 2022-01-01) 
ORDER BY "personne"."nom" ASC, "personne"."prenom" ASC

Is there any way to avoid that subselect and simply do a "cipe_prestationcipe"."type_consult" != hors_stats instead?

2

Responding to myself: my issue can be solved with a FilteredRelation:

query = Personne.objects.annotate(
    prestations=FilteredRelation('famille__prestationcipe',
        condition=~Q(famille__prestationcipe__type_consult='hors_stats')
    )
).filter(
    prestations__date__lte=date_end,
    prestations__date__gte=date_start)
).distinct().values('pk', 'pays_origine')

Thanks for reading me :slight_smile:

3 Likes
3

That is one way to do it.

Another way, for numeric data, is to reverse the not condition by rewriting the condition as a gt/lt pair of conditions joined by an or.

1 Like