Python queryto get height price user with count

Question

I have two tables like :

User:

Id | Name | Age |

1 | Pankhuri | 24

2 | Neha | 23

3 | Mona | 25

And another Price log:

Id | type | user_id | price | created_at|

1 | credit | 1 | 100 | 2021-03-05 12:39:43.895345

2 | credit | 2 | 50 | 2021-03-05 12:39:43.895345

3 | debit | 1 | 100 | 2021-03-04 12:39:43.895345

4 | credit | 1 | 100 | 2021-03-05 12:39:43.895345

These are my two tables from where i need to get heighst credit price user with their total price count acoording to last week date..

i want a result like :

like if i want to get result for user id 1 then result should be like: pankhuri on date 04/03 price 100

and on date 5

pankhuri on date 05/03 price 200

want only heighst price user in retirn with their price total on date basisi.

Answer 1

You can either use GROUP BY, ORDER BY data DESC/ASC, MAX or using sub query.

Answer 2

I will assume your Model name is PriceLog, and if I got it right you want to:

1. Filter for records whose type is credit and get records for the last week
2. Group by user_id and create aliased fields: total_price and max_price for each corresponding user

Possible solution would be:

from datetime import datetime, timedelta

from django.db.models import Sum, Count, Max, F

queryset = PriceLog.objects.filter(
    type='credit',
    created_at__gte=(datetime.now() - timedelta(days=7)),  # Filtering for records created in last 7 days
).values(
    'user_id'
).annotate(
    total_price=Sum(F('price')),
    max_price=Max(F('price'))
)

This should return you a QuerySet like this: (According to the data you provided)

<QuerySet [{'user_id': 1, 'total_price': 200, 'max_price': 100}, {'user_id': 2, 'total_price': 50, 'max_price': 50}, ... ]>

Then, to get result for a particular user you can query the queryset with simple .get():

>>> queryset.get(user_id=1)
{'user_id': 1, 'total_price': 200, 'max_price': 100}  # Output