annotate use doubts

[zhangyuxin123]

@hongquan @sinaso @jrmi @allrod5 @charleswhchan hello,

Describe the bug When I use foreign key to filter the database table and then use annotate to get the maximum value, I can't work as expected.

To Reproduce data = await m.WorkInfo.filter(Q(city__name="New York") & Q(position__name="bim")).exclude(Q(minsalary=None) | Q(maxsalary=None)).annotate(max_salary=Max("maxsalary")).values('max_salary') Expected behavior I want to get a list containing only one dict, whose dict content is the maximum value of the field "maxsalary" in all filtering results,For example： [{“max_salary”：135}]

but,What I got was [{“max_salary”：135},{“max_salary”：13},{“max_salary”：100},......]

Hope to get a reply as soon as possible, thank you！

[dstlny]

@hongquan @sinaso @jrmi @allrod5 @charleswhchan hello,

Describe the bug When I use foreign key to filter the database table and then use annotate to get the maximum value, I can't work as expected.

To Reproduce data = await m.WorkInfo.filter(Q(city__name="New York") & Q(position__name="bim")).exclude(Q(minsalary=None) | Q(maxsalary=None)).annotate(max_salary=Max("maxsalary")).values('max_salary') Expected behavior I want to get a list containing only one dict, whose dict content is the maximum value of the field "maxsalary" in all filtering results,For example： [{“max_salary”：135}]

but,What I got was [{“max_salary”：135},{“max_salary”：13},{“max_salary”：100},......]

Hope to get a reply as soon as possible, thank you！

You probably want to use .first()

[hongquan]

Expected behavior I want to get a list containing only one dict, whose dict content is the maximum value of the field "maxsalary" in all filtering results,For example： [{“max_salary”：135}]

but,What I got was [{“max_salary”：135},{“max_salary”：13},{“max_salary”：100},......]

Hope to get a reply as soon as possible, thank you！

Just use Python slice. Take a look:

>>> a = [{'max_salary': 135}, {'max_salary': 13},{'max_salary': 100}]           

>>> a[:1]                                                                       
[{'max_salary': 135}]

[zhangyuxin123]

Sorry, I don't think I have made it clear. I want to get the maximum value from the results filtered out by some conditions. The results should be unique, not multiple, and then sliced.

[hongquan]

Just call order_by:

queryset = WorkInfo.filter(Q(city__name="New York") & Q(position__name="bim")).exclude(Q(minsalary=None) | Q(maxsalary=None)).annotate(max_salary=Max("maxsalary")).values('max_salary')
data = await queryset.order_by('-max_salary')[:1]

[zhangyuxin123]

Sorry, I have used the max function, and the result should be only one. If I have 200 pieces of salary data and use the max function, the result is still 200 pieces, which is obviously wrong.

[hongquan]

@zhangyuxin123 Sorry, I don't have your schema (models), and failed to imagine what you are trying to achieve.

To use aggregate functions like Max, Sum, which operates over a group of data, you have to "group" the data by some column first. For example, you are trying to get maximum salary, then you have to answer "maximum among which dataset"? If you simply just want to get maximum over all the table, not a group of data, you just sort data of the whole table then getting the first record, no need to use annotate.