The closest thing you've got is to use aggregations :
MyModel.objects.annotate(
created_date = TruncDay('created')
).values('created_date').annotate(id = Min('id'))This would aggregate over the similar dates, and pick-up the minimal id.
[{
'created_date': datetime.date(2017, 3, 16),
'id': 146
},
{
'created_date': datetime.date(2017, 3, 28),
'id': 188
},
{
'created_date': datetime.date(2017, 3, 24),
'id': 178
},
{
'created_date': datetime.date(2017, 3, 23),
'id': 171
},
{
'created_date': datetime.date(2017, 3, 22),
'id': 157
}
]...If you need the whole objects, you can chain this with a .values_list() and another query set, which would result in a subquery:
MyModel.objects.filter(
id__in = MyModel.objects.annotate(
created_date = TruncDay('created')
).values('created_date').annotate(id = Min('id')).values_list(
'id', flat = True
)
)Maybe a query like this should do the work :
MyModel.objects.all().distinct('created__date')What I need (using postgres + django 1.10): I have many rows with many duplicate dates (=days) within a datetime field. I want a queryset containing one row/object each date/day., 2 days ago Dec 06, 2016 · What I need (using postgres + django 1.10): I have many rows with many duplicate dates (=days) within a datetime field. I want a … , 1 day ago Django - distinct rows/objects distinguished by date/day from datetime field My password reset view in django shows up only when the user has logged in but not when logged out Is there a example in django 1.6 and python 3 to build a accounts app (include:register , … ,Whats the best (performance + pythionic/django) way to do this. My model/table is going to have many rows (>million).
fk | col1 | colX | created(type: datetime) -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --1 | info | info | 2016 - 09 - 03 08: 25: 52.142617 + 00: 00 < -get it(time does not matter) 1 | info | info | 2016 - 09 - 03 16: 26: 52.142617 + 00: 00 2 | info | info | 2016 - 09 - 03 11: 25: 52.142617 + 00: 00 1 | info | info | 2016 - 09 - 14 16: 26: 52.142617 + 00: 00 < -get it(time does not matter) 3 | info | info | 2016 - 09 - 14 11: 25: 52.142617 + 00: 00 1 | info | info | 2016 - 09 - 25 23: 25: 52.142617 + 00: 00 < -get it(time does not matter) 1 | info | info | 2016 - 09 - 25 16: 26: 52.142617 + 00: 00 1 | info | info | 2016 - 09 - 25 11: 25: 52.142617 + 00: 00 2 | info | info | 2016 - 09 - 25 14: 27: 52.142617 + 00: 00 2 | info | info | 2016 - 09 - 25 16: 26: 52.142617 + 00: 00 3 | info | info | 2016 - 09 - 25 11: 25: 52.142617 + 00: 00 etc.fk | col1 | colX | created(type: datetime) -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --1 | info | info | 2016 - 09 - 03 08: 25: 52.142617 + 00: 00 < -get it(time does not matter) 1 | info | info | 2016 - 09 - 03 16: 26: 52.142617 + 00: 00 2 | info | info | 2016 - 09 - 03 11: 25: 52.142617 + 00: 00 1 | info | info | 2016 - 09 - 14 16: 26: 52.142617 + 00: 00 < -get it(time does not matter) 3 | info | info | 2016 - 09 - 14 11: 25: 52.142617 + 00: 00 1 | info | info | 2016 - 09 - 25 23: 25: 52.142617 + 00: 00 < -get it(time does not matter) 1 | info | info | 2016 - 09 - 25 16: 26: 52.142617 + 00: 00 1 | info | info | 2016 - 09 - 25 11: 25: 52.142617 + 00: 00 2 | info | info | 2016 - 09 - 25 14: 27: 52.142617 + 00: 00 2 | info | info | 2016 - 09 - 25 16: 26: 52.142617 + 00: 00 3 | info | info | 2016 - 09 - 25 11: 25: 52.142617 + 00: 00 etc.MyModel.objects.filter(fk = 1).order_by('created__date').di stinct('created__date')MyModel.objects.annotate(created_date = TruncDay('created')).values('created_date').annotate(id = Min('id'))[{
'created_date': datetime.date(2017, 3, 16),
'id': 146
}, {
'created_date': datetime.date(2017, 3, 28),
'id': 188
}, {
'created_date': datetime.date(2017, 3, 24),
'id': 178
}, {
'created_date': datetime.date(2017, 3, 23),
'id': 171
}, {
'created_date': datetime.date(2017, 3, 22),
'id': 157
}]...MyModel.objects.filter(id__in = MyModel.objects.annotate(created_date = TruncDay('created')).values('created_date').annotate(id = Min('id')).values_list('id', flat = True))TruncDate casts expression to a date rather than using the built-in SQL truncate function. It’s also registered as a transform on DateTimeField as __date.,TruncTime casts expression to a time rather than using the built-in SQL truncate function. It’s also registered as a transform on DateTimeField as __time.,These are logically equivalent to Trunc('datetime_field', kind). They truncate all parts of the date up to kind and allow grouping or filtering datetimes with less precision. expression must have an output_field of DateTimeField.,These are logically equivalent to Trunc('date_field', kind). They truncate all parts of the date up to kind which allows grouping or filtering dates with less precision. expression can have an output_field of either DateField or DateTimeField.
class Author(models.Model):
name = models.CharField(max_length = 50)
age = models.PositiveIntegerField(null = True, blank = True)
alias = models.CharField(max_length = 50, null = True, blank = True)
goes_by = models.CharField(max_length = 50, null = True, blank = True)>>> from django.db.models
import FloatField
>>>
from django.db.models.functions
import Cast
>>>
Author.objects.create(age = 25, name = 'Margaret Smith') >>>
author = Author.objects.annotate(
...age_as_float = Cast('age', output_field = FloatField()),
...).get() >>>
print(author.age_as_float)
25.0>>> # Get a screen name from least to most public
>>>
from django.db.models
import Sum
>>>
from django.db.models.functions
import Coalesce
>>>
Author.objects.create(name = 'Margaret Smith', goes_by = 'Maggie') >>>
author = Author.objects.annotate(
...screen_name = Coalesce('alias', 'goes_by', 'name')).get() >>>
print(author.screen_name)
Maggie
>>>
# Prevent an aggregate Sum() from returning None >>>
# The aggregate
default argument uses Coalesce() under the hood. >>>
aggregated = Author.objects.aggregate(
...combined_age = Sum('age'),
...combined_age_default = Sum('age',
default = 0),
...combined_age_coalesce = Coalesce(Sum('age'), 0),
...) >>>
print(aggregated['combined_age'])
None
>>>
print(aggregated['combined_age_default'])
0
>>>
print(aggregated['combined_age_coalesce'])
0>>> from django.db.models
import DateTimeField
>>>
from django.db.models.functions
import Cast, Coalesce
>>>
from django.utils
import timezone
>>>
now = timezone.now() >>>
Coalesce('updated', Cast(now, DateTimeField()))>>> Author.objects.filter(name=Collate(Value('john'), 'nocase'))
<QuerySet [<Author: John>, <Author: john>]>>>> Author.objects.order_by(Collate('name', 'et-x-icu'))
<QuerySet [<Author: Ursula>, <Author: Veronika>, <Author: Ülle>]>