Peewee - Subqueries

In SQL, a subquery is an embedded query in WHERE clause of another query. We can implement subquery as a model.select() as a parameter inside where attribute of outer model.select() statement.

Example - Create Tables

To demonstrate use of subquery in Peewee, let us use defined following models −

main.py

from peewee import *

db = SqliteDatabase('mydatabase.db')

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

class Contacts(BaseModel):
   RollNo = IntegerField()
   Name = TextField()
   City = TextField()

class Branches(BaseModel):
   RollNo = IntegerField()
   Faculty = TextField()

db.create_tables([Contacts, Branches])
db.close()

After tables are created, they are populated with following sample data −

Contacts table

The contacts table is given below −

Following Insert statements are used to populate data.

insert into contacts(rollno, name, city) values(101,'Anil','Mumbai');
insert into contacts(rollno, name, city) values(102,'Amar','Delhi');
insert into contacts(rollno, name, city) values(103,'Raam','Indore');
insert into contacts(rollno, name, city) values(104,'Leena','Nasik');
insert into contacts(rollno, name, city) values(105,'Keshav','Pune');
insert into contacts(rollno, name, city) values(106,'Hema','Nagpur');
insert into contacts(rollno, name, city) values(107,'Beena','Chennai');
insert into contacts(rollno, name, city) values(108,'John','Delhi');
insert into contacts(rollno, name, city) values(109,'Jaya','Nasik');
insert into contacts(rollno, name, city) values(110,'Raja','Nasik');
insert into branches(rollno,faculty) values(101, 'CSE');
insert into branches(rollno,faculty) values(102, 'MECH');
insert into branches(rollno,faculty) values(103, 'ETC');
insert into branches(rollno,faculty) values(104, 'ETC');
insert into branches(rollno,faculty) values(105, 'MECH');
insert into branches(rollno,faculty) values(106, 'CIVIL');
insert into branches(rollno,faculty) values(107, 'CSE');
insert into branches(rollno,faculty) values(108, 'ETC');
insert into branches(rollno,faculty) values(109, 'CIVIL');
insert into branches(rollno,faculty) values(110, 'ETC');

Example - Using Subquery

In order to display name and city from contact table only for RollNo registered for ETC faculty, following code generates a SELECT query with another SELECT query in its WHERE clause.

main.py

from peewee import *

db = SqliteDatabase('mydatabase.db')

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

class Contacts(BaseModel):
   RollNo = IntegerField()
   Name = TextField()
   City = TextField()

class Branches(BaseModel):
   RollNo = IntegerField()
   Faculty = TextField()
   
#this query is used as subquery
faculty=Branches.select(Branches.RollNo).where(Branches.Faculty=="ETC")
names=Contacts.select().where (Contacts.RollNo .in_(faculty))

print ("RollNo and City for Faculty='ETC'")
for name in names:
   print ("RollNo:{} City:{}".format(name.RollNo, name.City))

db.close()

Output

Above code will display the following result−

RollNo and City for Faculty='ETC'
RollNo:103 City:Indore
RollNo:104 City:Nasik
RollNo:108 City:Delhi
RollNo:110 City:Nasik