In a simplified data model, I have three types of documents: items, users and assignments. Users and items are stored in their own collections, while assignments are embedded in items. A sample item might look like this:
{
"_id" : ObjectId("xxx"),
"name" : "yyy",
"assignments" : [
{
"assignmentDate" : ISODate("2018-01-11T10:05:20.125Z"),
"user" : ObjectId("zzz"),
},
{
"assignmentDate" : ISODate("2018-01-12T10:05:20.125Z"),
"user" : ObjectId("iii"),
}
]
}
I would like to query all items that are currently assigned to a given user. This aggregation pipeline does the job:
db.Item.aggregate([
{
$addFields: {
currentAssignment: { $arrayElemAt: ['$assignments', -1] }
}
},
{
$lookup: {
from: 'User',
localField: 'currentAssignment.user',
foreignField: '_id',
as: 'currentUser'
}
},
{
$match: {
'currentUser.name': { $eq: 'admin' }
}
}
]);
How can I build this with the Doctrine ODM Aggregation Builder? The Stage::lookup
method accepts only a from
parameter. If I use it on a computed field from the aggregation pipeline (currentAssignment
in this case), it results in:
arguments to $lookup must be strings, localField: null is type null
Other solutions (if possible even without aggregation?) for retrieving the described dataset are also welcome.
The Lookup
stage has more methods, one of which is localField
which sets the localField
in the aggregation stage.