I've got a document "item" that looks like this:
{
"_id" : ObjectId("5a146ce6cca59f21e897589b"),
"platform" : "example_platform",
"mp_id" : "example_marketplace_id",
"category" : {
"platform" : "example_platform",
"id" : 999,
"name" : "example_category_name"
},
"image_urls" : [
"http://example.com/image.jpg"
],
"title" : "example_title",
"seller" : {
"username" : "example_username",
"platform" : "example_platform",
},
"quantity_sold" : 100,
"sales" : [
{
"_id" : ObjectId("5a146cf3cca59f21e8975951"),
"time" : ISODate("2017-09-09T04:07:36.000Z"),
"amount" : 31.4500007629395,
"currency" : "USD",
"buyer" : {
"username" : "example_username",
"platform" : "example_platform",
},
"item_id" : ObjectId("5a146ce6cca59f21e897589b")
},
{
"_id" : ObjectId("5a146cf3cca59f21e8975952"),
"time" : ISODate("2017-11-16T01:24:10.000Z"),
"amount" : 27.0900001525879,
"currency" : "USD",
"buyer" : {
"username" : "example_username",
"platform" : "example_platform",
},
"item_id" : ObjectId("5a146ce6cca59f21e897589b")
}
]
}
What I'd like to do is be able to query to db to sort the items by number of times an item sold within a given time range, and also paginate through these results using skip and limit (or some other method).
This is what my aggregation query/pipeline currently looks like:
func (this SellerItemsQuery) MakePipeline() []bson.M{
var pipeline = []bson.M{
{"$match": bson.M{
"seller.username": this.Username,
"seller.platform": this.Platform,
}}}
if !this.SalesFromDate.Equal(time.Time{}) && !this.SalesToDate.Equal(time.Time{}) {
pipeline = append(pipeline, bson.M{
"$addFields": bson.M{
"sales": bson.M{
"$filter": bson.M{
"input": "$sales",
"as": "sale",
"cond": bson.M{"$and": []bson.M{{"$gte": []interface{}{"$$sale.time", this.SalesFromDate}}, {"$lte": []interface{}{"$$sale.time", this.SalesToDate}}}},
},
},
},
})
}
pipeline = append(pipeline, bson.M{
"$addFields": bson.M{
"num_sales": bson.M{
"$size": bson.M{
"$ifNull": []interface{}{
"$sales", []interface{}{},
},
},
},
},
})
pipeline = append(pipeline, bson.M{
"$sort": bson.M{"num_sales": -1,
"_id": 1},
}, bson.M{
"$skip": this.Skip,
}, bson.M{
"$limit": this.Limit,
})
return pipeline
The issue with this currently is that it returns very inconsistent results. When paginating by 25 items at a time (skip=0&limit=25, skip=25&limit=25, etc.). The first result set is usually correct, with the best selling items in given time period appearing at the top and number of sales decreasing as expected, however by the 2nd, 3rd, or 4th set of 25 the results are almost completely random. Number of sales of items in the set will suddenly drop to zero, while the next set will include items with 3 sales, etc. Completely out of order, with duplicate items showing up as well, even though I've included a sort by {_id: 1} in the pipeline, which I thought would solve this problem.