是否使用mongoose查询筛选具有多个运算符的多个值？

我有一个数组的过滤器选项与多个操作符。在这里我提供了样本数据和结构。用户可以选择一个以上的过滤器与任何组合。样本JSON结构我已经给出了下面。什么是有效的方法来获得数据使用mongoose查询？
系列=〉'包含'、'不包含'、'开头为'、'结尾为'
产品数量=〉'='、'！='、'〉'、'〉='、'〈'、'〈='
状态=〉'活动'，'非活动'
属性数=〉'总数'，'强制'，'可选'
“自定义”、“本周”、“上周”、“前2周”、“本月”、“上月”、“前2月”
输入：

{
    "search":"",
    "offset":0,
    "limit": 10,
    "filter":[{
        "filter_by":"family",
        "operator":"starts_with",
        "from_value":"test",
        "to_value":""
    },
    {
        "filter_by":"no_of_products",
        "operator":"=",
        "from_value":"10",
        "to_value":""
    }]
    
}

实际值：（schema.js）

return Joi.object({
      search: Joi.string().allow('').description('Search the family by name'),
      offset: Joi.number().required().description('Specify the offset for the pagination'),
      limit: Joi.number().required().description('Limit the number of records to be displayed in the result'),
      filter: Joi.array().items(Joi.object({
          filter_by: Joi.string().valid('family','no_of_products', 'state', 'no_of_attributes', 'last_updated').allow('').description('Filter the results from the specified filter value.'),
          operator: Joi.string().valid('contains', 'doesnt_contain', 'starts_with', 'ends_with','is_empty', 'is_not_empty', 'active', 'inactive', '=', '!=', '>','>=', '<', '<=', 'total', 'mandatory', 'optional', 'custom','this_week', 'last_week', 'last_2_week', 'this_month', 'last_month', 'last_2_month').allow('').description('Provide operator name'),
          from_value: Joi.string().allow('').description('from_value'),
          to_value: Joi.string().allow('').description('to_value')
      }))
     }).label('family')

controller.js:

if(!search && !filter){
                 dbFamilies = await Family.find({client_id : client_id, "status": { $ne: 'Deleted' }})
                 .populate([{path:'created_by'},{path:'updated_by'}])
                 .sort("name ASC").limit(limit).skip(offset).lean()
            }else if(!!search){
                //  dbFamilies = await Family.find({client_id : client_id, name: search, "status": { $ne: 'Deleted' }})
                //  .collation({ locale: 'en', strength: 2 }).sort("name ASC").limit(limit).skip(offset).lean()
                dbFamilies = await Family.find(
                { $and: [{client_id : client_id, "name": { "$regex": search, "$options": "i" }, "status": { $ne: 'Deleted' }}]})
                .populate([{path:'created_by'},{path:'updated_by'}])
                .sort("name ASC").limit(limit).skip(offset).lean()
            }else if(!!filter){

            }

在这里我添加了我的解决方案。在这个解决方案中，获取请求并在对象内创建查询，然后根据请求参数将整个对象传递给mongoose查询。希望它能对其他人有所帮助。

