Prefetch with custom queryset

[Anssi Kääriäinen]

Loic Bistuer has been working on a patch to allow using custom querysets with prefetch_related(). There was a lot of trying out different APIs. For example the kwargs based API discussed earlier on this mailing list was tried, but it turns out that due to prefetch ordering issues it wasn't actually workable.

The final API is somewhat close to what the original R() object API in ticket #17001 was. Custom prefetches are done with Prefetch objects. Simple prefetches are possible with either Prefetch or the traditional lookup approach:
    Article.objects.prefetch_related('authors') == Article.objects.prefetch_related(Prefetch('authors'))

Custom queryset can be provided. For example this will fetch to authors.all() with different ordering:
    Article.objects.prefetch_related(Prefetch('authors', queryset=Author.objects.order_by('name')))

It is also possible to fetch to a list:
    Article.objects.prefetch_related(Prefetch('authors', queryset=Author.objects.order_by('name'), to_attr='authors_ordered_by_name'))
Now each fetched article will have authors_ordered_by_name list.

Notably when prefetching to a list performance will be up to 2x better compared to prefetching into a queryset (thus solving #20577). OTOH you can't do further queryset operations on the list. But if you want that, then your prefetch will be wasted in any case.

It is also possible to fetch the same relation multiple times to different attrs:
    Article.objects.prefetch_related(
        Prefetch('authors', queryset=Author.objects.order_by('name'), to_attr='authors_ordered_by_name'),
        Prefetch('authors', queryset=Author.objects.order_by('age'), to_attr='authors_ordered_by_age')
    )

Finally, nesting prefetches with custom querysets is possible:
    authors_starting_with_a_with_friends = Author.objects.filter(name__istartswith='a').prefetch_related('friends')
    Article.objects.prefetch_related(
        Prefetch('authors', queryset=authors_starting_with_a_with_friends, to_attr='authors_a'))

Above the 'friends' prefetch could be a Prefetch with custom queryset.

While reviewing this I wondered about multidb support. Should there be automatic .using() call for the provided queryset if it doesn't have explicitly set db? Currently the queryset is used as-is, which can mean it queries different database than the outer queryset. I don't know multidb that well so opinions welcome.

Apart of the multidb issue I consider the patch as ready for checkin.

If you are interested in this feature reviewing is the best way to move this forward. While code and docs reviews are welcome, just trying this out with your use-cases is an excellent way to review the provided functionality.

This issue's ticket is https://code.djangoproject.com/ticket/17001, and pull request can be found from https://github.com/django/django/pull/1826.

 - Anssi