Quering latest entries wtih specific values.

Using Django Using the ORM
1

Count not figure out a good title for the question.

I have the following model where each host’s current list of packages are saved. Say there are three hosts, hostA, hostB and hostC and we are updating the latest packages from the hosts a few times daily via HostDetails.

Now, for a package libc-bin with version 0.1.0 was there till yesterday and today both hostB and hostC got updated to libc-bin with version 0.2.0. Today’s latest entry for hostA still has libc-bin with version 0.1.0.

class Host(models.Model):
    hostname = models.CharField(max_length=200)

 
class Package(models.Model):
    name = models.CharField(max_length=255)
    version = models.CharField(max_length=255)

    class Meta:
        unique_together = ("name", "version")


class HostDetails(models.Model):
    time = models.DateTimeField()
    host = models.ForeignKey(Host, on_delete=models.CASCADE)
    packages = models.ManyToManyField(Package)

I want to query all the hosts which still have libc-bin version 0.1.0 (answer hostA). How can I do this?

2

Hi Kushaldas,

I’m not the best at this, and there are other guys on here that are like Django masters, so my answer is probably not best way, but ill give it a go.

I think you are asking to return any host in HostDetails where the Package Name and Version are libc-bin and 0.1.0

    version_check = HostDetails.objects.filter(
        packages__name = 'libc-bin', 
        packages__version='0.1.0'
    ).order_by('-time')    

    for item in version_check:
        print(item.host.hostname)
        print(item.time)
        print(item.packages.all())
3

I am trying to ask for the hosts where in the latest hostdetails (for that host) name is libc-bin and version is 0.1.0.

4

Ok, understood.

I think this gets what you need. There is probably a much simpler way.

latest_host_details_subquery = HostDetails.objects.filter(
    host=OuterRef('pk')
).order_by('-time').values('pk')[:1]


hosts_with_package = Host.objects.filter(
    hostdetails__pk__in=Subquery(latest_host_details_subquery),
    hostdetails__packages__name='libc-bin',
    hostdetails__packages__version='0.1.0'
).distinct()
2 Likes
5

YES!!, can you please explain me a bit more? I guess I will have to read through Subquery, never used that before.

6

If it were me, it would be like this.

a = HostDetails.objects.filter(packeages__name='libc-bin').values_list('host_id', flat=True)
b = Host.objects.filter(pk__in=a)
c = HostDetails.objects.fiter(host_id=OuterRef('pk')).order_by('-time')
d = b.annotate(
  version=Subquery(c.values('packages__version')),
  time=Subquery(c.values('time'))
).filter(version='0.1.0')
for i in d: print(i.__dict__)