[SOLVED] Django - how to annotate multiple fields from a Subquery?

Django - how to annotate multiple fields from a Subquery?

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!

Solution

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.