CODEWITHSUNDEEP
djangodjango-modelsdjango-querysetdjango-orm
Philipp Chapkovski
Philipp Chapkovski

Reputation: 2059

Error when trying to use Subquery in calculating time difference

I track how much time people spend on a specific page (I asked several questions regarding this issue here). I register a time when the page is loaded ('Enter' event). And in a separate model I register all 'Exits': when form is submitted, when page is unloaded, etc. This 'ExitEvent' model is connected to EnterEvent model via ForeignKey because one Enter event can and usually does have several corresponding Exit events.

EXITTYPES = [(0, 'form submitted'), (1, 'page unloaded'), (2, 'client disconnected')]


class EnterEvent(models.Model):
    page_name = models.CharField(max_length=1000)
    user = models.ForeignKey(to=User, related_name='enters')
    timestamp = models.DateTimeField()
    closed = models.BooleanField(default=False)


class ExitEvent(models.Model):
    enter_event = models.ForeignKey(to=EnterEvent, related_name='exits')
    timestamp = models.DateTimeField()
    exit_type = models.IntegerField(choices=EXITTYPES)

To calculate time spent by user on a page I use a following code which works correctly:

def get_time_per_page(user, page_name):
    earliest = ExitEvent.objects.filter(enter_event=OuterRef('pk')).order_by('timestamp')
    delta = timedelta(days=1)

    a = EnterEvent.objects.filter(closed=True,
                                  user=user,
                                  page_name=page_name).annotate(
        earliest_exit=Subquery(earliest.values('timestamp')[:1]),
    ).values()
    sum_diff = sum([i['earliest_exit'] - i['timestamp'] for i in a], timedelta())

    return sum_diff

However when I try to use the results of the subquery in the following annotate queries it fails:

b = EnterEvent.objects.filter(closed=True,
                              participant=player.participant,
                              page_name=page_name).annotate(
    earliest_exit=Subquery(earliest.values('timestamp')[:1]),
).annotate(timespent=ExpressionWrapper(F('earliest_exit') - F('timestamp'), output_field=DurationField()))

Error log:

Exception Type: TypeError
Exception Value: can only concatenate tuple (not "list") to tuple
Exception Location: /Users/chapkovski/otree2/lib/python3.6/site-packages/django/db/backends/sqlite3/operations.py in subtract_temporals, line 280

What am I doing wrong?

UPDATE:::

an error is caused when Django tries to subtract two dates, one of which is produced by Subquery. It is specifically caused by the fact that a parameter that comes from 'normal' field is a tuple, and Subquery instead returns a list, so when Django (in subtract_temporals function) tries to sum these two it brings in an error:

 internal_type   'DateTimeField'
 lhs_params  ()
 rhs_params  []

Upvotes: 4

Views: 600

Answers (2)

Garvit Sharma
Garvit Sharma

Reputation: 21

Did this. and it is working.

in Subquery make output_field=DurationField() instead of DateTimeField(). and Add ExpressionWrapper with output_field=DurationField() when Subtracting both fields.

ModelA.objects.filter()\
.annotate(jct=Subquery(ModelB.objects.filter(model_a_id=OuterRef('pk')).values_list('created_at',flat=True)[:1], output_field=DurationField()))\
.annotate(ans=ExpressionWrapper(F('jct') - F('created_at'), output_field=DurationField()))

Upvotes: 2

Vitor Freitas
Vitor Freitas

Reputation: 3610

I faced this issue twice this month, and I ended up coming with a hack that is working for me. I wanted to have the calculation inside the QuerySet so I could benefit from the database ordering, and not have to code a custom ordering when the user wanted to order by the duration field.

So the solution I found was to annotate both columns via Subquery, so the end result would be both columns being internally a list, so Django would successfully concatenate/calculate the duration correctly, without yielding the ValueError exception.

Here is an example using your code:

from django.db.models import Subquery, OuterRef, F, ExpressionWrapper, DurationField

earliest = ExitEvent.objects.filter(enter_event=OuterRef('pk')).order_by('timestamp')
enter_timestamp = EnterEvent.objects.filter(pk=OuterRef('pk')).annotate(enter_timestamp=F('timestamp'))

EnterEvent.objects.filter(closed=True, participant=player.participant, page_name=page_name) \
    .annotate(earliest_exit=Subquery(earliest.values('timestamp')[:1])) \
    .annotate(enter_timestamp=Subquery(enter_timestamp.values('enter_timestamp')[:1])) \
    .annotate(timespent=ExpressionWrapper(F('earliest_exit') - F('enter_timestamp'), output_field=DurationField()))

Upvotes: 3

Related Questions