How do I query MongoDB from PHP with IFNULL() expression inside WHERE?

Question

I use this query to query NULL values as "0":

select * from myTable where IFNULL(field_id, 0) = 0

How do I achieve the same in MongoDB using PHP?

My code:

/**
 * @var MongoDB
 */
$db = $this->getMongoDbHandler();

/**
 * @var MongoCollection
 */
$coll = $db->selectCollection('myColl');

/**
 * @var MongoCursor
 */
$cursor = $coll->find($where);

How should I form $where array?

I guess I should do something like:

$where['field_id'] = [
    '$ifNull' => ['field_id', 0]
];

But where do I then indicate the required value?

Thanks!

chridam · Accepted Answer · 2015-11-23 16:07:11Z

1

Create a variable to hold the JavaScript function that returns the null coalescing expression. Here's some sample test documents to test this in mongo shell:

db.test.insert([
    { _id: 1, field_id: 0 },
    { _id: 2, test_field: 1 },
    { _id: 3, field_id: null },    
    { _id: 4, field_id: 3 }
])

I would expect my find query that uses the $where operator to return the documents with _ids 1,2 and 3, thus output for the query

db.test.find({ "$where": "function() { return (this.field_id || 0) == 0; }" })

would be

/* 0 */
{
    "_id" : 1,
    "field_id" : 0
}

/* 1 */
{
    "_id" : 2,
    "test_field" : 1
}

/* 2 */
{
    "_id" : 3,
    "field_id" : null
}

This PHP example demonstrates how to search a collection using javascript code to reduce the resultset using the same concept above:

<?php

/**
 * @var MongoDB
 */
$db = $this->getMongoDbHandler();

/**
 * @var MongoCollection
 */
$coll = $db->selectCollection('myColl');

/**
 * @var JavaScript code
 */
$js = "function() { return (this.field_id || 0) == 0; }" ;

/**
 * @var MongoCursor
 */ 
$cursor = $coll->find(array('$where' => $js));

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

?>

answered Nov 23, 2015 at 16:07

chridam

104k26 gold badges246 silver badges243 bronze badges

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

1 Comment

temuri Over a year ago

Awesome, most appreciated!

Collectives™ on Stack Overflow

How do I query MongoDB from PHP with IFNULL() expression inside WHERE?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related