MongoDB multiple $and operators query in PHP

Question

I’m having trouble with this MongoDB query using PHP array syntax. This is a direct version of the query I want to use.

db.collection.find({
        $or: [
            {$and : [{X:1}, {X: {$gt: 100}}]},
            {$and : [{X:2}, {X: {$lt: 100}}]}
        ]
});

Note: The real query is more complicated, this is just an example.

I wasn’t able to find some examples describing this kind of query in PHP. The best I’ve come up was this:

$query = array(
    '$or' => array(
        array(
            '$and' => array(
                array('X' => 1),
                array('X' => array('gt' => 100))
            )
        ),
        array(
            '$and' => array(
                array('X' => 2),
                array('X' => array('lt' => 100))
            )
        ),
    )
);

$this->db->collection->find($query);

But this query doesn't return any results. Obviously we can't remove $and from the array because we can't have duplicate keys in PHP array.

I don't want to use JavaScript expressions because the speed is critical.

UPDATE: As Alexander Azarov pointed out in comments, my original query can be written differently. I've updated the question with the properly used $and query.

Fiona

What exactly doesn't work? Do you have error messages? – Harker

The query executes fine but returns 0 results. – Fiona

Could you post a sample data dump? – Harker

BTW, @ChristianP , you do not need $and here. {X:1, Y: {$all: [5,6,7]}} should work. – Infantry

Does equivalent query in mongo shell find these records? – Harker

@SergioTulentsev The query in mongo shell finds there records ok. But this query doesn't find anything when I try to run it from PHP. Is the syntax correct? – Fiona

@ChristianP: I don't know, I'm not a PHP guy :-) – Harker

But, logically thinking, the problem is in your PHP. Either your syntax is off, or the driver doesn't support this kind of queries yet. – Harker

@AlexanderAzarov thanks it works. But then, why the $and operator exists? – Fiona

@SergioTulentsev It crossed my mind. I didn't found any code examples for this kind of queries so I don't know if this is isn't supported or if my syntax is wrong when I try to use multiple $and operators. – Fiona

@ChristianP: $and exists for this king of thing: db.foo.find( { $and: [ { a: 1 }, { a: { $gt: 5 } } ] } ) – Harker

@AlexanderAzarov never mind, I re-read the MongoDB documentation about $and. – Fiona

Answer 1

Here is an example of the code with or and and operators in PHP for a Mongo query.

It uses different field names from what you are going to find in your query, but it is straightforward to understand how to use it.

Right now I don't have time to change it, to match your query, but if you have problems with that, let me know and I will adjust it. This query is working and finding what it should find.

$arrFind = array(
    '$or' => array(
        array(
            '$and' => array(
                array(
                    UI_name => array(
                        '$regex' => 'andrew',
                        '$options' => 'i'
                    )
                ),
                array(
                    UI_surname => array(
                        '$regex' => 'mik',
                        '$options' => 'i'
                    )
                )
            )
        ),
        array(
            '$and' => array(
                array(
                    UI_surname => array(
                        '$regex' => 'andrew',
                        '$options' => 'i'
                    )
                ),
                array(
                    UI_name => array(
                        '$regex' => 'mik',
                        '$options' => 'i'
                    )
                )
            )
        ),
    )
);

Blink