CODEWITHSUNDEEP
djangopython-3.xdjango-queryset
AlexW
AlexW

Reputation: 2591

Django Queryset - get the newest record per foreign key

I see a lot of similar queries on but none seem to be working for what im after,

I have a table as such:

class BGPData(models.Model):
    subnet = models.ForeignKey(Subnet, verbose_name="Subnet", on_delete=models.CASCADE, blank=True, null=True)
    bgp_peer_as = models.CharField(max_length=20, verbose_name='BGP Peer AS', blank=True, null=True)
    bgp_session = models.CharField(max_length=10, verbose_name='BGP Session', blank=True, null=True)
    bgp_routes = models.CharField(max_length=10, verbose_name='BGP Routes Received', blank=True, null=True)
    timestamp = models.DateTimeField(auto_now=True, blank=True, null=True)

Objective: I would like to obtain the newest record per foreign key on subnet only.

Attempts thus far below: -

bgp_data_query = BGPData.objects.filter(subnet__get_bgp=True,subnet__wb_bgp=True) \
                                .annotate(last_updated=Cast(TruncSecond('timestamp', DateTimeField()), CharField()))

d = BGPData.objects.annotate(max_timestamp=Max('timestamp')).filter(timestamp=F('max_timestamp'))

Theres something im not quite getting here but im unsure as to what it is...

Thanks

so if my table contains:

subnet_id | routes | timestamp
    1     |   10   | 01-01-20 17:30
    1     |   10   | 01-01-20 17:20
    1     |   10   | 01-01-20 17:10
    2     |   20   | 01-01-20 17:30

I would return 

subnet_id | routes | timestamp
    1     |   10   | 01-01-20 17:30
    2     |   20   | 01-01-20 17:30

Upvotes: 2

Views: 2653

Answers (3)

funnydman
funnydman

Reputation: 11326

What are you looking for is equivalent of this SQL query expressed in Django ORM terms:

SELECT * FROM bgpdata GROUP BY subnet_id HAVING max(timestamp);

This query is elegant and easy to understand, but it looks like that there is no obvious way to implement such a query in Django ORM. Generally speaking, you could write subquery to retrieve all needed columns, something like this:

from django.db.models import Max

BGPData.objects.filter(
    timestamp__in=BGPData.objects.values('subnet').annotate(
        timestamp__max=Max('timestamp')
    ).values('timestamp__max')
)

Upvotes: 2

bdoubleu
bdoubleu

Reputation: 6107

One method using .values() to group by Subnet before annotating with the max timestamp:

from django.db.models import Max

BGPData.objects.values('subnet').annotate(timestamp=Max('timestamp'))

The downside to using this method is that the result is a list of dictionaries which contain only the field keys/values for the fields you specify. This isn't a problem if these fields are the only fields you're looking for.

If you need access to more fields in the BGPData model or from related models then you could use a Window function. Unfortunately as I was completing this I realized window functions aren't allowed in filter so this doesn't work as a solution.

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

data = (
    BGPData.objects
    .annotate(
        max_timestamp=Window(
            expression=Max('timestamp'),
            partition_by=[F('subnet')],
            order_by=F('timestamp').desc(),
        )
    )
    # .filter(timestamp=F('max_timestamp')
)

Upvotes: 5

Slyme
Slyme

Reputation: 131

Try this:

BGPData.objects.filter(timestamp = max(timestamp)).distinct(subnet_id)

Upvotes: -1

Related Questions