I try to get some results filtered with a condition if data does not exist or if exists and is older than...
I have query build with PHP query builder
->addOr(
['interactions.lastDisplays' => ['$elemMatch' => [
'user' => new \MongoId($id),
'date' => ['$lte' => $date]
]]],
['interactions.lastDisplays.user' => ['$ne' => new \MongoId($id)]]
)
And it logs to:
db.Post.find({
"$or": [
{ "interactions.lastDisplays": {
"$elemMatch": { "user": ObjectId("5a61bb816e0bb1542a0bc574"),
"date": { "$lte": new ISODate("2019-06-14T13:22:01+00:00") } } } },
{ "interactions.lastDisplays.user": {
"$ne": ObjectId("5a61bb816e0bb1542a0bc574") }
}]
}).limit(25).skip(0);
For some reason, it does not return any results in PHP. When I copy it and run directly on a database it works fine, and all 25 results are returned.
The solution was as simple as changing $lte
to MongoDate.
->addOr(
['interactions.lastDisplays' => ['$elemMatch' => [
'user' => new \MongoId($id),
'date' => ['$lte' => $new \MongoDate($date->getTimestamp())]
]]],
['interactions.lastDisplays.user' => ['$ne' => new \MongoId($id)]]
)
The strange thing that I don't understand yet is that it used to work with DateTime
in other queries (but they don't have nested array conditions, so maybe it's the issue). And also that query log is pretty the same.