Using NotIn & Regex in MongoDB with PHP

Question

I have a table with some user email address like:

    johndoe@somemail.com
    test@test_mail.com
    test2@test_mail.com
    admin@company_mail.com
    janedoe@someothermail.com
    sales@company_mail.com
    mruser@validmail.com

I want to get the user list with emails not ending with @test_mail.com OR @company_mail.com. I succeeded this with following mongo query:

db.users.find({
    userEmail: { 
        $nin: [/@test_mail.com$/,/@company_mail.com$/] 
    }
})

I trid to run the same query in PHP with following code but couldn't make it work:

$criteria = array(
    "userEmail" => array(
        '$nin' => array(
            '$regex' => new \MongoRegex("/@test_mail.com|@company_mail.com/i")
        )
    )
);
$cursor = $collection->find($criteria);

Any suggestions?

Alexey Kosov · Answer 1 · 2014-10-02T09:07:42.020

0

You shouldn't use $nin in this case, I would try something like this:

$criteria = array(
    "userEmail" => array(
        '$not' => array(
            '$or' => array(
                 '$regex' => new \MongoRegex("/@test_mail\.com$/i"),
                 '$regex' => new \MongoRegex("/@company_mail\.com$/i"),
             ),
        ),
    )
);

UPD:

you can try this:

$criteria = array(
    '$not' => array(
        '$or' => array(
            "userEmail" => array('$regex' => new \MongoRegex("/@test_mail\.com$/i")),
            "userEmail" => array('$regex' => new \MongoRegex("/@company_mail\.com$/i")),
        ),
    ),
    )
);

edited Oct 02 '14 at 09:07

answered Oct 02 '14 at 08:41

Alexey Kosov

3,010
2
23
32

Thank you for your suggestion but I get following MongoCursorException when I used your code: invalid use of $not – emrec Oct 02 '14 at 08:55
Now I am getting "invalid operator: $or" MongoCursorException with the updated code – emrec Oct 02 '14 at 10:28

score 0 · Accepted Answer · answered Oct 02 '14 at 09:41

The correct way to apply this is with a singular MongoRegex object and the $not operator to reverse the condition:

$cursor = $users->find(array(
  "userEmail" => array(
    '$not' => new MongoRegex('/\@test_mail\.com$|\@company_mail\.com$/i')
  )
));

foreach ( $cursor as $doc ) {
  var_dump( $doc );
}

The same applies to $nin where you can actually specify a "regex" argument, but it must be an regular expression "object" type and not the operator form:

$cursor = $user->find(array(
  "userEmail" => array(
    '$nin' => array(new MongoRegex('/\@test_mail\.com$|\@company_mail\.com$/i'))
  )
));


foreach ( $cursor as $doc ) {
  var_dump( $doc );
}

But not really necessary as you are not providing an "array" of different values, the regular expression can represent the "or" condition itself.

Using NotIn & Regex in MongoDB with PHP

2 Answers2