let dbFamilies = []
            const offset = req.query.offset;
            const limit = req.query.limit;
            const search = req.query.search;
            const sort = {};
            if (req.query.sort) {
                let x = req.query.sort.split(':')
                sort[x[0]] = x[1]
            } else {
                sort['updatedAt'] = 'desc'
            }
            var filterObj = {
                "client_id": client_id,
                "name": req.query.name ? req.query.name : '',
                "no_of_products": req.query.no_of_products ? req.query.no_of_products : '',
                "status": req.query.status ? req.query.status : { $ne: 'Deleted' },
                "no_of_attributes": req.query.no_of_attributes ? req.query.no_of_attributes : '',
                "no_of_mandatory": "",
                "optional": "",
                "updatedAt": req.query.last_updated ? req.query.last_updated : ''
            }
            let familyList = {
                'Total': 0,
                'Active': 0,
                'Inactive': 0
            }
            if (!search && !filterObj.name && !filterObj.no_of_products && !filterObj.no_of_attributes && !filterObj.status && !filterObj.last_updated) {
                let family = await Family.aggregate([
                    { "$match": { client_id: client_id } },
                    { "$group": { _id: "$status", count: { $sum: 1 } } }
                ])
                family.forEach(data => {
                    if (data._id == "Active") {
                        familyList.Active = data.count
                    } else if (data._id == "Inactive") {
                        familyList.Inactive = data.count
                    }
                })
                familyList.Total = familyList.Active + familyList.Inactive
                dbFamilies = await Family.find({ client_id: client_id, "status": { $ne: 'Deleted' } })
                    .populate([{ path: 'created_by' }, { path: 'updated_by' }])
                    .sort(sort).limit(limit).skip(offset).lean()
            } else if (!!search) {
                //  dbFamilies = await Family.find({client_id : client_id, name: search, "status": { $ne: 'Deleted' }})
                //  .collation({ locale: 'en', strength: 2 }).sort("name ASC").limit(limit).skip(offset).lean()
                let family = await Family.aggregate([
                    { "$match": { client_id: client_id, "name": { "$regex": search, "$options": "i" } } },
                    { "$group": { _id: "$status", count: { $sum: 1 } } }
                ])
                family.forEach(data => {
                    if (data._id == "Active") {
                        familyList.Active = data.count
                    } else if (data._id == "Inactive") {
                        familyList.Inactive = data.count
                    }
                })
                familyList.Total = familyList.Active + familyList.Inactive
                dbFamilies = await Family.find(
                    { $and: [{ client_id: client_id, "name": { "$regex": search, "$options": "i" }, "status": { $ne: 'Deleted' } }] })
                    .populate([{ path: 'created_by' }, { path: 'updated_by' }])
                    .sort(sort).limit(limit).skip(offset).lean()
            } else if (filterObj.name || filterObj.no_of_products || filterObj.no_of_attributes || filterObj.status || filterObj.last_updated) {
                let operator
                if (filterObj.name) {
                    operator = filterObj.name.split(":")
                    if (operator[0] === 'contains') {
                        filterObj.name = { "$regex": operator[1], "$options": "i" }
                    } else if (operator[0] === 'doesnt_contain') {
                        filterObj.name = { "$not": { "$regex": operator[1], "$options": "i" } }
                    } else if (operator[0] === 'starts_with') {
                        filterObj.name = { "$regex": "^" + operator[1], "$options": "i" }
                    } else if (operator[0] === 'ends_with') {
                        filterObj.name = { "$regex": '.*' + operator[1], "$options": "i" }
                    } else {
                        return h.response({ error: responseMessages.bad_request }).code(400);
                    }
                }
                if (filterObj.no_of_products) {
                    operator = filterObj.no_of_products.split(":")
                    const number = parseInt(operator[1])
                    if (operator[0] === 'eq') {
                        filterObj.no_of_products = { "$eq": number }
                    } else if (operator[0] === 'neq') {
                        filterObj.no_of_products = { "$ne": number }
                    } else if (operator[0] === 'gte') {
                        filterObj.no_of_products = { "$gte": number }
                    } else if (operator[0] === 'gt') {
                        filterObj.no_of_products = { "$gt": number }
                    } else if (operator[0] === 'lte') {
                        filterObj.no_of_products = { "$lte": number }
                    } else if (operator[0] === 'lt') {
                        filterObj.no_of_products = { "$lt": number }
                    } else if (operator[0] === 'is_empty') {
                        filterObj.no_of_products = 0
                    } else if (operator[0] === 'is_not_empty') {
                        filterObj.no_of_products = { $ne: 0 }
                    } else {
                        return h.response({ error: responseMessages.bad_request }).code(400);
                    }
                }
                if (filterObj.status) {
                    if (filterObj.status === 'active') {
                        filterObj.status = 'Active'
                    } else if (filterObj.status === 'inactive') {
                        filterObj.status = 'Inactive'
                    }
                }
                if (filterObj.no_of_attributes) {
                    operator = filterObj.no_of_attributes.split(":")
                    if (operator[0] === 'total') {
                        const number = parseInt(operator[2])
                        if (operator[1] === 'eq') {
                            filterObj.no_of_attributes = { "$eq": number }
                        } else if (operator[1] === 'gte') {
                            filterObj.no_of_attributes = { "$gte": number }
                        }
                    } else if (operator[0] === 'mandatory') {
                        filterObj.no_of_attributes = ''
                        const number = parseInt(operator[2])
                        if (operator[1] === 'eq') {
                            filterObj.no_of_mandatory = { "$eq": number }
                        } else if (operator[1] === 'gte') {
                            filterObj.no_of_mandatory = { "$gte": number }
                        }
                        // }else if(operator[0] === 'optional'){
                        // filterObj.no_of_attributes = ''
                        //     filterObj.no_of_mandatory = number
                    } else {
                        return h.response({ error: responseMessages.bad_request }).code(400);
                    }
                }
                if (filterObj.updatedAt) {
                    let startDate
                    let endDate
                    if (filterObj.updatedAt.includes(',')) {
                        //custom dates
                        operator = filterObj.updatedAt.split(",")
                        startDate = operator[0].split(':')[1]
                        endDate = operator[1].split(':')[1]
                    } else {
                        if (filterObj.updatedAt === 'this_week') {
                            startDate = new Date(moment().startOf('week').format('YYYY-MM-DD'));
                            endDate = new Date(moment().endOf('week').format('YYYY-MM-DD'));
                        } else if (filterObj.updatedAt === 'last_week') {
                            startDate = new Date(new Date() - 7 * 60 * 60 * 24 * 1000)
                            endDate = new Date()
                        } else if (filterObj.updatedAt === 'last_2_week') {
                            startDate = new Date(new Date() - 14 * 60 * 60 * 24 * 1000)
                            endDate = new Date()
                        } else if (filterObj.updatedAt === 'this_month') {
                            startDate = new Date(moment().startOf('month').format('YYYY-MM-DD'));
                            endDate = new Date(moment().endOf('month').format('YYYY-MM-DD'));
                        } else if (filterObj.updatedAt === 'last_month') {
                            startDate = new Date().setMonth(new Date().getMonth() - 1);
                            endDate = new Date();
                        } else if (filterObj.updatedAt === 'last_2_month') {
                            startDate = new Date().setMonth(new Date().getMonth() - 2);
                            endDate = new Date();
                        } else {
                            return h.response({ error: responseMessages.bad_request }).code(400);
                        }
                    }
                    filterObj.updatedAt = { "$gte": new Date(startDate), "$lt": new Date(endDate) }
                }
                let filterValues = Object.entries(filterObj).reduce((a, [k, v]) => (v ? (a[k] = v, a) : a), {})
                let query = { $and: [filterValues] }
                let family = await Family.aggregate([
                    { "$match": query },
                    { "$group": { _id: "$status", count: { $sum: 1 } } }
                ])
                family.forEach(data => {
                    if (data._id == "Active") {
                        familyList.Active = data.count
                    } else if (data._id == "Inactive") {
                        familyList.Inactive = data.count
                    }
                })
                familyList.Total = familyList.Active + familyList.Inactive
                dbFamilies = await Family.find(query).populate([{ path: 'created_by' }, { path: 'updated_by' }])
                    .sort(sort).limit(limit).skip(offset).lean()

是否使用mongoose查询筛选具有多个运算符的多个值？

1条答案

相关问题

热门标签

最新问答