Using nested Subqueries

Hello everyone! I’m fairly new to Django and working on my first big-ish project. I’m having a problem trying to use Subquery, but it could very well be that I’m not using it in the intended manner.

To sum it up, I have 3 models involved in the query, I have a “Member” model which I’m trying to list in a view, but I want to add some additional information to each object that I want to display. I have a “Subscription” model, which has a foreign key field to an object of the “Package” model

So, each Member has a Subscription, and can have 0, 1 or many Packages related. Simplified:

class Member(models.Model):
    first_name = models.CharField()
    last_name= models.CharField()

class Subscription(models.Model):
    description = models.CharField()
    activation_date = models.DateField()
    member = models.ForeignKey(to=Member)

class Package(models.Model):
    description = models.CharField()
    period = models.DateField()
    suscription= models.ForeignKey(to=Subscription)

Now, as you can see, every “Package” is related to a Member, not directly, but through a “Suscription” object.

What I want to do is get a particular “Package” object given a “Member” and annotate it to the queryset. But I’m failing to do so. However, if I try to get the Subscription for every Member, it works correctly. Basically, this works, by first getting the suscription, annotating it and then doing a query for the package:

subscription_subquery = Subscription.objects.filter(
    member=OutRef('pk')
).order_by('activation_date')[:1]
# The ordering is to get the latest suscription as there could be many

package_subquery = Package.objects.filter(
    period__year=2024,
    period__month=8,
    susbscription=OutRef('suscription')
)

# This is a Members queryset
queryset.annotate(
    susciption=Subquery(subscription_subquery)
    package=Subquery(package_subquery)
)

But if I try to get the Package directly, I just don’t understand how to make it work. My idea was to do something like this:

subscription_subquery = Subscription.objects.filter(
    member=OutRef('pk')
).order_by('activation_date')[:1]
# The ordering is to get the latest suscription as there could be many

package_subquery = Package.objects.filter(
    period__year=2024,
    period__month=8,
    susbscription=subscription_subquery
)

# This is a Members queryset
queryset.annotate(
    package=Subquery(package_subquery)
)

I did try writing ‘susbscription=Subquery(subscription_subquery)’ instead in the package_subquery, but it seems to do nothing.

Now… it could very well be that the subquery is considering the ‘pk’ in the OuterRef of the subscription_subquery as the pk of the package instead of the pk of the original queryset objects, but I do not know how to do it.

What I want to do is to “nest” a Subquery to get the pk of the original query, is it even possible? Am I just not understanding how any of this works? In any case, thanks to everyone!