Untitled

// Define an array of company IDs you want to query
var companyIds = [
    ObjectId("company_id_1"),
    ObjectId("company_id_2"),
    // Add more company IDs as needed
];

// Query optimization
db.company.aggregate([
    // Match multiple companies
    { $match: { _id: { $in: companyIds } } },
    // Project only the necessary fields from the user collection
    {
        $project: {
            name: 1,
            users: {
                $filter: {
                    input: "$users",
                    as: "user",
                    cond: { $in: ["$$user.company_id", companyIds] }
                }
            }
        }
    },
    // Unwind the users array
    { $unwind: "$users" },
    // Lookup orders belonging to the users
    {
        $lookup: {
            from: "order",
            let: { userId: "$users._id" },
            pipeline: [
                { $match: { $expr: { $eq: ["$user_id", "$$userId"] } } },
                { $sort: { "created_at": -1 } }, // Sort orders by descending creation
                { $limit: 5 } // Limit the number of orders per user if needed
            ],
            as: "orders"
        }
    },
    // Group by company and reconstruct the document
    {
        $group: {
            _id: "$_id",
            name: { $first: "$name" },
            newest_orders: { $push: "$orders" }
        }
    }
]);