We have a Shipment document with an embedded ShipmentEvent document as in...
class Shipment
include Mongoid::Document
embeds_many :shipment_events
end
class ShipmentEvent
include Mongoid::Document
embedded_in :shipment
end
What we need to get is a join containing shipments and theirs latest shipment_event based on a :event_at field.
Example data (simplified with just one shipment):
[
{
"_id" => BSON::ObjectId('1'),
"shipment_events" => [
{
"_id" => BSON::ObjectId('2'),
"event_at" => 2022-04-09 15:00:00 UTC
},
{
"_id" => BSON::ObjectId('3'),
"event_at" => 2022-04-09 18:00:00 UTC
}
]
}
]
The result expected should contain the shipment and only the second (latest by :event_at) shipment_event. Something like...
[
{
"_id" => BSON::ObjectId('1'),
"_event_id" => BSON::ObjectId('3'),
"event_at" => 2022-04-09 18:00:00 UTC
}
]
I know join queries are not possible with MongoId and we will need to use aggregation I guess. Any ideas will be appreciated.
Thank you.
You can do it this way:
unwind = { "$unwind" => "$shipment_events" }
sort = {
"$sort" => {
"_id" => 1, "shipment_events.event_at" => -1
}
}
group = {
"$group" => {
"_id" => "$_id",
"event_id" => {
"$first" => "$$ROOT.shipment_events._id"
},
"event_at" => {
"$first" => "$$ROOT.shipment_events.event_at"
}
}
}
Shipment.collection.aggregate([unwind, sort, group])
Please find playground example below.
https://mongoplayground.net/p/oLTG-18aIDm