How can we calculate average difference of datetime fields from related table in django?-CodePudding

I have two models Gigs and Orders. and want to calculate the average of diffrence between order_start_time and order_completed_time of every gig. check my code its giving following error

Cannot resolve keyword 'orders' into field. Choices are: category, category_id, details, gig, id, images, price, reviews, seller, seller_id, title

please help!

Models.py (in seller app)

from django.db.models import Avg
from django.db.models import F

class Gigs(models.Model):
    title = models.CharField(max_length=255)
    category = models.ForeignKey(Categories , on_delete=models.CASCADE)
    images = models.ImageField(blank=True, null = True, upload_to= upload_path)
    price = models.DecimalField(max_digits=6, decimal_places=2)
    details = models.TextField()
    seller = models.ForeignKey(User,default=None, on_delete=models.CASCADE)

    @property
    def average_completionTime(self):
        if getattr(self, '_average_completionTime', None):
            return self._average_completionTime
        return self.gig.aggregate(Avg(F('orderCompletedTime') - F('orderStartTime')))

I think here the problem is in average completion time how can I use 'order_completed_time'-'order_start_time' in one variable that I should refer in views.py

Models.py(in buyer app)

focus on item field

from seller.models import Gigs
class Orders(models.Model):
    buyer = models.ForeignKey(User,default=None, on_delete=models.CASCADE,related_name='buyer_id')
    seller = models.ForeignKey(User,default=None, on_delete=models.CASCADE,related_name='seller_id')
    item = models.ForeignKey(Gigs,default=None, on_delete=models.CASCADE,related_name='gig')
    payment_method= models.CharField(max_length=10)
    address = models.CharField(max_length=255)
    mobile = models.CharField(max_length=13,default=None)
    quantity = models.SmallIntegerField(default=1)
    status = models.CharField(max_length=13,default='new order')
    orderStartTime = models.DateTimeField(default=None)
    orderCompletedTime = models.DateTimeField(default=None)
    created_at = models.DateTimeField(auto_now_add=True)

Views.py

class RetrieveGigsAPI(GenericAPIView, RetrieveModelMixin):
    def get_queryset(self):
        return Gigs.objects.annotate(
            _average_completionTime=Avg(
                F('gig__orderCompletedTime') - F('gig__orderStartTime')
            )
        )
    serializer_class = GigsSerializerWithAvgTime
    permission_classes = (AllowAny,)

    def get(self, request , *args, **kwargs):
        return self.retrieve(request, *args, **kwargs)

Serializers.py

class GigsSerializerWithAvgTime(serializers.ModelSerializer):
    average_completionTime = serializers.SerializerMethodField()
    def get_average_completionTime(self, obj):
        return obj.average_completionTime
    class Meta:
        model = Gigs
        fields = ['id','title','category','price','details','seller','images','average_completionTime']

CodePudding user response：

You can annotate each gig with its average completion time like this:

    def get_queryset(self):
        return Gigs.objects.annotate(
            _average_completionTime=Avg(
                F('gig__order_completed_time') - F('gig__order_start_time')
            )
        )

In your average_completionTime model method, you also need to use F expressions if self._average_completionTime is not set so:

    @property
    def average_completionTime(self):
        if getattr(self, '_average_completionTime', None):
            return self._average_completionTime
        return self.gig.aggregate(Avg(F('order_completed_time') - F('order_start_time')))

CodePudding user response：

An F() object represents the value of a model field, it refers to model field values and perform database operations using them. for more details, https://docs.djangoproject.com/en/3.2/ref/models/expressions/#f-expressions

from django.db.models import F

let say queryset = Gigs.objects.all()

#you add '_average_completionTime' in the queryset
queryset = queryset.annotate(_average_completionTime=Avg(F('gig__order_completed_time') - F('gig__order_start_time'))