Sort by date in Datetime field in mongo
I am using django with mongo and I have this model type:
class ProductDate(EmbeddedDocument):
created = DateTimeField()
updated = DateTimeField(null=True)
class Product(Document):
product_id = IntField()
saves = IntField(default=0)
title = StringField(max_length=1000)
gender = StringField(choices=settings.GENDER_CHOICES, default=settings.UNISEX, max_length=50)
date = EmbeddedDocumentField(ProductDate)
Now when I make a request like this:
queryset = queryset.filter(title=search)
I want to order first by date in the created DateTime field and then order using save. This way, products with the same date rather than time are sorted by number of saves. However, I cannot find a way to turn the diatomite to date, so it only compares the date.
I can not:
.order_by("-date__created","-saves")
because these orders are based on time.
I tried to use aggregate in mongo, using this tutorial:
but that didn't work for me.
Can anyone please guide me on how I can do this? Thank!
source to share
You need to use an _get_collection()
accesssor to get the base method .aggregate()
from the pymongo collection and then use it to generate the abbreviated date information. $project
You can use date aggregation operators :
Product._get_collection.aggregate([
{ "$project": {
"product_id": 1,
"saves": 1,
"title": 1,
"gender": 1,
"date": 1,
"partDate": {
"year": { "$year": "$date.created" },
"dayOfYear": { "$dayOfYear": "$date.created" }
}
}},
{ "$sort": {
"partDate.year": -1,
"partDate.dayOfYear": -1,
"saves": -1
}}
])
Or instead of date math:
Product._get_collection.aggregate([
{ "$project": {
"product_id": 1,
"saves": 1,
"title": 1,
"gender": 1,
"date": 1,
"truncDate": {
"$subtract": [
{ "$subtract": [
"$date.created",
datetime.datetime.utcfromtimestamp(0)
] },
{ "$mod": [
{ "$subtract": [
"$date.created",
datetime.datetime.utcfromtimestamp(0)
] },
1000 * 60 * 60 * 24
]}
]
}
}},
{ "$sort": { "truncDate": -1, "saves": -1 }}
])
The latter works when you do math on a Date
BSON object , the result is a unix timestamp value from seconds from epoch. So, subtracting the epoch date gives the timestamp value, which is then rounded to one day.
- EDIT -
Better yet, with a little extra math and you get back the BSON Date
that the API returns. Just apply $add
to return a numeric value on a BSON date:
Product._get_collection.aggregate([
{ "$project": {
"product_id": 1,
"saves": 1,
"title": 1,
"gender": 1,
"date": 1,
"truncDate": {
"$add": [
{ "$subtract": [
{ "$subtract": [
"$date.created",
datetime.datetime.utcfromtimestamp(0)
]},
{ "$mod": [
{ "$subtract": [
"$date.created",
datetime.datetime.utcfromtimestamp(0)
]},
1000 * 60 * 60 * 24
]}
]},
datetime.datetime.utcfromtimestamp(0)
]
}
}},
{ "$sort": { "truncDate": -1, "saves": -1 }}
])
This is the same basic epoch date application that is used in the rest of the conversion.
- END -
Then you can in the appropriate box. $sort
Of course, these are "raw" objects and are no longer mongoengine documents, but then the result of the aggregation rarely exactly matches the input of the collection. Of course, in this case, you can select the appropriate fields and repeat in your document type if necessary. Or else just work with the results as they are.
source to share