I was going about it all wrong December 24, 2014
MongoDB Aggregation Framework, which, if it didn't work right, I would call the Aggrevation Framework. Well it works wonderfully. So that name shall be put in my back pocket for a special kind of software.
For this blog, there is a list on the right which has the years months and posts for that month. It's pretty easy to represent in SQL, like so:
select Month(Post.Date), Year(Post.Date), count(*) from Post group by Month(Post.Date), Year(Post.Date)
Well, in my current version of this site, which is written in Node.JS using MongoDB, I wrote something which after looking at it, and the new solution, is not something I want to post here. :)
I think it was written before I knew about the Aggregation Framework. It was like this:
Loop from 2005 to now creating Date objects for each month along the way, loop over these Date objects, grab post counts for each Date object, update object with count.
Anyway, it was correct but could be done way better. Like instead of multiple calls to the database, how about just 1? Seems like an improvement.
Here it is in straight up MongoDB shell.
db.posts.aggregate (
[
{ $match: {} },
{ $project: {
"postMonth": { "$month": "$date" },
"postYear": { "$year": "$date" }, _id: 0 }
},
{ $group: {
_id: { "postMonth": "$postMonth", "postYear":"$postYear" },
"count": { "$sum": 1 } }
}
]
)
Here it is in mgo (apparently pronounced mango)
pipe := posts.Pipe([]bson.M{
{ "$match": bson.M{} },
{ "$project": bson.M{ "postMonth": bson.M{ "$month": "$date" }, "postYear": bson.M{ "$year": "$date" } } },
{ "$group": bson.M{ "_id": bson.M{ "postMonth": "$postMonth", "postYear": "$postYear" }, "count": bson.M{ "$sum": 1 } } },
{ "$sort": bson.M{ "_id.postYear": -1, "_id.postMonth": -1 } },
})
Here's the whole GetPostDateCounts method:
func (postRepo PostRepository) GetPostDateCounts() []YearCount {
posts := postRepo.OpenCollection(postRepo.collection)
pipe := posts.Pipe([]bson.M{
{ "$match": bson.M{} },
{ "$project": bson.M{ "postMonth": bson.M{ "$month": "$date" }, "postYear": bson.M{ "$year": "$date" } } },
{ "$group": bson.M{ "_id": bson.M{ "postMonth": "$postMonth", "postYear": "$postYear" }, "count": bson.M{ "$sum": 1 } } },
{ "$sort": bson.M{ "_id.postYear": -1, "_id.postMonth": -1 } },
})
itr := pipe.Iter()
p := YearMonthCount{}
mapped := make(map[int][]MonthCount, 30) //this will work for 30 years. this code should be re-architected to accommodate advances in medicine
for itr.Next(&p) {
mapped[p.Date.Year] = append(mapped[p.Date.Year], MonthCount{ Month: p.Date.Month, Count: p.Count, MonthNum: int(p.Date.Month) })
}
yc := []YearCount{}
for year, months := range mapped {
yc = append(yc, YearCount{ Year: year, Months: months })
}
sorter := &YearCountSorter{ Entries: yc }
sort.Sort(sort.Reverse(sorter))
return sorter.Entries
}
And some structs used within that code
type YearMonth struct {
Year int `bson:"postYear"`
Month time.Month `bson:"postMonth"`
}
type YearMonthCount struct {
Count int `bson:"count"`
Date YearMonth `bson:"_id"`
}
type MonthCount struct {
Count int
Month time.Month
MonthNum int
}
type YearCount struct {
Year int
Months []MonthCount
}
