When joining two tables using a foreign key, the query result for the foreign key field does not meet expectations.

[willow-yang]

When joining two tables using a foreign key, the query result for the foreign key field does not meet expectations.

from peewee import *
from playhouse.pool import PooledPostgresqlExtDatabase

db = PooledPostgresqlExtDatabase(
    database='database',
    host='127.0.0.1',
    port='5432',
    user='user_name',
    password='password',
)

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

    id = AutoField()
    name = CharField(null=True)

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

    id = AutoField()
    teacher = ForeignKeyField(Teacher, column_name='teacher_id')

db.drop_tables([Teacher, Student])
db.create_tables([Teacher, Student])
teachers = [
    {'id': 1, 'name': "A", },
    {'id': 2, 'name': None, },
    {'id': 3, 'name': "C", },
]
students = [
    {'id': 1, 'teacher_id': 1},
    {'id': 2, 'teacher_id': 2},
    {'id': 3, 'teacher_id': 3},
]
Teacher.insert_many(teachers).execute()
Student.insert_many(students).execute()

# When I use different queries, the results for q.teacher_id do not meet expectations.
# ------------------query--------------------------------------------
# only select Student.teacher_id, the results are incorrect.
print('==========================query_teacher_id====================================')
query_teacher_id = (Student.select(Student.teacher_id)
                    .join(Teacher, on=(Student.teacher_id == Teacher.id)))
for q in query_teacher_id:
    print(f'teacher_id: {q.teacher_id}')

print('==========================select with Teacher.name====================================')
# When I add Teacher.name to the SELECT statement, the results are incorrect.
query_with_teacher_name = (Student.select(Student.teacher_id,
                                          Teacher.name)
                           .join(Teacher, on=(Student.teacher_id == Teacher.id)))
for q in query_with_teacher_name:
    print(f'teacher_id: {q.teacher_id}, name:{q.teacher.name}')

print('==========================select with Teacher.id Teacher.name====================')
# When I add Teacher.id to the SELECT statement, the results become correct again.
query_with_teacher_id_name = (Student.select(Student.teacher_id,
                                             Teacher.id,
                                             Teacher.name)
                              .join(Teacher,
                                    on=(Student.teacher_id == Teacher.id)))
for q in query_with_teacher_id_name:
    print(f'teacher_id: {q.teacher_id}, name:{q.teacher.name}')

print('==========================join with alias=================================')
# Alternatively, when I change an attribute during the join, the results are also correct.
query_with_attr = (Student.select(Student.teacher_id,
                                  Teacher.name)
                   .join(Teacher, attr='t',
                         on=(Student.teacher_id == Teacher.id)))
for q in query_with_attr:
    print(f'teacher_id: {q.teacher_id}, name:{getattr(q, "t", None) and q.t.name}')

the output is

==========================query_teacher_id====================================
teacher_id: 1
teacher_id: 2
teacher_id: 3
==========================select with Teacher.name====================================
teacher_id: None, name:A
teacher_id: 2, name:None
teacher_id: None, name:C
==========================select with Teacher.id Teacher.name====================
teacher_id: 1, name:A
teacher_id: 2, name:None
teacher_id: 3, name:C
==========================join with alias=================================
teacher_id: 1, name:A
teacher_id: 2, name:None
teacher_id: 3, name:C

[coleifer]

What is happening is when you are selecting the Student.teacher_id and Teacher.name only, Peewee is creating two models for each row in the database: a Student and it's associated Teacher instance. When it applies the Teacher instance to the Student, the Teacher "id" was not selected explicitly and is None. When setting the Student.teacher attribute (which has an empty primary-key), this in turn sets the Student.teacher_id to None. This is a sort-of a bug, but the workarounds are easy:

• Select the related object primary key (Teacher.id).
• Use .objects() to flatten the data.

[willow-yang]

thanks!