Build Mongo Query based on query parameters in url

Question

I want to build a query as per the value available in the query parameters of the URL. The sample URL is :-

http://localhost:3000/api/user?page=&status=&search&role_id=

So when the status is available, the where clause related to it should work, the same for search and role_id.

I have tried to built a query where pagination part along with search parameter is working perfectly. But when I pass/set the keys of query string, the query shows no results.

The query I have build so far is something like this:-

let {page, search, status, role_id} = req.query;
role_id = role_id ? role_id : null;
status = status ? status : null;
const currentPage = parseInt(page) || 1;
const perPage = recordsPerPage;
const userData = await User.find({
                                $and: [
                                    {
                                        $or : [
                                                {username:{'$regex' : search, '$options' : 'i'}},
                                                {email:{'$regex' : search, '$options' : 'i'}}
                                            ]
                                    },
                                    {
                                        $or : [
                                            {status : status}
                                        ]
                                    },
                                    {
                                            $or : [
                                                {role_id : role_id}
                                            ]
                                    },
                                    {
                                        email: { $ne: '[email protected]' }
                                    }
                                ]
                            })
                            .sort({_id : -1})
                            .populate('role_id')
                            .skip((currentPage - 1) * perPage).limit(perPage);

caffeinated.tech · Accepted Answer · 2021-04-28 15:34:36Z

1

Instead of trying to rely on $or and $and in your query, you can just conditionally add fields to your query. A mongoose query is just passed an object and can be built outside the function call.

This way you won't have to worry about edge cases such as a user with status null being returned whenever you leave out the status field from your query.

let {page, search, status, role_id} = req.query;
role_id = role_id ? role_id : null;
status = status ? status : null;
const currentPage = parseInt(page) || 1;
const perPage = recordsPerPage;
const query ={
    email: { $ne: '[email protected]' }
}
if (status) {
    query.status = status
}
if (role_id) {
    query.role_id = role_id
}
if (search) {
    query.username = {'$regex' : search, '$options' : 'i'}
    delete query.email
    query['$and'] = [
        { email: {'$regex' : search, '$options' : 'i'}},
        { email: { $ne: '[email protected]' } }
    ]
}
const userData = await User.find(query)
    .sort({_id : -1})
    .populate('role_id')
    .skip((currentPage - 1) * perPage).limit(perPage);

As an aside, I think a query with a $regex and a $ne on the same field (email in your example) may be very slow once executed on a large collection.

edited Apr 28, 2021 at 15:34

answered Apr 28, 2021 at 14:14

caffeinated.tech

6,5561 gold badge24 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Varun.Kumar Over a year ago

okay, I will try this out. As for your side note, the query will only work on 10 collections at a time as it is combined with pagination. Also, if you don't mind, would you let me know what the following block is doing:- delete query.email

Varun.Kumar Over a year ago

Just a small update to your code:- ``` query['$and'] = [ { email: {'$regex' : search, '$options' : 'i'}}, { email: { $ne: '[email protected]' } } ] ```

caffeinated.tech Over a year ago

@Varun.Kumar Thanks, I applied the edit. I delete the default email not equal to part of the query, as I then add it back with the regex in the $and query. This may not be needed - I don't know what would happen if email field is in the query multiple times. You can try both versions, and use mongo's explain to see how the query is executed

Collectives™ on Stack Overflow

Build Mongo Query based on query parameters in url

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related