peewe join

join_test.py

import os                                                                                                                                                                                                                                                                                                                                                                       

from peewee import CharField, DecimalField, IntegerField, FloatField, DateTimeField, BooleanField, TextField, CompositeKey, DateField, Model, ForeignKeyField
from playhouse.postgres_ext import JSONField, PostgresqlExtDatabase

# Add real values here...
database = PostgresqlExtDatabase()

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


class Joins(BaseModel):
    id = IntegerField(null=True, primary_key=True)
    class Meta:
        db_table = 'joins'

class Products(BaseModel):
    id = IntegerField(null=True, primary_key=True)
    joinrow_id = ForeignKeyField(Joins, db_column='joinrow_id', unique=True)
    name = CharField(null=True)
    class Meta:
        db_table = 'products'

class Departments(BaseModel):
    id = IntegerField(null=True, primary_key=True)
    joinrow_id = ForeignKeyField(Joins, db_column='joinrow_id', unique=True)
    name = CharField(null=True)
    priority = IntegerField(null=True)
    class Meta:
        db_table = 'departments'

def go():
    Products.delete().execute()
    Departments.delete().execute()
    Joins.delete().execute()

    joinrow = Joins.create(id=1)
    Departments.create(id=1, joinrow_id=1, name='clearance')
    Products.create(id=1, joinrow_id=1, name='chair')


    okay_builder = Products.select(Products.name, Departments.name)\
                              .join(Departments, on=(Products.joinrow_id == Departments.joinrow_id).alias('department'))
    print(okay_builder.execute()[0].department.name)                                                                                                                   
    bad_builder = Products.select(Products.name, Departments.priority)\
                              .join(Departments, on=(Products.joinrow_id == Departments.joinrow_id).alias('department'))
    print(bad_builder.execute()[0].department.priority)

schema.txt

  sandbox=# \d joins
               Table "public.joins"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           |          | 

sandbox=# \d products
                Table "public.products"
   Column   |  Type   | Collation | Nullable | Default 
------------+---------+-----------+----------+---------
 joinrow_id | integer |           |          | 
 name       | text    |           |          | 
 id         | integer |           |          | 

sandbox=# \d departments
              Table "public.departments"
   Column   |  Type   | Collation | Nullable | Default 
------------+---------+-----------+----------+---------
 name       | text    |           |          | 
 priority   | integer |           |          | 
 joinrow_id | integer |           |          | 
 id         | integer |           |          |