I have array of filter options with multiple operators. Here i provided sample datas and structure. user can select more than one filter with any combination. sample JSON structure i have given below. what is the effective way to get datas using mongoose query?
Family => 'contains', 'doesnt_contain', 'starts_with', 'ends_with'
no of products => '=', '!=', '>','>=', '<', '<='
state => 'Active', 'Inactive'
no of attributes => 'total', 'mandatory', 'optional'
'custom','this_week', 'last_week', 'last_2_week', 'this_month', 'last_month', 'last_2_month'
input:
{
"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":""
}]
}
actual values:(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){
}
here I have added my solution. In this solution, getting request and creating query within object and pass whole object into mongoose query based on request params. Hope it helps to someone..
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()