Django: get unique together fields and count how many times they appear in a table

Question

I am trying to create a trip monitoring app whereas each trip is given a ticket and particular origin and destination. What I want to do now is that given a date range, I would like to know how many trips are made for the unique origin-destination pair.

I have not found solutions to this yet and I'm hoping that somebody could enlighten me on this.

Here is my models:

class Location(models.Model)
    name = models.Charfield(max_length=50, unique=True)
    ...

class Trip(models.Model):
    ticket = models.PositiveIntegerField(primary_key=True, blank=False, null=False)
    origin = models.ForeignKey(Location, on_delete=models.CASCADE)
    destination = models.ForeignKey(Location, on_delete=models.CASCADE)
    ...

I am expecting the following output which I currently don't know what to do:

Origin   | Destination | Number of Trips
Place #1 | Place #2    | 5
Place #2 | Place #1    | 3
Place #3 | Place #1    | 8
Place #3 | Place #2    | 1

`Trip.objects.values('origin', 'destination').distinct().annotate(trips=Count('origin'))` ? — art, May 02 '18 at 08:43

score 1 · Accepted Answer · answered May 02 '18 at 09:05

1

Let me post the answer from the comment...

Trip.objects.values('origin', 'destination').distinct().annotate(trips=Count('origin'))

answered May 02 '18 at 09:05

art

1,358
1
10
24

score 0 · Answer 2 · answered May 02 '18 at 08:51

Try with:

query = Trip.objects.values('origin', 'destination').annotate(count=Count('pk'))
for trip in query:
    print(trip['origin'], trip['destination'], trip['count'])

If you want to get the names of the locations instead of the ids:

query = Trip.objects.values('origin__name', 'destination__name').annotate(count=Count('pk'))
for trip in query:
    print(trip['origin__name'], trip['destination__name'], trip['count'])

This is called aggregation in Django docs, you can find many more examples there: https://docs.djangoproject.com/en/2.0/topics/db/aggregation/

thanks @JoseKilo for additional input! will read through the link you have provided! — Josh21, May 02 '18 at 09:14

Django: get unique together fields and count how many times they appear in a table

2 Answers2