Peewee return all objects not referenced by foreign key

Question

I have models for Coordinate and Weather events. Coordinate objects do not reference weather events but each Weather object has a ForeignKeyField to a Coordinate. I would like to return all of the Coordinate objects that are not referenced by a foreign key in the Weather model.

I imagine there is a single query to to this but the following is the closest I have come and illustrates what I am trying to achieve:

coords = Coordinate.select()
no_weather = Coordinate.select().join(Weather).where(~(Weather.coordinate << coords))

I am hoping I am close since leaving out the "~" does return all of the Coordinate objects associated with Weather objects; I just want the reverse.

Answer 1

There are a lot of ways you could do this. Here's one:

(Coordinate
 .select()
 .join(Weather, JOIN.LEFT_OUTER)
 .group_by(Coordinate)
 .having(fn.COUNT(Weather.id) == 0))

Maybe also:

(Coordinate
 .select()
 .where(~fn.EXISTS(
     Weather
     .select()
     .where(Weather.coordinate == Coordinate.id))))