[SOLVED] How to write select query in django in chain of one-to-many relationship?

How to write select query in django in chain of one-to-many relationship?

How to write select query in django? I have 2 one-to-may relationship

At the beginning, i am not very good at english. i am so sorry :). I have 3 tables. Driver, Car and Ride. The relationship between Driver and Car is (one-to-many: a Driver can have multiple Car. The relationship between Car and Ride is (one-to-many: a Car can have multiple Ride. I want list of drivers who have x times of rides.

My Models:

class Driver(models.Model):
    account = GenericRelation(Account, related_query_name='drivers')

class Car(models.Model):
    owner = models.ForeignKey(Driver, on_delete=None)

class Ride(models.Model):
    car = models.ForeignKey(Car, on_delete=models.CASCADE)
    request = models.OneToOneField(RideRequest, on_delete=models.CASCADE, null=False)

I expect something like this: <QuerySet [<Driver: 1>, <Driver: 2>]>which is Drivers with greater than or equal x Rides

Solution

You can count the number of Rides per Driver, and then filter on that number, like:

from django.db.models import Count

Driver.objects.annotate(
    nride=Count('car__ride')
).filter(nride__gte=x)

We thus first annotate each Driver object with the Count(..) [Django-doc] of related Rides, next we filter that queryset by specifying that the nrides field annotation should be greater than or equal to x, by using the __gte lookup [Django-doc]. Here x is of course the number of required rides you need to fill in (for example 5).