Mongoose query with ref document

Question

I am building a complex search route (express.js, mongoose) based on tags, price, rating with sorting and pagination.

This is the Product schema:

import { Schema, model, SchemaTypes } from 'mongoose';
import { IProductDocument, IReviewDocument } from '../types.js';

const ProductSchema = new Schema<IProductDocument>(
    {
        name: {
            type: String,
            required: true,
            trim: true
        },
        description: {
            type: String,
            required: true,
            trim: true
        },
        price: {
            type: Number,
            required: true
        },
        imagePath: {
            type: String,
            required: true
        },
        tags: {
            type: [String],
            required: true,
            default: []
        },
        sold: {
            type: Number,
            required: true,
            default: 0
        },
        available: {
            type: Boolean,
            required: true,
            default: true
        },
        reviews: {
            type: [SchemaTypes.ObjectId],
            ref: 'Review',
            default: []
        }
    },
    {
        timestamps: true,
        toJSON: {
            virtuals: true
        },
        toObject: {
            virtuals: true
        }
    }
);

ProductSchema.virtual('rating').get(async function () {
    if (this.reviews.length === 0) return 0;

    const reviews = this.reviews as unknown as IReviewDocument[];

    const totalRating = reviews.reduce((acc, review) => {
        const reviewDocument = review;

        return acc + reviewDocument.rating;
    }, 0);

    const avgRating = totalRating / this.reviews.length;

    return Math.round(avgRating * 10) / 10;
});

export default model<IProductDocument>('Product', ProductSchema);

This is the route:

router.get('/search', async (req: Request, res: Response) => {
    const tagsQuery = req.query.tags ? String(req.query.tags) : '';
    const priceQuery = req.query.price ? String(req.query.price) : '';
    const ratingQuery = req.query.rating ? String(req.query.rating) : '';
    const sortQuery = req.query.sort ? String(req.query.sort) : '';

    let sort: Record<string, 1 | -1>;

    switch (sortQuery) {
        case 'price_asc':
            sort = { price: 1 };
            break;
        case 'price_desc':
            sort = { price: -1 };
            break;
        case 'recommend':
            sort = { sold: -1 };
            break;
        case 'newest':
            sort = { createdAt: -1 };
            break;
        case 'oldest':
            sort = { createdAt: 1 };
            break;
        default:
            sort = { sold: -1 };
            break;
    }

    // e.g. tags=tag1,tag2 => tags = ['tag1', 'tag2'] (tags includes tag1 or tag2)
    let tags = tagsQuery
        .split(',')
        .map((tag) => tag.trim())
        .filter((tag) => Boolean(tag));

    if (!tags || tags.length === 0) {
        tags = ['bedroom', 'bed', 'bookshelf', 'chair', 'desk', 'drawer', 'livingroom', 'sofa', 'table'];
    }
    // e.g. price=0,100 => price = [0, 100] (price >= 0 && price <= 100)
    const price = priceQuery
        .split(',')
        .map((p) => Number(p.trim()))
        .filter((p) => Boolean(p));
    // e.g. rating=3,5 => rating = [3, 5] (rating >= 3 && rating <= 5)
    const rating = ratingQuery
        .split(',')
        .map((r) => Number(r.trim()))
        .filter((r) => Boolean(r));

    const limit = req.query.limit ? Number(req.query.limit) : 10;
    const page = req.query.page ? Number(req.query.page) : 1;
    const skip = (page - 1) * limit;

    const pipeline = [
        {
            $match: {
                tags: { $in: [...tags] },
                price: { $gte: price[0] || -1, $lte: price[1] || Number.MAX_SAFE_INTEGER }
            }
        },
        {
            $facet: {
                filteredProducts: [
                    {
                        $lookup: {
                            from: 'reviews',
                            localField: '_id',
                            foreignField: 'productId',
                            as: 'reviews'
                        }
                    },
                    {
                        $addFields: {
                            rating: { $round: [{ $avg: '$reviews.rating' }, 2] }
                        }
                    },
                    {
                        $unset: 'reviews'
                    },
                    {
                        $match: {
                            rating: { $gte: rating[0] || -1, $lte: rating[1] || Number.MAX_SAFE_INTEGER }
                        }
                    },
                    { $sort: sort },
                    { $skip: skip },
                    { $limit: limit }
                ],
                totalCount: [
                    {
                        $count: 'count'
                    }
                ]
            }
        }
    ];

    const results = await Product.aggregate(pipeline);

    const products = results[0].filteredProducts;
    const count = results[0].totalCount[0]?.count || 0;

    if (products === undefined) return res.sendStatus(404);

    res.json({
        products,
        count,
        currPage: page,
        totPage: Math.ceil(count / limit)
    });
});

This doesn't work as expected, since querying like this: /search?limit=5&sort=recommend, returns an empty array of products. Debugging, I think that the problem hides in the rating filtering, but I am not sure.

Also, I would prefer to use, if possible, mongoose query methods instead of aggregation framework, but I don't know how to do it in such complex case.

Answer 1

Just fixed the problem. After some more debugging, I found out that the rating/score field was null when the product didn't have reviews.

Here's the fix

Pipeline:

const pipeline: PipelineStage[] = [
    {
        $match: {
            tags: { $in: [...tags] },
            price: { $gte: price[0], $lte: price[1] }
        }
    },
    {
        $facet: {
            filteredProducts: [
                {
                    $lookup: {
                        from: 'reviews',
                        localField: '_id',
                        foreignField: 'productId',
                        as: 'reviews'
                    }
                },
                {
                    $addFields: {
                        score: { $ifNull: [{ $round: [{ $avg: '$reviews.rating' }, 2] }, 0] }
                    }
                },
                {
                    $match: {
                        score: { $gte: rating[0], $lte: rating[1] }
                    }
                },
                {
                    $project: {
                        reviews: 0,
                        score: 0
                    }
                },
                { $sort: sort },
                { $skip: skip },
                { $limit: limit }
            ],
            totalCount: [
                {
                    $count: 'count'
                }
            ]
        }
    }
];

Mongoose methods

const products = await Product.aggregate()
    .match({
        tags: { $in: tags },
        price: { $gte: price[0], $lte: price[1] }
    })
    .lookup({
        from: 'reviews',
        localField: '_id',
        foreignField: 'productId',
        as: 'reviews'
    })
    .addFields({
        score: {
            $ifNull: [{ $round: [{ $avg: '$reviews.rating' }, 2] }, 0]
        }
    })
    .match({
        score: { $gte: rating[0], $lte: rating[1] }
    })
    .project({
        reviews: 0,
        score: 0
    })
    .sort(sort)
    .skip(skip)
    .limit(limit);
const count = (
    await Product.aggregate()
        .match({
            tags: { $in: tags },
            price: { $gte: price[0], $lte: price[1] }
        })
        .count('count')
)[0].count;

See more about this project: Front-end, Back-end