Django - Annotate multiple fields from a Subquery

Question

I'm working on a Django project on which i have a queryset of a 'A' objects ( A.objects.all() ), and i need to annotate multiple fields from a 'B' objects' Subquery. The problem is that the annotate method can only deal with one field type per parameter (DecimalField, CharField, etc.), so, in order to annotate multiple fields, i must use something like:

A.objects.all().annotate(b_id          =Subquery(B_queryset.values('id')[:1],
                         b_name        =Subquery(B_queryset.values('name')[:1],
                         b_other_field =Subquery(B_queryset.values('other_field')[:1],
                         ... )

Which is very inefficient, as it creates a new subquery/subselect on the final SQL for each field i want to annotate. I would like to use the same Subselect with multiple fields on it's values() params, and annotate them all on A's queryset. I'd like to use something like this:

b_subquery = Subquery(B_queryset.values('id', 'name', 'other_field', ...)[:1])
A.objects.all().annotate(b=b_subquery)

But when i try to do that (and access the first element A.objects.all().annotate(b=b_subquery)[0]) it raises an exception:

{FieldError}Expression contains mixed types. You must set output_field.

And if i set Subquery(B_quer...[:1], output_field=ForeignKey(B, models.DO_NOTHING)), i get a DB exception:

{ProgrammingError}subquery must return only one column

In a nutshell, the whole problem is that i have multiple Bs that "belongs" to a A, so i need to use Subquery to, for every A in A.objects.all(), pick a specific B and attach it on that A, using OuterRefs and a few filters (i only want a few fields of B), which seens a trivial problem for me.

Thanks for any help in advance!

Whether the query is inefficient or not depends on how good the DB engine is at spotting the common subexpressions and evaluating them only once. As this is a typical pattern in large SQL queries, one should hope they are pretty good at that. — Lutz Prechelt, Aug 02 '21 at 14:07

score 17 · Accepted Answer · answered Mar 29 '20 at 07:44

17

What I do in such situations is to use prefetch-related

a_qs = A.objects.all().prefetch_related(
    models.Prefetch('b_set',
        # NOTE: no need to filter with OuterRef (it wont work anyway)
        # Django automatically filter and matches B objects to A
        queryset=B_queryset,
        to_attr='b_records'
    )
)

Now a.b_records will be a list containing a's related b objects. Depending on how you filter your B_queryset this list may be limited to only 1 object.

answered Mar 29 '20 at 07:44

Todor

15,307
5
55
62

Sorry for late response. Thank you very much. That is really what i was needing after all. – Emanuel Kozerski Mar 30 '20 at 14:12
Is it possible to use within the same model? – popcorn May 23 '20 at 19:57
1

@popcorn prefetch_related works based on FK/M2M fields, so if you have a self-referential foreign key, yes you should be able to use it within the same model. – Todor May 24 '20 at 06:44
How can you limit the prefetched b_set items? – Nebex Elias Apr 28 '21 at 08:44
1

@NebexElias you cannot set a `b_set` items limit, you can only add more filtration to the `b_queryset` which will result in less `b_set` items. Check [this example](https://stackoverflow.com/a/43926433/2062318) for some more advanced filtration. – Todor Apr 28 '21 at 09:06
But what would a good option to get the top 5 **b_set** items? I tried using Subquery but limit doesn't seem to work with MySql. – Nebex Elias Apr 28 '21 at 09:19
you can use Prefetch() class to customize the query @NebexElias (on the phone, no link, but easily found in docs) – Filipe Pina Jul 07 '21 at 16:07
9

on a different note, this answer is correct as it solves the problem from OP but it does not provide a solution for subquery (which landed me here). For those using Subquery on non-related models, prefetch won’t help… – Filipe Pina Jul 07 '21 at 16:08
@FilipePina have you found a way around on non-related models? I'm stuck with the problem and havent found a solution yet – nicofrlo Feb 09 '22 at 11:58
@nicofrlo you should do the prefetching (aka the "joining") manually by yourself. Run the 1st query, map the objects for a faster lookup, run the 2nd query iterate the objects and use the already created lookup to link them back to the related object from the first query. – Todor Feb 09 '22 at 14:08

Django - Annotate multiple fields from a Subquery

1 Answers1