Join Queries in Peewee

Question

Hello Stackoverflow Community,

I have just started working on peewee application and little stuck with join queries. Currently, I have following models setup :

class Equipment(BaseModel):
    equip_id = CharField(primary_key=True)
    is_fullset = BooleanField(default=False)


class Department(BaseModel):
    dept_id = IntegerField(primary_key=True)
    dept_name = CharField()


class User(BaseModel):
    email = CharField(primary_key=True)
    equip_id_fk = ForeignKeyField(Equipment, related_name="equipments", null=True)
    dept_id_fk = ForeignKeyField(Department, related_name="departments")
    user_name = CharField()

I want to display a view/table that will hold combined information of all the table. I tried doing this like this for User and Equipment (for now) :

  query = (Equipment.select(Equipment, User).join(User, JOIN.LEFT_OUTER))

Here I get equipment values first and then User. I tried changing User with Equipment and vice versa, but didn't work out well. Basically, I want to display all these three tables into one singular table in this order:

User Name || User Email || Dept_ID || Dept_NAME || EQUIP_ID || Is_Fullset

Please let me know if I need to change my models or query. Any help would be really appreciated.

score 2 · Accepted Answer · answered Nov 14 '17 at 17:14

2

query = (User
         .select(User, Equipment, Department)
         .join(Equipment, JOIN.INNER)
         .switch(User)
         .join(Department, JOIN.INNER))
for row in query:
    print(row.user_name, row.email, row.dept_id_fk.dept_id,
          row.dept_id_fk.dept_name, row.equip_id_fk.equip_id,
          row.equip_id_fk.is_fullset)

answered Nov 14 '17 at 17:14

coleifer

24,887
6
60
75

Thanks alot @coliefer , that worked. I had a question related to it. Some of my user instance has null foreign key and I dont think above suggested query would work for it.. For eg, this is how I am creating new user instance will null foreign key: `user = User.create( email=request.form['email_id'], user_name=request.form['user_name'], dept_id_fk =request.form['dropdown_depts'], equip_id_fk=None )` – aak Nov 29 '17 at 01:22
Secondly, when I try to edit one of the user instance with equipment id, it does show new equipment id on the list but it doesn't update is_fullset(boolean) using your suggested query. `equip_id = request.form['radioID'] Equipment.create( equip_id=equip_id, is_fullset=request.form["radioType"] ) user = User.select().where(User.email == email_id).get() user.equip_id_fk = equip_id user.save()` Any help on that would be really appreciated :) – aak Nov 29 '17 at 01:26

Join Queries in Peewee

1 Answers1