2
\$\begingroup\$

I am using jQuery data Tables server processing to show some data to user. The documentation instructs that the response that the server sends must have this data:

recordsTotal
recordsFiltered
data

when I am trying to provide this data, I have to query the database 3 times! One for total document count, once for filtered data count and once for the data itself. The whole point of using serverSide processing is to increase the whole performance, but this is too much pain for the server. Any suggestions?

PS1: I have around 6000 documents in Tests collection, so client side rendering is not an option.

PS2: Because I am using $lookup I had to use aggregation for search

PS3: I know the code is a mess right now! (But it's working)

app.post('/getTests', (req, res) => {
    var searchStr = {};
    if (req.body.search['value']) {
        var regex = new RegExp(req.body.search.value, "i");
        searchStr =  { $or: [{'name': regex }, {'user.lastName': regex}, {'category.title': regex }] };
    }

     const userLookup = {
        $lookup: {
            from: 'users',
            localField: 'melliCode',
            foreignField: 'melliCode',
            as: 'user'
        }
    };

    const categoryLookup = {
        $lookup: {
            from: 'categories',
            localField: 'category',
            foreignField: '_id',
            as: 'category'
        }
    };

    // QUERY NO 1
    Test.find().estimatedDocumentCount({}, function(err, c) {

        if(err) console.log(err);
        let recordsTotal = c;

        // QUERY NO 2
        Test.aggregate([
            userLookup,
            categoryLookup,
            {
                $match: searchStr
            },
            {
                $count: "filtered"
            }
            ])
            .then(count => {

            const recordsFiltered = count[0].filtered;

            // QUERY NO 3
            Test.aggregate([
                userLookup,
                categoryLookup,
                {
                    $unwind: '$user'
                },
                {
                    $unwind: '$category'
                },
                {
                    $match: searchStr
                },
                {
                    $skip: Number(req.body.start) || 0
                },
                {
                    $limit: Number(req.body.length),
                }

            ])
                .then(results => {

                    if(!results) return res.send('error');

                    res.json({
                        "draw": req.body.draw,
                        "recordsFiltered": recordsFiltered,
                        "recordsTotal": recordsTotal,
                        "data": results
                    });
                })
                .catch((error) => {
                    if(error) console.log(err);
                    res.send(error)
                })
        })
            .catch((error) => {
                console.log(error);
            })
    })
})
\$\endgroup\$
2
  • \$\begingroup\$ With some databases this can be done with one query. The result of query should tell you how many result rows there are (recordsFiltered) and give you the data. And some databases also have an option to see the total numbers of rows touched (recordsTotal). This is how I do it in PHP with MySQL.... not that that helps you in any way, but now you know it is at least "possible". One other thing: I guess you have checked your database indexes? These can have an enormous effect on the speed of queries. \$\endgroup\$ Commented Apr 30, 2019 at 12:07
  • \$\begingroup\$ that what I was thinking, there should be a way to get a count in an easier way in mongodb, and yes I have checked my indexes, but again this is not very efficient. @KIKOSoftware \$\endgroup\$ Commented Apr 30, 2019 at 14:49

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.