How to do left outer join with PeeWee and no ForeignKey?

Question

Using PeeWee on top of SQLite, I am trying to do a left outer join between two tables that do not have a ForeignKey relation defined. I can get the data if the right table an entry that matches the left table, but if there is no match, the columns in the right table do not make it into the returned models.

class BaseModel(Model):
    class Meta:
        database = db

class Location(BaseModel):
    location_key = CharField(primary_key=True)
    lat = FloatField(null = False)
    lon = FloatField(null = False)


class Household(BaseModel):
    name = CharField(null=True)
    location_id = CharField(null=True)

I am trying to do something like:

    for h in Household.select(Household,Location).join(Location, on=(Household.location_id == Location.location_key), join_type=JOIN.LEFT_OUTER):
        print(type(h), h, h.location, h.location.lat)

This works if Household.location_id matches something in Location, but if Household.location_id is None (null), then I get an AttributeError: 'Household' object has no attribute 'location'

I would have expected location to be present, but have a valid of None.

How can I check for the existence of location before using it? I am trying to avoid using ForeignKey, there are a lot of mismatches between Household.location_id and Location.location_key and PeeWee really gets angry about that...

Answer 1

I think I understand what you're trying to do after re-reading. What I'd suggest is to use Peewee's "on" keyword argument in the join, which can patch the related Location (if it exists) onto a different attr than "location":

query = (HouseHold
         .select(HouseHold, Location)
         .join(Location, on=(HouseHold.location_id == Location.location_key),
               attr='location_obj', join_type=JOIN.LEFT_OUTER))

Then you can check the "location_obj" to retrieve the related object.

for house in query:
    # if there was a match, get the location obj or None.
    location_obj = getattr(house, 'location_obj', None)
    # the location_id is still present.
    print(house.location_id, location_obj)