CODEWITHSUNDEEP
django
Cristian Flórez
Cristian Flórez

Reputation: 2781

Obtain multiple model associated in a single query according some conditions in Django

I have a Car model which has multiple associations with other models through foreignkeys and manytomany fields, in this case a Car have one Owner and has Many Obligations.

Model relationship

class Car(TimeStampedModel, models.Model):
    owner = models.ForeignKey(
        to=settings.AUTH_USER_MODEL, on_delete=models.CASCADE
    )

class Obligation(TimeStampedModel, models.Model):
    """Representation of the obligations that a car should have up to date."""
    car = models.ForeignKey(to=Car, on_delete=models.CASCADE)
    expiration_date = models.DateField(
        default=date.today,
    )

Expected result

I expect to obtain those cars with expired obligations and from these cars to have their owner and those expired obligations.

What I've tried

1. Obtain those cars with obligation expired

For this task I create a custom Car Manager that retrieve those cars with expired obligations:

class CarManager(models.Manager):
    """Define a manager for Car model."""

    def with_obligations_expired(self) -> "QuerySet[Car]":
        """Returns a Car given where there is at least one Obligation that is expired."""

        return (
            self.get_queryset()
            .filter(obligation__expiration_date__lte=date.today())
            .distinct()
        )

2. Obtain owners of that cars with obligations expired

I run the custom manager method with_obligations_expired() to obtain the cars with obligation expired. Through the result I can access the owners and the obligations of that vehicle.

cars_with_expired_obligations = Car.objects.with_obligations_expired()
owners = [car.owner for car in cars_with_expired_obligations]
obligations = # ...something similar to owners

Problem

With the above code I can obtain the owners and the obligations for that vehicle with expired obligations but separately.

What I expect is a query to return a set of owner and obligations for those cars with expired obligations. This set is necessary, because an email should be sent to those vehicle owners with expired obligations, with those expired obligations as a reminder.

Upvotes: 1

Views: 36

Answers (1)

Iain Shelvington
Iain Shelvington

Reputation: 32244

If you get all Obligations in a queryset and use select_related to also get the related Car and Owner. You can then use itertools.groupby to iterate over a single query while grouping by the Owner

expired_obligations = Obligation.objects.filter(
    expiration_date__lte=date.today()
).select_related('car__owner').order_by('car__owner')

for owner, obligations in itertools.groupby(expired_obligations, lambda obligation: obligation.car.owner):
    print(owner, list(obligations))

Upvotes: 1

Related Questions