Hi, I’m having some issues on one query.
I have 4 collections: materials, sales, bomlines and products. Bomlines are documents that relate one material with one (finished) product. It’s like the ingredient list. And all sales have a parameter line, which is an array with documents containing, among others, a parameter product.
Given a material, I first fetch all bomlines that have this material. Then I lookup for all sales that its lines array have a document which productId is equal to one of the products of the previously retrieved bomlines. I do that by not unwind it.
The issue comes to the fact that now I have all lines that have as product one of the products gathered from the first lookup, but also all lines that are inside the sales document, not only the ones whose product is related with the material given (through the bomline document)
So what I think I should do, from my knowledge of things, would be simple perform a $match operation, and I would query the parameter ‘sO.lines.product’ and its value should be $in an array. This array should contain all the products gathered on the second query, ie, the first lookup, where I fetched all bomlines that have a material. But I don’t know how to do this. If I manually put the Id works, of course, but my problem is to know how to express the id’s of parameter product as an array so the $in operator can properly filter all lines that have a product that it is not in the bomlines found previously.
Any Ideas?
Thanks.
const pLines = await Material
.aggregate([
{$match: {user: user, _id: ObjectId(materialId)}},
{$lookup: {
from: 'bomlines',
localField: '_id',
foreignField: 'material',
as: 'bO'
}},
// {$unwind: '$bO'}, // I DO NOT UNWIND IT SO IT THE LEXT LOOKUP I USE AN ARRAY AS A LOCAL FIELD
{$lookup: {
from: 'sales',
localField: 'bO.product',
foreignField: 'lines.product',
as: 'sO'
}},
{$unwind: '$sO'},
{$unwind: '$sO.lines'},
{$match: {user: user, 'sO.lines.product': {$in: ['$bO.product'] /*[ObjectId('61ba5e9e64bcdcb4b59eb151')]*/}}},
{$sort: {'sO.date': -1}},
// {$lookup: {
// from: 'sales',
// let: {'pIdArr': '$bO.product'},
// pipeline: [
// {$match: {
// $expr: {
// $in: ['$sO.lines.product', '$$pIdArr']
// }
// }}
// ],
// as: 'sO2'
// }},
])
// .limit(parseInt(fetchLoad))
.toArray();
I also tried the $expr approach, as the last thing commented out but with the same failed result.