Peewee inner join returns None on the FK and does not include requested attributes-CodePudding

I'm using PostgreSQL as my DB server.

This inner join returns None on the FK ('search_page_id': None) and does not include the requested attribute from the inner join (.select(Search_Page.priority)):

q=Product_Page.select(Product_Page, Search_Page.priority).join(Search_Page).where(Search_Page.priority == 1).limit(1)

vars(q[0])
{'__data__': {'id': 1,
  'search_page_id': None,
  'desc': 'product description',
  'status': 'TODO'},
 '_dirty': set(),
 '__rel__': {'search_page_id': <Search_Page: None>}}

But if I check what sql the query above is executing and execute the sql myself, you can see that the FK is not None, and the priority attribute is there too (the last one)!

q.sql()
('SELECT "t1"."id", "t1"."search_page_id", "t1"."desc", "t1"."status", "t1"."apiresponse", "t2"."priority" FROM "product_page" AS "t1" INNER JOIN "search_page" AS "t2" ON ("t1"."search_page_id" = "t2"."id") WHERE ("t2"."priority" = %s) LIMIT %s', [1, 1])

e=pg_db.execute_sql(q.sql()[0] % tuple(q.sql()[1]))

e.fetchall()[0]
(1,
 132627,
 'product description',
 'TODO',
 1)

Here are my tables for refrence:

class BaseModel(Model):
    class Meta:
        database = pg_db
class Search_Page(BaseModel):
    id = AutoField()
    desc = CharField(max_length=512)
    status = CharField(max_length=50, choices=[('TODO', 'todo'), ('DONE', 'done'), ('PENDING', 'pending')])
    priority = IntegerField()
class Product_Page(BaseModel):
    id = AutoField()
    search_page_id = ForeignKeyField(Search_Page, backref="products", null=True)
    desc = CharField(max_length=512)
    status = CharField(max_length=50, choices=[('TODO', 'todo'), ('DONE', 'done'), ('PENDING', 'pending')])

I double checked and even though search_page_id is nullable, I don't have ANY null values on this attribute on my DB.

Why does executing the raw sql query work expectedly but using the peewee query syntax returns unexpected results and how can I fix that?

CodePudding user response：

That is just the repr. The priority attribute should be stored:

q = (Product_Page
     .select(Product_Page, Search_Page.priority)
     .join(Search_Page)
     .where(Search_Page.priority == 1)
     .limit(1))
for pp in q:
    print(pp.id, pp.search_page_id.priority)

If you want all attrs on the outer model:

q = (Product_Page
     .select(Product_Page, Search_Page.priority)
     .join(Search_Page)
     .where(Search_Page.priority == 1)
     .objects()  # Put priority on the Product_Page model.
     .limit(1))
for pp in q:
    print(pp.id, pp.priority)

This is covered in detail in the docs: http://docs.peewee-orm.com/en/latest/peewee/relationships.html#selecting-from-multiple-sources