Django ORM better way to manipulate django ORM query

Question

Below are the models:

class Seat(models.Model):
    hall = models.ForeignKey(Hall,on_delete=CASCADE)
    type = models.TextField(verbose_name="Seat Type")

class Show(models.Model):
    show_time = models.TimeField(verbose_name='Show Time')
    movie = models.ForeignKey(Movie,on_delete=CASCADE)
    hall = models.ForeignKey(Hall,on_delete=CASCADE)
    cinema = models.ForeignKey(Cinema,on_delete=CASCADE)

class Booking(models.Model):
    seat = models.ForeignKey(Seat,on_delete=CASCADE)
    show = models.ForeignKey(Show,on_delete=CASCADE)
    movie = models.ForeignKey(Movie,on_delete=CASCADE)
    hall = models.ForeignKey(Hall,on_delete=CASCADE)
    cinema = models.ForeignKey(Cinema,on_delete=CASCADE)
    user = models.ForeignKey(User, verbose_name="Username", on_delete=DO_NOTHING) # donothing

Explanation of the models:

Multiple Seats in a Hall

Each Hall hosts multiple Shows

Each Booking is basically a ticket for a show with a specific seat and a specific show.

Requirement is to get a queryset that has all seats not present in the bookings table for a specific show. Basically, get the list of available seats for a show by checking they are not present in the Bookings table

The SQL query would look like this:

SELECT * FROM Seat as S join Show as Sh on S.hall = Sh.hall join Bookings as B on B.show = Sh.id where Sh.id = 1 AND B.seat IS NULL

how to convert this to Django ORM:

able to do it like this (is there a better way rather than creating the seat_id_list?):

            qs = Seat.objects.filter(hall=hid) #----------------------------------
            show_bookings = Booking.objects.filter(show=spk)
            seat_id_list = []

            for each_booking in show_bookings:
                print(each_booking.seat.id)
                seat_id_list.append(each_booking.seat.id)
            qss = Seat.objects.exclude(id__in = seat_id_list)

willeM_ Van Onsem · Accepted Answer · 2021-11-05 16:22:12Z

1

You can obtain these Seats with:

Seat.objects.filter(hall=hid).exclude(booking__show=spk)

This will retrieve all Seats for a given Hall object hid where there is no Booking for a Show determined by the spk object.

answered Nov 5, 2021 at 16:22

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

akash deep Over a year ago

Thanks a lot, this worked well. What does booking represent here? it is not a field in the Seat model neither in the show model? also the model is called Booking.

willeM_ Van Onsem Over a year ago

@akashdeep: If you define a ForeignKey, Django defines a relation in reverse. By default it uses as related_query_name the name of the model where you define it in lowercase, so you can access the related bookings in a query with booking.

Collectives™ on Stack Overflow

Django ORM better way to manipulate django ORM query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related