Peewee: `object has no attribute` error on join

[skatenerd]

Suppose I have a many-to-one relationship between products and departments, where both tables have a joinrow_id column. Suppose that we only have one product and its name is "chair". The chair is in the only department, whose name is "all products", and whose "priority" is null.

I'm seeing inconsistent behavior between these two queries:

builder = Products.select(Products.name,                                                                                                                               
                          Departments.name)\
                  .join(Departments, on=(Departments.joinrow == Products.joinrow).alias('department'))\
                  .switch(Products)\
                  .where(Products.id == 1)

builder = Products.select(Products.name,                                                                                                                               
                          Departments.priority)\
                  .join(Departments, on=(Departments.joinrow == Products.joinrow).alias('department'))\
                  .switch(Products)\
                  .where(Products.id == 1)

In the first case, seemingly because the name field is truthy, I am able to type: [r for r in builder][0].department.name

But in the second case, if I try [r for r in builder][0].department.priority, the library raises an error:
Products has no attribute 'department'.

This is easily remedied. I just have to make sure that, in my select, I include a column from departments whose value is always truthy.

I just wanted to bring this behavior to your attention in case you weren't aware.

[coleifer]

Is the joinrow a ForeignKeyField? Which model is the foreign-key on?

[skatenerd]

It's an IntegerField on both tables

[coleifer]

I tried to replicate it, but it seems to be working correctly in my test:

class PetType(TestModel):
    name = TextField()
    flags = IntegerField(default=0)
    type_id = IntegerField()

class Pet(TestModel):
    name = TextField()
    pet_type = ForeignKeyField(PetType)
    type_id = IntegerField()

class TestJoinFalseyRelatedModels1756(ModelTestCase):
    requires = [PetType, Pet]

    def test_1756(self):
        cat = PetType.create(name='cats', flags=0, type_id=1)
        dog = PetType.create(name='dogs', flags=1, type_id=2)
        Pet.create(name='huey', pet_type=cat, type_id=1)
        Pet.create(name='zaizee', pet_type=cat, type_id=1)
        Pet.create(name='mickey', pet_type=dog, type_id=2)

        query = (Pet
                 .select(Pet.name, PetType.flags)
                 .join(PetType, on=(Pet.pet_type == PetType.id).alias('kind'))
                 .order_by(Pet.name))
        with self.assertQueryCount(1):
            self.assertEqual([(p.name, p.kind.flags) for p in query],
                             [('huey', 0), ('mickey', 1), ('zaizee', 0)])

        query = (Pet
                 .select(Pet.name, PetType.flags)
                 .join(PetType,
                       on=(Pet.type_id == PetType.type_id).alias('kind'))
                 .order_by(Pet.name))
        with self.assertQueryCount(1):
            self.assertEqual([(p.name, p.kind.flags) for p in query],
                             [('huey', 0), ('mickey', 1), ('zaizee', 0)])

[coleifer]

Or I suppose I need to change the "flags=0" in the test, to "flags=None" ?

[skatenerd]

I think to recreate the bug, you need a third table

[skatenerd]

I'm on Peewee 3.7.0 FWIW

[skatenerd]

To clarify, the generated SQL looks like:
('SELECT "t1"."join_row_id", "t2"."name" FROM "products" AS "t1" INNER JOIN "departments" AS "t2" ON ("t2"."join_row_id" = "t1"."join_row_id") WHERE ("t1"."id" = %s)', [1])

Before you invest any time in this, I'll assemble a program that looks like the one you pasted above, just so there's no ambiguity in recreating the issue.

[skatenerd]

I've created a self-contained example here:

https://gist.github.com/skatenerd/45b65a361384869e39ee20069d8ea7d4

On my machine, I'm able to make the first print statement in go(), but the second crashes with AttributeError: 'Products' object has no attribute 'department'

[coleifer]

To replicate, this is all I needed:

class Animal(TestModel):
    name = TextField()
    flags = IntegerField(null=True)

class Pet(TestModel):
    name = TextField()
    animal = ForeignKeyField(Animal)

cat = Animal.create(name='cat', flags=0)
dog = Animal.create(name='dog', flags=None)
huey = Pet.create(name='huey', animal=cat)
mickey = Pet.create(name='mickey', animal=dog)

base_query = (Pet
              .select(Pet.name, Animal.flags)
              .join(Animal)
              .order_by(Pet.name))

# Works, prints "huey 0".
for pet in base_query.where(Pet.name == 'huey'):
    print(pet.name, pet.animal.flags)

# raises DoesNotExist
for pet in base_query.where(Pet.name == 'mickey'):
    print(pet.name, pet.animal.flags)

What's going on is a side-effect of the way relational databases use NULL to signal missing data. Consider if the Pet.animal foreign-key was nullable and we wanted to list all pets and their animal-type name. If the "pet" had an animal relationship, then the name would be displayed. If no animal was associated, though, we would see NULL for the animal name.

This NULL could signify either that the Animal's name was NULL, or that the Pet has no related Animal object. In the case that the Pet has no related Animal row, Peewee has a choice between setting the row's "animal" object to None, or a "blank"/unpopulated Animal instance. Peewee does the latter if-and-only-if all the columns selected from a given model are NULL.

So, in your example, I believe it is the fact that Department.priority is NULL and since it is the only column selected from that table in your query, Peewee cannot tell whether the "Product" has a department with a null priority, or if it does not have a department at all (since all the values we selected from the department table are NULL).

This is not a bug per-se, but a trade-off between representing missing related objects as "blank"/"empty" objects versus assuming the related object is None if all of the columns we selected from it are also NULL.

[coleifer]

For your example, you should be able to workaround the "NULL" priority by also selecting the Department's primary key, e.g.

builder = (Products
           .select(Products.name, Departments.id, Departments.priority)
           .join(Departments, on=(Departments.joinrow == Products.joinrow).alias('department'))
           .where(Products.id == 1))