[Django]-Django prefetch_related & Prefetch nested

by

5👍

✅

After tons of help from Nicholas Cluade LeBlanc, below is the working query:

UserProfile.objects.annotate(rgs=Count(
            Case(

                When(subscriptions__artist__release_groups__release_date__gte=startdate, then=F('subscriptions__artist__release_groups__release_date')),

                When(subscriptions__artist__release_groups__release_date__lt=startdate, then=None),

                output_field=DateField()
            )
            ))

As Nicholas suggested, subscriptions is the profile related_query_name set in Subscription.

👤zerohedge

5👍

context['test_totals'] = UserProfile.objects.prefetch_related(
    Prefetch(
        'subscription_set', 
        queryset=Subscription.objects.select_related(
            'artist', 'profile').prefetch_related(
                 Prefetch(
                     'artist__release_groups',
                     queryset=ReleaseGroup.objects.filter(
                          release_date__gte=startdate
                     ), 
                     to_attr='release_groups'
                 )
            ), 
        to_attr='subscriptions'
        )
    )

I haven’t had the chance to test this, but it should work. you were using prefetch_related on a foreign key artist which is not supported; prefetch_related is meant for relations to support a list of items. So, you prefetch the subscription_set and use select_related on the artist, then prefetch the artist__release_groups relationship. now you should have profile_instance.subscriptions ...subscriptions[index].artist ...subscriptions[index].artist.release_groups

*EDIT:

After discussion with the OP, we wanted to use this method but the Date filter is not used.

UserProfile.objects.annotate(
    rgs=Count(
        'subscription_set__artist__release_groups',
        filter=Q(subscription_set__artist__release_groups__release_date__gte=startdate),
    distinct=True
    )
)

The real answer is to use django.db.models Case and When as the OP and I found. See his answer for the finished query

👤Nicholas Claude LeBlanc

Leave a comment