django prefetch_related & Prefetch nested

Question

I'm trying to return, for each UserProfile, which has one-to-many Subscription, which has a Foreignkey to both Artist and UserProfile, with each artist having many ReleaseGroup, the count of future release groups that each UserProfile have.

In short: I want to return the total count of upcoming releases for all of the subscription that each of the users have.

However I'm getting stuck way before I get to count...

    context['test_totals'] = UserProfile.objects.prefetch_related(
        Prefetch('subscription_set', queryset=Subscription.objects.
                 prefetch_related(Prefetch('artist', queryset=Artist.objects.
                                           prefetch_related(Prefetch('release_groups',
                                             queryset=ReleaseGroup.objects.filter(
                                        release_date__gte=startdate
        ), to_attr='rggg')), to_attr='arti')), to_attr='arts'))

accessing userprofile.arts|length in template returns total number of subscription, but rggg and arti return nothing. How can this be done?

I tried using filtering on self with, say, filter(profile='userprofile)`, but that returns an error. If I could filter on self I could probably get this to work?

Answer 1

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

Answer 2

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.