A User has a bunch of Essays, stored as an array of ObjectIds. In CoffeeScript, the schema is:
User = new Schema
essays: [Schema.Types.ObjectId]
name: String
Essay = new Schema
grade: Number
text: String
author: Schema.Types.ObjectId
How do I get all distinct users whose latest essay was written in the last day, and whose latest essay's grade is between 80 and 90, in one query?
I've got this (in CoffeeScript):
req.mongoose.Essay
.find(
_id:
$gt:
# trick which creates an ObjectId with yesterday's timestamp
mongoose.Types.ObjectId.createFromHexString(
(~~(yesterday.getTime() / 1000)).toString(16) +
"0000000000000000"
)
)
.where("grade").gt(80).lt(90)
.popluate(path: "User")
.exec (err, docs) ->
console.log ({name: essay.author.name} for essay in docs)
This is close but not quite.
This will get anyone who has written an essay in the last day where the essay's score is between 80-90. So for example, if I wrote an essay two hours ago and scored an 85, but then wrote an essay 1 hour ago and scored a 50, I would show up - but I shouldn't because my latest essay (which was written within the last day) did not score between 80-90.
Also, this will get duplicates if someone wrote two essays in the last day and got an 85 on both. Note that distinct doesn't work in conjunction with populate on the same field.
This query is too broad. I need to get everyone whose latest essay (which must be written in the last day) is between 80-90.
The only way I can think of is to form an array of all last Essays _ids by querying User collection:
req.mongoose.User
.find(essays: $not: $size: 0) # Just to be sure
.select(essays: $slice: -1) # We only interested in last Essay
.lean() # It's faster
.exec (err, users) ->
ids = users.map ({essays}) ->
# essays array contains exactly one [last] element
essays[0]
.filter (_id) ->
# We only interested in yesterday Essays
_id.getTimestamp() >= yesterday
req.mongoose.Essay
.find(_id: $in ids)
.where('grade').gt(80).lt(90)
.popluate('User')
.exec (err, docs) ->
console.log ({name: essay.author.name} for essay in docs)
Note that it also allows me to properly filter fetched ids to compare they generation dates with yesterday date using ObjectId::getTimestamp method.