How to select documents with one field greater than other in MongoDB?

3
votes

I have mongodb documents like this

[_id] => MongoId Object (
    [$id] => 52135a6baabacb9f948b4567
)
[change] => 7
[from] => 8
[to] => 1

How to select documents with field "from" greater than "to" in MongoDB? I tried something like this but it doesn't work at all.

$collection->find( array('from' => array('$gt' => 'to') ) );
2
phpmongodb

2 Answers

7
votes

I would recommend against using $where. It will be slow at scale.

For this use case, you will want to move your logic from your query to your document schema. Add a new attribute to your document called "from_to_diff" and set it to "from" - "to" at document write time.

Then, run the following query:

$collection->find(array('from' => array('$gt' => 0)))

Then, create an index on {from_to_diff: 1}. You will have good cardinality on your index, and not run the massive table scans you will have with $where.

2
votes

It should be like this ;)

$collection->find( array('$where' => 'this.from > this.to'  ) );