I would like to express that query through Django orm

My project have 4 models

stock/models.py

class StockIndex(models.Model):
  name = models.CharField(max_length=128)
  
  def __str__(self):
    return self.name

class Stock(models.Model):
  code = models.CharField(max_length=24)
  name = models.CharField(max_length=128)
  price = models.DecimalField(max_digits=10, decimal_places=2)
  dividend_rate = models.DecimalField(decimal_places=2, max_digits=4)
  index = models.ForeignKey(StockIndex, related_name='stocks', on_delete=models.SET_NULL, null=True)
  
  def __str__(self):
    return self.name

class StockSet(models.Model):
  code = models.ForeignKey(Stock, related_name='stock_set', on_delete=models.CASCADE)
  quantity = models.IntegerField()
  owner = models.ForeignKey(User, on_delete=models.CASCADE, related_name='stock_set', to_field='wallet')
  
  def __str__(self):
    return f'{self.owner} - {self.code}'

users/models.py

class User(AbstractBaseUser, PermissionsMixin):
  
  objects = UserManager()
  
  username = models.CharField(
      max_length=150,
      unique=True,
  ) 
  email = models.EmailField(blank=True)
  is_staff = models.BooleanField(
    default=False,
  )
  is_active = models.BooleanField(
    default=True,
  )
  wallet = models.UUIDField(unique=True, default=uuid.uuid4, editable=False)
  
  EMAIL_FIELD = "email"
  USERNAME_FIELD = "username"
  REQUIRED_FIELDS = ["email"]
  
  def __str__(self):
    return self.username

All I want is to get each user's assets.

The way to evaluate users' assets is to multiply the StockSet object by the Stock Price and the quantity field and add it all.

The database use is mariadb and I know how to get the value from the query.

SELECT u.username, u.email, SUM(st.quantity * s.price) AS user_asset 
FROM stock_stockset st JOIN users_user u ON (st.owner_id = u.wallet) 
JOIN stock_stock s ON (st.code_id = s.id) 
GROUP BY u.username;

But I don't know how to express the above query in Django ORM.

class UserView(ListAPIView):
  queryset = ?
  serializer_class = UserSerializer

You annotate with:

from django.db.models import F, Sum


class UserView(ListAPIView):
    queryset = User.objects.annotate(
        user_asset=Sum(F('stock_set__code__price') * F('stock_set__quantity'))
    )
    serializer_class = UserSerializer

and add the user_asset field to the serializer:

class UserSerializer(serializers.ModelSerializer):
    user_asset = serializers.FloatField()

    class Meta:
        model = User
        fields = ['username', 'email', 'user_asset']
from django.db.models import Sum, F

User.objects.annotate(user_asset=Sum(F('StockSet__quantity') * F('Stock__price'))).values('username','email', 'user_asset')
Back to Top