Python peewee joins multiple tables

foreign-keys, join, peewee, python

Solution

Going to try answering this one more time.

(TicketBooking
 .select(
     TicketBooking, 
     Ticket, 
     TicketCategory, 
     Event)
 .join(Ticket)
 .join(TicketCategory)
 .join(Event)
 .where(
     TicketBooking.user_id == user_id,
     TicketBooking.deleted >> None
 ))

You're almost there. Try instead:

(TicketBooking
 .select(
     TicketBooking, 
     Ticket, 
     TicketCategory, 
     Event)
 .join(Ticket)
 .join(TicketCategory)
 .switch(Ticket)  # <-- switch the "query context" back to ticket.
 .join(Event)
 .where(
     TicketBooking.user_id == user_id,
     TicketBooking.deleted >> None
 ))

Problem

I want to be able to join multiple tables in peewee. However the scenario is a little difficult for me to figure out how to get it to work with peewee. I have the following tables: Ticket TicketCategory TicketBooking Event Here are my models: ``` class TicketCategory(BaseModel): venue_id = IntegerField() name = CharField() description = CharField() class Ticket(BaseModel): event = ForeignKeyField(Event) category = ForeignKeyField(TicketCategory) order_number = IntegerField() tier_name = CharField() num_available = IntegerField() price = DecimalField() class TicketBooking(BaseModel): user_id = IntegerField() ticket = ForeignKeyField(Ticket, related_name="ticketbookings") price_paid = DecimalField() created = DateTimeField() deleted = DateTimeField() class Event(BaseModel): venue_id = IntegerField() date = DateField() event_image_url = CharField() start = TimeField() end = TimeField() ``` Now I want to run a query which will select all the ticketbookings for a given user. After running my join, I want all the info to be loaded -- I don't want another query to be launched when I access ticketbooking.ticket.category.name or ticketbooking.ticket.event.description I cannot just do this: ``` return TicketBooking.select(TicketBooking, Ticket, TicketCategory, Event).join(Ticket).join(TicketCategory).join(Event).where( TicketBooking.user_id == user_id, TicketBooking.deleted >> None ) ``` Because Event is not a foreign key on TicketCategory, so I get an error. Any help would be much appreciated.

Original source