Python peewee joins multiple tables

Question

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.

Brynhild

Answer 1

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
 ))

Reimburse

Thanks, I missed the switch in the documentation. – Brynhild

For anyone wondering how it works with Flask-RESTful, use a similar query like the one above, and once you get your data, marshal() the records into a list. The names of the fields for which to marshal to should be the same names as the column names, without prefixing it with the table name, just be careful if you have columns with the same names across multiple tables, in case you are selecting all columns. – Incurable

Answer 2

Try .join(Event, on=(TicketCategory.venue_id == Event.venue_id))

Reimburse

Event is not a foreign key on TicketCategory. Event is a foreign key on Ticket (as is TicketCategory). – Brynhild

And the reason I want to join on both the event and the ticketcategory is so that I don't launch any new queries when I access ticketbooking.ticket.category or ticketbooking.ticket.event – Brynhild