Search Fails For Number/Date Mongoose Schema

[aprouja1]

For example, when searching for a single letter ("t" in this case) the search is accurate when all number, and date fields are set to searchable:false. Otherwise this error is returned:

{ error: 
   { [CastError: Cast to number failed for value "/t/i" at path "campaignEstimate"]
     message: 'Cast to number failed for value "/t/i" at path "campaignEstimate"',
     name: 'CastError',
     kind: 'number',
     value: /t/i,
     path: 'campaignEstimate',
     reason: undefined } }

campaignEstimate is a simple number such as 1234

[vinicius0026]

Unfortunately, I'm not able to fix this right now. We use a regex to allow text search, but it really breaks when searching for numbers.

If you are willing to contribute, I would gladly review a PR. Perhaps checking the data type and only applying the regex when it is a text field.

A faster solution on your side, although a far from ideal one, is to duplicate the numeric field as a string, so you would end up with two fields, campaignEstimate and perhaps campaignEstimateText, then you could use the text field as your search parameter.

[elderapo]

I've found solution for this. It's not perfect but at least works :D

https://gist.github.com/elderapo/b092d960e0c534a412d359dbf32fb40f

Which produces something like:
{ '$where': '(this._id.toString().match(/a/i) !== null) || (this.code.toString().match(/a/i) !== null) || (this.amount.toString().match(/a/i) !== null)' }

@vinicius0026

[hikmahgumelar]

hello I have problem to implement datatable-query to my app
my problem internal server error 500 can you give me other example
for my app, I using nodejs,express,mongodb,ejs for template engine @vinicius0026

[hikmahgumelar]

sorry my mistake
app.use(bodyParser.urlencoded({ extended: false}));
I changed extended: false to true and now work like a charm
datatables-query can show data over 169.000 data

[janneman001]

I tried the proposed solution of apr 1, as a patch on v0.20, but the search does not work after that.
When i have a string only table it is OK. However I also have tables with Number, Boolean and Date.
I think the target should be to support mongoose/mongodb types.

[ikonduktor]

It is very bad that there are still no solution!

Mb someone i could help.

1. Create own module, call it datatablesQuery
2. copy all code from https://github.com/vinicius0026/datatables-query/blob/master/index.js

change function buildFindParameters like this:

    buildFindParameters = function (params, Model) {

        if (!params || !params.columns || !params.search || (!params.search.value && params.search.value !== '')) {
            return null;
        }

        var searchText = params.search.value,
            findParameters = {},
            searchRegex,
            searchOrArray = [];

        if (searchText === '') {
            return findParameters;
        }

        var searchableFieldsDefault = getSearchableFields(params);  
        var searchableFields = [];        

        if(!isNaN(searchText + 1)) {

            searchableFieldsDefault.forEach( (field) => {
                if(Model.schema.obj[field].type.prototype == Number.prototype) {
                    searchableFields.push(field);
                }              
            });

            searchRegex = searchText;
        } else {
            searchableFieldsDefault.forEach( (field) => {
                if(Model.schema.obj[field].type.prototype == String.prototype) {
                    searchableFields.push(field);
                }              
            });

            searchRegex = new RegExp(searchText, 'i');
        }

        if (searchableFields.length === 1) {
            findParameters[searchableFields[0]] = searchRegex;
            return findParameters;
        }

        searchableFields.forEach(function (field) {
            var orCondition = {};
            orCondition[field] = searchRegex;
            searchOrArray.push(orCondition);
        });

        findParameters.$or = searchOrArray;
        return findParameters;        
    },

[vinicius0026]

I'm sorry for being unresponsive, but as I stated before, I'm not actively maintaining this module anymore.

@ikonduktor perhaps you could turn your comment into a PR, then it could land in this module and creating another one would be unnecessary.

If someone is willing to become a contributor, that is something that I would happily accept.

[vovanluan]

Thanks @ikonduktor for your great answer.
I fixed my problem based on your answer but have to change
Model.schema.obj[field].type.prototype == Number.prototype
into
Model.schema.paths[field].instance == 'Number'

[saad3074]

HI i use this in model to get record. how i can get other collection
`app.post('/path/to/api/endpoint', function (req, res) {
var Model = require('./path/to/model'),
datatablesQuery = require('datatables-query'),
params = req.body,
query = datatablesQuery(Model);

query.run(params).then(function (data) {
    res.json(data);
}, function (err) {
    res.status(500).json(err);
});

};`

for example i show title and status from one model and i also want to show category title from other table. what i can do for this

[ashish336b]

any one solve this issue please..

[s4suryapal]

Hi,
issue in search is , its looking for searchText in all fields, even in _id also.

So from frontEnd when creating datatable you need to set option "searchable : false " in columns array

e.g :

columns: [
{
data: '_id',
searchable : false
},
{
data: 'name',
searchable : true
},
{
data: 'tags',
searchable : false
}
]

as you can see only name field is searchable. So it will not cause any issue.

[ashish336b]

@s4suryapal thanks for you answer anyway. I have created my own datatable helper which solve those problems in both server and in UI level.