Django filter ManyToMany with Q and Or

user_916945 0
Viewed 57
class State(models.Model):
    name = models.CharField(max_length=55, unique=True)

class City(models.Model):
    name = models.CharField(max_length=255)
    states = models.ManyToManyField(State, related_name='cities')
    parent = models.ForeignKey('self', on_delete=models.CASCADE, null=True, related_name='children', blank=True)

I have a state and I want to find cities that either have null parent or whose parent states doesn't contain a state.

I tried state.cities.filter(Q(parent=None) | ~Q(parent__states=state)) but it doesn't work and I query is strange:

`SELECT "city"."id", "city"."name", "city"."parent_id", FROM "city" INNER JOIN "city_states" ON ("city"."id" = "city_states"."city_id") WHERE ("city_states"."state_id" = 7 AND ("city"."parent_id" IS NULL OR NOT ("city"."parent_id" IN (SELECT U2."city_id" FROM "city_states" U2 WHERE (U2."state_id" = 7 AND U2."id" = "city_states"."id")) AND "city"."parent_id" IS NOT NULL))) ORDER BY "city"."name" ASC`

In particular what does the code AND U2."id" = "city_states"."id" perform?

django-queryset django-models django
1 Answers

You can simply work with the .exclude(…) [Django-doc] here:

state.cities.exclude(
    parent__states=state
)

This will still work if parent is None/NULL, since then the parent__states=state does not hold.

This will construct a query that looks like:

SELECT app_name_city.*
FROM app_name_city
WHERE NOT (
    app_name_city.parent_id IN (
        SELECT U2.city_id
        FROM `app_name_city_states U2
        WHERE U2.state_id = id_of_state
    )
    AND app_name_city.parent_id IS NOT NULL
)

The subquery thus makes a list of cities for the given state, and we thus require the city to have a parent that is not in any of these states.

user_675790
Related