MongoDB (is the first column larger than the second column?) (Php)

Question

I will explain this with a mysql query.

select * from stock where home>away home away 1 3 2 1 1 0 4 5 

I just want to make the same request with mongodb. but I could not do it.

 array('column' => array('$gt' => what should I write here?)) 

I need help, please, for using PHP.

Answer 1

You cannot do this directly with a MongoDB query. Queries in MongoDB allow only static values ​​to be compared. However, there are several options.

First of all, you can just save the comparison result whenever you update the values ​​in each field, i.e. You can save it as:

 home away gt 1 3 0 2 1 1 1 0 1 4 5 0 

This is the simplest solution and has the added benefit you can set for an index on gt . Of course, when updating the values, this means additional overhead. Performing this kind of preliminary calculation is very similar to denormalization. Denormalization is something you often have to do in NoSQL databases to make the most of the system.

There is an alternative, but that will not allow you to perform an indexed search on > . You can use the aggregation structure as follows:

 db.so.aggregate( [ { $project: { 'away' : 1, 'home': 1, 'better_at_home': { $cmp: [ '$home', '$away' ] } } }, { $match: { 'better_at_home': { $gt: 0 } } } ] ); 

In the first step, we use $cmp to compare home and away . In the second stage ( $match ), we then filter out all documents where the difference is less than or equal to 0.

Aggregation Response:

 { "result" : [ { "_id" : ObjectId("51ee7cfb812db9ff4412f12f"), "home" : 2, "away" : 1, "better_at_home" : 1 }, { "_id" : ObjectId("51ee7cff812db9ff4412f130"), "home" : 1, "away" : 0, "better_at_home" : 1 } ], "ok" : 1 } 

Answer 2

Unfortunately, $gt cannot compare two fields.

What you can do for non-critical time requests is to use $where ;

 > db.test.insert({home:5, away:3}) > db.test.insert({home:1, away:3}) > db.test.find({$where: 'this.home > this.away'}) { "_id" : ObjectId("51ec576418fd21f745899945"), "home" : 5, "away" : 3 } 

Your performance will be better if you just save the extra "diff" field in the string object and search using $gt:0 .

 > db.test.insert({home:5, away:3, diff:2}) > db.test.insert({home:1, away:3, diff:-2}) > db.test.find({diff: {$gt:0}}, {_id:1,home:1,away:1}) { "_id" : ObjectId("51ee982a6e4b3b34421de7bc"), "home" : 5, "away" : 3 } 

Answer 3

You cannot use regular queries for this, but never fear, the aggregation structure can help with the $cmp operator: http://docs.mongodb.org/manual/reference/aggregation/cmp/

 db.collection.aggregate({$project:{c: {$cmp:['$col1','$col2']}}},{$match:{c:{$gt:0}}}) 

However, as said, it is not very index-friendly and is currently limited to 16 million results.

change

To account for your changes:

 $mongo->collection->aggregate(array( array('$project'=> array( 'c'=>array('$cmp'=>array('$col1','$col2')) ) ), array('$match'=>array('c'=>array('$gt'=>0))) ))