23

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!

Improve this question
1
  • 1
    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. Commented Aug 2, 2021 at 14:07

1 Answer 1

Reset to default
19

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

@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.
@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 for some more advanced filtration.
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…
@FilipePina have you found a way around on non-related models? I'm stuck with the problem and havent found a solution yet
@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.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.