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.

score 25 · Accepted Answer · answered Feb 28 '14 at 19:21

25

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

answered Feb 28 '14 at 19:21

coleifer

24,887
6
60
75

3

Thanks, I missed the switch in the documentation. – Atul Bhatia Mar 04 '14 at 09:43
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. – OzzyTheGiant Jan 18 '17 at 21:01

score 1 · Answer 2 · answered Feb 25 '14 at 15:20

1

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

answered Feb 25 '14 at 15:20

coleifer

24,887
6
60
75

Event is not a foreign key on TicketCategory. Event is a foreign key on Ticket (as is TicketCategory). – Atul Bhatia Feb 25 '14 at 21:55
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 – Atul Bhatia Feb 25 '14 at 22:08

Python peewee joins multiple tables

2 Answers2