Peewee: Foreign key attribute access in join

[bndr]

Hi!

So I've run in to a bit of a problem.

I have models:

   class Trip(AbstractModel):

    date = DateTimeField()
    from_place = ForeignKeyField(Place, related_name="place_from")
    to_place = ForeignKeyField(Place, related_name="place_to")

   class Place(AbstractModel):

    name = CharField(unique=True)
    lat = FloatField()
    lon = FloatField()
    type_ = CharField()

And I need to calculate the great circle distance formula (Get the places around the from_place with predefined radius)

    rLat = math.radians(from_.lat)
    rLon = math.radians(from_.lon)
    earth_radius = 6371  # km
    # Great circle distance formula
    trips = Trip.select().join(Place).where(
        Trip.to_place == to_.id &
        Trip.date >= date &
        Trip.status == "active").where(
            (math.acos(
             math.cos(math.radians(Trip.from_place.lat)) * math.cos(
             rLat) * math.cos(math.radians(Trip.from_place.lon) - rLon)
             + math.sin(math.radians(Trip.from_place.lat)) * math.sin(rLat)) * earth_radius) < radius)

The problem is that I can't access Trip.place_from.lat attribute. I get an error: AttributeError: 'ForeignKeyField' object has no attribute 'lat'

Is there anyway to fix this/do this type of query? Or will I have to write this as raw SQL?

[coleifer]

When you access the lat and lon attrs, you need to do so from the Place model. You need to wrap your comparisons in parens because of operator precedence. Lastly, the usage of the math module won't work at all the way you're trying to use it. You will need to use database functions.

I did not test the code below, but hopefully this helps get you on the right track:

ToPlace = Place.alias()
FromPlace = Place.alias()
trips = (
    Trip
    .select()
    .join(ToPlace, on=(Trip.to_place == ToPlace.id))
    .switch(Trip)
    .join(FromPlace, on=(Trip.from_place == FromPlace.id))
    .where(
        (Trip.date >= date) &
        (Trip.status == 'active') &
        (fn.Acos(
            (fn.Cos(fn.Radians(FromPlace.lat)) * fn.Cos(rLat) * fn.Cos(fn.Radians(FromPlace.lon) - rLon)) +
            (fn.Sin(fn.Radians(FromPlace.lat)) * fn.Sin(rLat)) * earth_radius)) < radius))

[bndr]

Thanks! Your answer helped a lot! I finally got it working.

I'll just write some keywords here, so that people looking for the same thing, could find it: peewee great circle distance haversine formula, two gps points