Can this query be expressed in Django?

I have the following set of models (simplified and renamed for the purpose of this question). A lot of other functionality has already been built on top of them so wholesale changes aren't possible, though small tweaks could be.

class Customer(models.Model):
    # Company-specific ID format, used everywhere
    customerId = models.CharField(primary_key=True, max_length=9)
    name = models.CharField()
    email = models.CharField()
    ...

class Trip(models.Model):
    """An event that customers may sign up for - think of a coach tour around 
        Italy, for example."""
    name=models.CharField()
    description=models.CharField()
    ...

class Product(models.Model):
    """Anything that Customers can buy. This could be as significant as a 
        hotel booking, or as small as a branded keyring."""
    # All the Products we're concerned about here have codes, but there do
    # exist in the database some ad-hoc pseudo-products that don't.
    code = models.CharField(unique=True, null=True)
    ...

class Payment(models.Model):
    # Various fields to do with banking, financial tracking, etc. The
    # relevant one for us is:
    is_paid = models.BooleanField(default=False)


class Purchase(models.Model):
    """The fact of a Customer buying a Product"""
    # All purchases are associated with a Booking - only booked Customers 
    # can buy things, as they're either an inherent part of the trip or, for
    # smaller items, handed out during it. 
    booking = models.ForeignKey(Booking, on_delete=models.CASCADE, related_name="purchases")
    product = models.ForeignKey(Product, on_delete=models.PROTECT, related_name="purchases")
    payment = models.ForeignKey(Payment, on_delete=models.SET_NULL, null=True, related_name="purchases")
    ...

class RoomShare(models.Model):
    """Customers on these trips almost always share rooms, usually two per 
        room though some larger rooms hold three people. The system 
        technically supports single-person room "shares" for special cases."""
    # Some fields relating to twins/doubles, accessibility requirements, 
    # special requests, etc. None relevant to this question.


class Booking(models.Model):
    """The fact of a Customer being booked onto a trip. This is created as 
        soon as they express an interest, but isn't confirmed until everybody 
        in the RoomShare has paid."""
    event = models.ForeignKey(Trip, on_delete=models.PROTECT)
    customer = models.ForeignKey(Customer, on_delete=models.CASCADE, related_name="bookings")
    roomShare = models.ForeignKey(RoomShare, on_delete=models.SET_NULL, null=True, default=None, related_name="roommates"
    purchase = models.OneToOneField(Purchase, on_delete=models.SET_NULL, null=True, related_name="bookingPurchase")
    ...

The way this system works is that each person's booking is largely independent, except that in most cases it's tied to one or two others via the RoomShare. The rules are that a Booking is not secured until everybody in the room has paid (bear in mind this is a semi-fictionalised version of the real application, so don't worry if it seems like an odd way to book a holiday).

What I'm trying to query for, is every Customer who:

  • Has a Booking

    • Which is for a given Trip

      and

    • Which has a Purchase

      • Which has a Payment

        • Which is_paid

      and

    • Which has a RoomShare

      • In which every other Booking has a Purchase, which has a Payment, which is_paid

What I'm struggling with is the last line - having gone "down the tree" to the RoomShare, I'm not sure how to "go back up" to all the Bookings associated with it.

I'm sure I could do this fairly straightforwardly in Python, tying together several queries, but the result of this is essentially "the list of paid-up customers who are definitely coming on the trip" so I expect it to form the basis of some more specific queries later ("all the people on the trip who signed up for the Colliseum visit", etc). So I'd like to keep it as a QuerySet in the database if I can, even if it's quite a complex one.

How should I approach this?

Вернуться на верх