Comparing values on a Foreign Key relationship

Using Django Using the ORM
1

Dear Django Wizards,

I’m hoping someone might be able to steer me in the right direction. I’ve been trying to solve this issue for about three weeks now, it feels like one of those issues where someone much more experienced will come in and solve it in a single line!

I’ve written a custom plugin for Netbox that allows me to track SSL certificates and their status.

I have three models in my application:

Certificate

CertificateInstance

CertificateAuthority

The Certificate model has a status field (active, issued, planned, retired) which tracks the certificate through it’s lifecycle.

The CertificateInstance model has a ForiegnKey relationship defined on it’s ‘certificate’ field with a reverse map called instances. Each instance also has a status field (planned, active, issued, exipred)

I’m trying to create a CertifciateExpiryView which uses a queryset but I’m struggling to create the logic for the queryset.

I need the queryset to return certificates that are status ‘issued’ and where the latest certificate instance doesn’t match the certificate instance for that specific certificates instances.

I’m also unsure whether it’s worth tracking ‘active’ and ‘latest’ instances of each cert in the certificate model.

I’ve tried multiple approaches to the above including:

GeneratedField (doesn’t work across tables)

Attempting to Annotate the active and latest instances into the Certificate model

Creating OneToOneFields from the CertificateInstance model to model both active and latest.;

I have some logic which I think will update the active and latest instances:

def update_instances(self):
        for cert in Certificate.objects.order_by('cn').filter(status="issued"):
            instances = CertificateInstance.objects.order_by('-expiry_date').filter(certificate__cn=cert.cn)
            cert.active = instances.filter(status='active').first()
            cert.latest = instances.first()
            cert.save()

But unsure this works or not.

Here’s the queryset code I’m using at the moment:

    def get_oustanding_certificates():

        # We may restrict by time in the future
        start = timezone.now() - timezone.timedelta(7)
        end = timezone.now() + timezone.timedelta(31)
        now = timezone.now()

        certs = Certificate.objects.order_by('cn').filter(status="issued")

        for cert in certs:
            try:

                if cert.active.ca_reference == cert.latest.ca_reference:
                    certs = certs.exclude(cn=cert.cn)

            except AttributeError:
                pass
        return certs

The code is available here if it makes it easier to understand!: GitHub - NetworkSeb/netbox-certificates at feature-cert-radar-v2

Apologies for the rambling I’m hoping to get a steer on the correct way to model the active and latest instances of the Certificate (on the Certificate model) and what the best way to create the queryset to feed into the view is!

Thanks,

NS

2

Hello, I think something like the following should work to fetch the queryset:

from django.db.models import OuterRef, Subquery, F, Q

def get_outstanding_certificates():
    # latest instance by expiry_date
    latest_instance = CertificateInstance.objects.filter(
        certificate=OuterRef("pk")
    ).order_by("-expiry_date")

    # active instance, take the latest
    active_instance = CertificateInstance.objects.filter(
        certificate=OuterRef("pk"), status="active"
    ).order_by("-expiry_date")

    return (
        Certificate.objects.filter(status="issued")
        .annotate(
            latest_instance_id=Subquery(latest_instance.values("id")[:1]),
            active_instance_id=Subquery(active_instance.values("id")[:1]),
        )
        .exclude(latest_instance_id=F("active_instance_id"))
    )
3

Hi Cliff,

Thanks so much for your response. I’ll give the above a go and let you know how it goes!

NS