Hi Galil,
in this case you'll want to take a look at how `values()` and `annotate()`
interact with
each others[1] in regard to GROUP BY.
The following should do:
Call.objects.annotate(
joindate=Func(F('jointime'), function='DATE')
).values('joindate').annotate( # values before annotate() specifies the
GROUP BY
count=Count('uniquecallid', distinct=True)
)
Cheers,
Simon
[1] https://docs.djangoproject.com/en/1.9/topics/db/aggregation/#values
Le mercredi 15 juin 2016 07:58:55 UTC-4, Galil a écrit :
>
> Thank you Simon,
>
> This func expression what was I looking for. However, some more complex
> queries with the date() function still confuse me.
>
> For example the SQL query:
>
> select count(distinct UniqueCallID), date(JoinTime) from Table group by
> date(JoinTime);
>
>
> I tried to write it as:
>
> calls = Call.objects.annotate(join_date=Func(F('jointime'), function='DATE'),
> count=Count('uniquecallid', distinct=True)).values('join_date', 'count')
>
>
> But it seems that the group by is not working as expected and the values
> returned are not distinct. Am I missing something?
>
>
> On Wednesday, 15 June 2016 04:08:48 UTC+1, Simon Charette wrote:
>>
>> Hi Galil,
>>
>> In the next version of Django (1.10) you'll be able to use the TruncDate
>> expression[1]
>> for this exact purpose:
>>
>> MyModel.objects.annotate(join_date=TruncDate('join_time')).values_list('join_date',
>>
>> flat=True)
>>
>> In the mean time you can simply use a Func expression[2]:
>>
>> MyModel.objects.annotate(join_date=Func(F('join_time'),
>> function='DATE')).values_list('join_date', flat=True)
>>
>> Cheers,
>> Simon
>>
>> [1]
>> https://docs.djangoproject.com/en/1.10/ref/models/database-functions/#django.db.models.functions.datetime.TruncDate
>> [2]
>> https://docs.djangoproject.com/en/1.9/ref/models/expressions/#func-expressions
>>
>> Le mardi 14 juin 2016 09:52:34 UTC-4, Galil a écrit :
>>>
>>> Hi,
>>>
>>> How can I convert this SQL query into a Django query?
>>>
>>> SELECT DATE(JoinTime) FROM table
>>>
>>> Please keep in mind that JoinTime is in datetime format and I want it to
>>> be date.
>>>
>>> Thanks
>>>
>>>
>
>
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/b39dbfd6-1bf5-4dec-8129-f362d7f30696%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
