How to add "annotate" data into query with several "values" with django models?

Question

Example model:

class A(models.Model):
    id = models.CharField(max_length=255, primary_key=True, default=make_uuid, editable=False)
    b = models.IntegerField()

Goal:
I need to get list which would contain id, b and same_b_total.

e.g. following quetyset returns:

a = list(models.Cell.objects.all().values("b").annotate(same_b_total=Count("b")))
print(a)  # [{"b": 1, "same_b_total": 5}, {"b": 2, "same_b_total": 3}]

When I add id into .values("b", "id") it return list with followind data

[{'b': 1, 'id': '<some uuid>', 'same_b_total': 1}, {'b': 1, 'id': '<some uuid2>', 'same_b_total': 1}, {'b': 2, 'id': '<some uuid3>', 'same_b_total': 1}, ...]

How to change query to receive correct same_b_total for each record? Like:

[{'b': 1, 'id': '<some uuid>', 'same_b_total': 5}, {'b': 1, 'id': '<some uuid2>', 'same_b_total': 5}, {'b': 2, 'id': '<some uuid3>', 'same_b_total': 3}, ...]

how b and same_b_total is related? Can you give us some real example scenario? — JPG
– JPG, Commented Apr 8, 2020 at 17:01
@ArakkalAbu added table example. same_b_total reflect total count of same b. There is 5 b with value = 1 and 3 b with value = 2 — Yuriy Leonov
– Yuriy Leonov, Commented Apr 8, 2020 at 17:20

partizaans · Accepted Answer · 2020-07-12 07:35:09Z

Explanation

Using the combination of OuterRef and Subquery is what you are looking for. For a specific integer value for the field of b you need to get the count of all existing rows. By replacing the specific integer value with OuterRef('b') the result will convert to a subquery. (instead of normal queryset)

Note that sub is a subquery and does not exist on its own.

sub = A.objects.filter(b=OuterRef('b')).values('b').annotate(same_b_count=Count('id'))

We need to inject sub into another query. Getting some help from Django docs about combination of Subquery and Outerref results in:

A.objects.annotate(same_b_count=Subquery(sub.values('same_b_count'))).values('id', 'b', 'same_b_count')

Conclusion

Combining two snippets from explanation:

sub = A.objects.filter(b=OuterRef('b')).values('b').annotate(same_b_count=Count('id'))
A.objects.annotate(same_b_count=Subquery(sub.values('same_b_count'))).values('id', 'b', 'same_b_count')

will result in the output of:

<QuerySet [{'id': 1, 'b': 1, 'same_b_count': 3}, {'id': 2, 'b': 1, 'same_b_count': 3}, {'id': 3, 'b': 1, 'same_b_count': 3}, {'id': 4, 'b': 3, 'same_b_count': 1}, {'id': 5, 'b': 4, 'same_b_count': 2}, {'id': 6, 'b': 4, 'same_b_count': 2}, {'id': 7, 'b': 6, 'same_b_count': 1}]>

Collectives™ on Stack Overflow

How to add "annotate" data into query with several "values" with django models?

1 Answer 1

Explanation

Conclusion

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Explanation

Conclusion

Comments

Your Answer

Sign up or log in

Post as a guest

Related