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!