CODEWITHSUNDEEP
pythondjangodjango-models
Mehdi Zare
Mehdi Zare

Reputation: 1381

Django get values for Max of grouped data

After many trials and errors and checking similar questions, I think it worth asking it with all the details.

Here's a simple model. Let's say we have a Book model and a Reserve model that holds reservation data for each Book.

class Book(models.Model):
    title = models.CharField(
        'Book Title',
        max_length=50
    )

    name = models.CharField(
        max_length=250
    )


class Reserve(models.Model):
    book = models.ForeignKey(
        Book,
        on_delete=models.CASCADE
    )
    reserve_date = models.DateTimeField()

    status = models.CharField(
        'Reservation Status',
        max_length=5,
        choices=[
            ('R', 'Reserved'),
            ('F', 'Free')
        ]
    )

I added a book and two reservation records to the model:

from django.utils import timezone

book_inst = Book(title='Book1')
book_inst.save()

reserve_inst = Reserve(book=book_inst, reserve_date=timezone.now(), status='R')
reserve_inst.save()

reserve_inst = Reserve(book=book_inst, reserve_date=timezone.now(), status='F')
reserve_inst.save()

My goal is to get data for the last reservation for each book. Based on other questions, I get it to this point:

from django.db.models import F, Q, Max


reserve_qs = Reserve.objects.values(
    'book__title'
)

reserve_qs now has the last action for each Book, but when I add .value() it ignores the grouping and returns all the records.

I also tried filtering with F:

Reserve.objects.values(
    'book__title'
).annotate(
    last_action=Max('reserve_date')
).values(

).filter(
    reserve_date=F('last_action')
)

I'm using Django 3 and SQLite.

Upvotes: 1

Views: 94

Answers (2)

Maharshi
Maharshi

Reputation: 232

book_obj = Book.objects.get(title='Book1')
reserve_qs = book_obj.reserve_set.all()

This returns all the Reserves that contains this book.
You can get the latest object using .first or .last() or sort them.

Upvotes: 0

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476557

By using another filter, you will break the GROUP BY mechanism. You can however simply obtain the last reservation with:

from django.db.models import F, Max

Reserve.objects.filter(
    book__title='Book1'
).annotate(
    book_title=F('book__title'),
    last_action=Max('book__reserve__reserve_date')
).filter(
    reserve_date=F('last_action')
)

or for all books:

from django.db.models import F, Max

qs = Reserve.objects.annotate(
    book_title=F('book__title'),
    last_action=Max('book__reserve__reserve_date')
).filter(
    reserve_date=F('last_action')
).select_related('book')

Here we will thus calculate the maximum for that book. Since we here join on the same table, we thus group correctly.

This will retrieve all the last reservations for all Books that are retained after filtering. Normally that is one per Book. But if there are multiple Books with multiple Reservations with exactly the same timestamp, then multiple ones will be returned.

So we can for example print the reservations with:

for q in qs:
    print(
        'Last reservation for {} is {} with status {}',
        q.book.title,
        q.reserve_date,
        q.status
    )

For a single book however, it is better to simply fetch the Book object and return the .latest(..) [Django-doc] reseervation:

Book.objects.get(title='Book1').reserve_set.latest('reserve_date')

Upvotes: 1

Related Questions