CODEWITHSUNDEEP
pythondjango
Radolino
Radolino

Reputation: 1844

How to aggregate a custom model function when it's not a part of the database in django?

I have a project that I need to open and close tickets. So, this is my Ticket model:

class Ticket(models.Model):
    issue = models.CharField(max_length=100)
    user = models.ForeignKey('Users', blank=True, null=True, related_name="tickets")
    date_opened = models.DateTimeField('Date opened')
    date_closed = models.DateTimeField('Date closed', blank=True, null=True)

    def __str__(self):
        return self.issue

    def time_to_solve(self):
        time_to_solve = self.date_opened - self.date_closed
        out = [ time_to_solve.hours//60 ]
        return '{:d} hours'.format(*out)

and I want to calculate the average of the time difference between date_opened and date_closed.

In my views.py I have created a view :

class Dashboard(ListView):
    model = Ticket
    template_name = 'assets/dashboard.html'
    def get_context_data(self, **kwargs):
        context = super(Dashboard, self).get_context_data(**kwargs)
        context['time_to_complete'] = Q(status__contains='closed')).aggregate(time_opened = Avg('time_to_solve'))
        return context

Unfortunately it does not work because "time_to_solve" is not a part of the database.

How can I achieve that?

Upvotes: 0

Views: 193

Answers (3)

Radolino
Radolino

Reputation: 1844

Found the answer from a friend in #irc - django on Freenode:

average = Ticket.objects.extra(
            select={ 'date_difference': 'AVG(time_to_sec(TIMEDIFF(date_closed,date_opened)))'}).first().date_difference
        context['average'] = "{:.2f}".format(average/86400)
        return context

This way it returns the average with 2 decimals accuracy and does everything in the database level so it's much lighter to run than fetching all rows.

Upvotes: 1

NicolasP
NicolasP

Reputation: 764

I don't think you can do that directly with the ORM. You can do it in Python but that will retrieve all closed Ticket rows from the database. If you want to do it in SQL you'll need to express your query as raw SQL. If you're using PostgreSQL you might find this useful : Working with Dates and Times in PostgreSQL.

Upvotes: 1

Germano
Germano

Reputation: 2482

You can only aggregate model fields, but it's not hard to do that in python:

tickets = Ticket.objects.filter(status__contains='closed') 
average = sum(map(lambda x: x.time_to_solve(), tickets)) / tickets.count()

In this case time_to_solve should return something like the number of seconds and you can format that as you need right after that.

Depending on the number of tickets, this might not be the fastest solution. If performance are an issue you might want to use some kind of denormalization.

Upvotes: 1

Related Questions