Peewee - Relationships and Joins

Peewee supports implementing different type of SQL JOIN queries. Its Model class has a join() method that returns a Join instance.

M1.joint(m2, join_type, on)

The joins table mapped with M1 model to that of m2 model and returns Join class instance. The on parameter is None by default and is expression to use as join predicate.

Join Types

Peewee supports following Join types (Default is INNER).

  • JOIN.INNER

  • JOIN.LEFT_OUTER

  • JOIN.RIGHT_OUTER

  • JOIN.FULL

  • JOIN.FULL_OUTER

  • JOIN.CROSS

To show use of join() method, we first declare following models −

db = SqliteDatabase('mydatabase.db')

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

class Item(BaseModel):
   itemname = TextField()
      price = IntegerField()

class Brand(BaseModel):
   brandname = TextField()
      item = ForeignKeyField(Item, backref='brands')

class Bill(BaseModel):
   item = ForeignKeyField(Item, backref='bills')
   brand = ForeignKeyField(Brand, backref='bills')
   qty = DecimalField()

db.create_tables([Item, Brand, Bill])

Tables

Next, we populate these tables with following test data −

Item Table

The item table is given below −

Item Table

Brand Table

Given below is the brand table −

Brand Table

Bill Table

The bill table is as follows −

Bill Table

To perform a simple join operation between Brand and Item tables, execute the following code −

qs=Brand.select().join(Item)
for q in qs:
print ("Brand ID:{} Item Name: {} Price: {}".format(q.id, q.brandname, q.item.price))

The resultant output will be as follows −

Brand ID:1 Item Name: Dell Price: 25000
Brand ID:2 Item Name: Epson Price: 12000
Brand ID:3 Item Name: HP Price: 25000
Brand ID:4 Item Name: iBall Price: 4000
Brand ID:5 Item Name: Sharp Price: 12000

Joining Multiple Tables

We have a Bill model having two foreign key relationships with item and brand models. To fetch data from all three tables, use following code −

qs=Bill.select().join(Brand).join(Item)
for q in qs:
print ("BillNo:{} Brand:{} Item:{} price:{} Quantity:{}".format(q.id, \
q.brand.brandname, q.item.itemname, q.item.price, q.qty))

Following output will be displayed, based on our test data −

BillNo:1 Brand:HP Item:Laptop price:25000 Quantity:5
BillNo:2 Brand:Epson Item:Printer price:12000 Quantity:2
BillNo:3 Brand:iBall Item:Router price:4000 Quantity:5
Advertisements