How to get data by query ref object in mongoose

Question

I have an issue and failed to find what i am exactly looking for. i have this Ads schema with references of others too:

    link: {
        type: String,
        default: 'n/a',
    },
    page: {
        type: mongoose.Schema.ObjectId,
        ref: 'AdsPage',
        required: true,
    },
    slot: {
        type: mongoose.Schema.ObjectId,
        ref: 'AdsSlot',
        required: true,
    },

and i want to get data by applying condition on page property, page is a schema which has url property in it. page schema:

    {
    title: {
        type: String,
        required: [true, 'Please add page title'],
        unique: true,
        trim: true,
        maxlength: [50, 'Name cannot be more then 50 characters'],
    },
    url: {
        type: String,
        required: [true, 'Add page URL'],
    },
    createdAt: {
        type: Date,
        default: Date.now,
    },
},

i want to get all ads that matches provided page url.

my query looks like:

if (req.params.pageUrl) {
    const ads = await Ads.find({
        'page.url': req.params.pageUrl,
    });

    return res.status(200).json({
        success: true,
        message: 'Successfully fetched ads for specific page',
        count: ads.length,
        data: ads,
    });
}

page url in param is good, but somehow this filter is not working, i got no error but with zero results. i have tried $match property but got some upper level error.

any help on query on nested ref object is much appreciated.

Answer 1

You could use aggregate and $lookup to do it. You could see more detail in aggregation.

Your ads_pages in output is your adspages. First element in aggregate array, $lookup will help you find the all matched condition which _id in adspage equal pagein ads and url in adspage equal to your req.params.pageUrl.

Second element in aggregate array, $match will help you remove document contains empty ads_pages which means its condition doesn't match above conditions. You could use this https://jsfiddle.net/cuxvd2pm to test.

await AdsModel.aggregate([
    {
        $lookup: {
            // This name must be same as your collection name "in the mongodb"
            // In my case, I must use lowercase string, and add more extra "s" in the end
            // If you didn't modify extra configuration, I think you should also do it.
            from: "adspages",

            // you could use as: "page" to replace the original field
            as: "ads_pages",
            let: { "page_id": "$page"},
            pipeline: [{ 
                $match: {
                    $expr: {
                        $and: [
                            {$eq: ["$url", req.params.pageUrl]},
                            {$eq: ["$_id", "$$page_id"]}
                        ]
                    }
                }
            }]
        }
    },
    {
        $match: {
            // when you change your `as field` to page
            // you should also change `ads_pages.0` to `page.0`
            "ads_pages.0": {
                $exists: true
            }
        }
    }
])