CODEWITHSUNDEEP
djangodatabasedjango-modelsormpython-3.7
N.Omugs
N.Omugs

Reputation: 311

Django: Get last record by ID [many-to-one relationship]

I'm trying to get the last record in one table connected to another one using many-to-one relationship in django models. Here's my django models:

class DataCollecttion(models.Model):
    default_name = models.CharField(max_length=100)

class NameHistory(models.Model):
    old_name = models.CharField(max_length=100)
    collection_data = models.ForeignKey(DataCollection, on_delete=models.CASCADE, null=True)

Here I created a sample data for DataCollection table:

enter image description here

And here's the sample data for NameHistory table:

enter image description here

What I want here is to filter or get the last record in NameHistory in each collection_data_id (the records inside the red rectangle) and display it in my views. So in short I want to get these lines and how can I do it in ORM Query:

sample3
test2
data1

Upvotes: 2

Views: 2759

Answers (2)

dani herrera
dani herrera

Reputation: 51745

Do you need a window function:

Window functions provide a way to apply functions on partitions. Unlike a normal aggregation function which computes a final result for each set defined by the group by, window functions operate on frames and partitions, and compute the result for each row.

For your schema design:

from django.db.models import F, Window
from django.db.models.functions.window import FirstValue

( DataCollecttion
  .objects
  .annotate(
     first_old_name=Window(
        expression=FirstValue('namehistory__old_name'),
        partition_by=[F('id'), ],
        order_by=F('namehistory__id').desc()
     )
   )
  .values_list('first_old_name', flat=True)
  .distinct()
 )

This should return the expected list.

Upvotes: 2

Zeeshan Siddiqui
Zeeshan Siddiqui

Reputation: 196

Try this-

from django.db.models import Max
NameHistory.objects.values('old_name').annotate(Max('id'))

It will return the largest id (latest) of the NameHistory Table.

Upvotes: 1

Related Questions