tortoise-orm icon indicating copy to clipboard operation
tortoise-orm copied to clipboard

annotate use doubts

Open zhangyuxin123 opened this issue 4 years ago • 6 comments

@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!

zhangyuxin123 avatar Jan 20 '21 01:01 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!

You probably want to use .first()

dstlny avatar Jan 23 '21 19:01 dstlny

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}]

hongquan avatar Jan 24 '21 04:01 hongquan

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.

zhangyuxin123 avatar Jan 24 '21 08:01 zhangyuxin123

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]

hongquan avatar Jan 24 '21 09:01 hongquan

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.

zhangyuxin123 avatar Jan 24 '21 09:01 zhangyuxin123

@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.

hongquan avatar Jan 24 '21 09:01 hongquan