plastikbaum plastikbaum - 2 months ago 23
PHP Question

Query where timestamp field is older than another timestamp field in MongoDB with PHP

How can I obtain an object from a MongoDB collection where a specific field1 (timestamp or date) is older/newer than another specific field2 (timestamp or date)?

Given the following example object:

// MongoDB 3.2
{
name: 'test',
updated_on: Timestamp(1474416000, 0),
export: {
active: true,
last_exported_on: Timestamp(1474329600, 0)
}
}


This object should match a query like:
where export.active is true and updated_on > export.last_exported_on


I've tried it with the aggregation framework, since I've read that $where can be very slow, but without any success.

// PHP 5.4 (and MongoDB PHP lib. http://mongodb.github.io/mongo-php-library)
$collection->aggregate([
['$project' => [
'dst' => ['$cmp' => ['updated_on', 'export.last_exported_on']],
'name' => true
]],
['$match' => ['dst' => ['$gt' => 0], 'export.active' => ['$eq' => true]]],
['$limit' => 1]
]);


I can change timestamps into date or anything else, but I don't see the problem in the type.

Edit: Not all objects have the
last_exported_on
or the
export
fields at all. Besides that both can be null or empty or 000000.

Answer

That's because after you do the $project you end up only with the dst and _id fields, so you cannot $match on export.active. You need to match on export.active before the projection. After that you need another match on the dst field.

[
    {
        $match: {
            "export.active": true
        }
    },
    {
        $project: {
            dst: {
                $cmp: [
                    "$updated_on",
                    "$export.last_exported_on"
                ]
            }
        }
    },
    {
        $match: {
            dst: 1
        }
    }
]

Edit

Alternatively, you can make sure to preserve export.active and to spare another $match:

[
    {
        $project: {
            "export.active": 1,
            cmp: {
                $cmp: [
                    "$updated_on",
                    "$export.last_exported_on"
                ]
            }
        }
    },
    {
        $match: {
            cmp: 1,
            "export.active": true
        }
    }
]
Comments