MongoDB (First Column bigger than Second Column?)(php)

Question

I'll explain this with mysql query.

select * from stock where home>away

home away
1    3
2    1
1    0
4    5

I just wanna make same query with mongodb. but I couldn't make this.

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

I need some help please for PHP usage.

Answer 1

You can not do this directly with a MongoDB query. Queries in MongoDB only allow comparisons with static values. However, there are a few options.

First of all, you can just store the result of the comparison whenever you update the values in each field, f.e., you can store 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 additional benefit that you can set an index on gt. Of course, it does mean more overhead when updating values. Doing this sort of pre-calculation is very similar to denormalisation. Denormalisation is something you will often have to do in NoSQL databases in order to make most of the system.

There is an alternative, but it wouldn't allow you to do an indexed search on >. You can use the aggregation framework in the following way:

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 step ($match), we then filter out all the documents where the difference is less than or equal to 0.

The answer of the aggregation is:

{
    "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

Sadly, $gt cannot compare two fields.

What you can do for non time critical queries 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 though if you just store an additional "diff" field in the row object and search on that 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 }