How to join three collections in mongodb?

Question

I am using almost-pure JS code to join three collections in MongoDB, and I believe there must be an easier way to do that using just mongoose query.

Here is my case; I have a seller that can create and sell documents. I want my seller to be able to see the documents that he sold.

This is my schema for the Order collection:

const OrderSchema = new mongoose.Schema({
    //...
    
    user: {
        type: mongoose.Schema.ObjectId,
        ref: 'User',
        required: true
    },
    documents: [
        {
            document: {
                type: mongoose.Schema.ObjectId,
                ref: 'Document',
                required: true
            },
            price: {
                type: Number,
                required: [true, 'Price is required']
            }
        }
    ],
    
    //...
});

and my schema for the document looks like this:

const DocumentSchema = new mongoose.Schema({
    //...
    
    title: {
        type: String,
        required: [true, 'Please provide a title'],
    },
    author: {
        type: mongoose.Schema.ObjectId,
        ref: 'Seller',
        required: true
    },
    
    //...
});

As I said before, I want to get a list of documents that belongs to the logged in Seller that got sold. (Something like this).

"data": [
        {
            "orderId": "606b448dd2d9d643a811bc33",
            "documentId": "606b448dd2d9d643a811bc34",
            "title": "Document 1 u1",
            "price": 90,
            "createdAt": "2021-04-05T17:10:37.469Z"
        },
        {
            "orderId": "606b43d2cd9b2740b8b8974b",
            "documentId": "606b43d2cd9b2740b8b8974c",
            "title": "Business Affiliate u7",
            "price": 222,
            "createdAt": "2021-04-05T17:07:30.859Z"
        },
        {
            "orderId": "606b1a03ec048e0d44cb3bee",
            "documentId": "606b1a03ec048e0d44cb3bef",
            "title": "Business Affiliate u7",
            "price": 777,
            "createdAt": "2021-04-05T14:09:07.539Z"
        },
        {
            "orderId": "606b1a03ec048e0d44cb3bee",
            "documentId": "606b1a03ec048e0d44cb3bf0",
            "title": "Doc 1 u1",
            "price": 1,
            "createdAt": "2021-04-05T14:09:07.539Z"
        },
 ]

The code I got this far is the one below. It looks kinda ugly and it contains three Fors.

const documents = await Document.find().where('author', req.uid).select('orders').populate({ path: 'orders' })

    let filteredOrders = [];

    for (const document of documents) {
        //GET ALL ORDERS THAT CONTAINS THIS DOCUMENTS
        const orders = await Order.find().where('documents.document', document._id).populate({ path: 'documents.document', select: 'title' }).sort('-createdAt');

        for (const order of orders) {
            for (const doc of order.documents) {
                if (doc.document._id.toString() == document._id.toString()) {
                    filteredOrders.push({
                        orderId: order._id,
                        documentId: doc._id,
                        title: doc.document.title,
                        price: doc.price,
                        createdAt: order.createdAt
                    });
                }
            }
        }
    }

    filteredOrders.sort((a, b) => new Date(b.createdAt) - new Date(a.createdAt));

    res.status(200).json({ success: true, data: filteredOrders });

Is there a way with just Mongoose that I can get the same result?

Answer 1

After some research and reading the documentations (20-ich tabs) this is the final code (It may needs some cleanups):

let id = mongoose.Types.ObjectId(req.uid);

    const orders = await Order
        .aggregate([
            { $unwind: '$documents' },
            {
                $lookup: {
                    from: "documents",
                    localField: "documents.document",
                    foreignField: "_id",
                    as: "document"
                }
            },
            { $match: { 'document.author': id } },
            { $project: { user: 1, deliveryEmail: 1, createdAt: 1, documentTitle: '$document.title', documentId: '$documents.document', documentPrice: '$documents.price' } },
            { $sort: { 'createdAt': -1 } }
        ])

    res.status(200).json({ success: true, data: orders });