I'm trying to know if specific motorcycle in date range has contract or not.
My schema looks like:
{
"_id" : ObjectId("575b7c0b0419c906e262d54b"),
"customer" : {
"id" : ObjectId("575b7c0b0419c906e262d54b")
},
"name" : "Harley Store",
"description" : "Harley Store",
"contracts" : [
{
"_id" : ObjectId("575b7c0b0419c906e262d54b"),
"bike" : {
"id" : ObjectId("575b7c0b0419c906e262d54b")
},
"from" : ISODate("2050-01-01T00:00:00.000Z"),
"till" : ISODate("2050-01-05T00:00:00.000Z"),
"cost" : 10000,
"lapse" : [
ISODate("2050-01-01T00:00:00.000Z"),
ISODate("2050-01-02T00:00:00.000Z"),
ISODate("2050-01-03T00:00:00.000Z"),
ISODate("2050-01-04T00:00:00.000Z"),
ISODate("2050-01-05T00:00:00.000Z")
]
},
{
"_id" : ObjectId("575b7c0b0419c906e262d54c"),
"bike" : {
"id" : ObjectId("575b7c0b0419c906e262d54c")
},
"from" : ISODate("2050-01-01T00:00:00.000Z"),
"till" : ISODate("2050-01-05T00:00:00.000Z"),
"cost" : 10000,
"lapse" : [
ISODate("2050-01-06T00:00:00.000Z"),
ISODate("2050-01-07T00:00:00.000Z"),
ISODate("2050-01-08T00:00:00.000Z"),
ISODate("2050-01-09T00:00:00.000Z")
]
}
]
}
I have the following query in the mongo shell:
db.getCollection('BikeStore').aggregate([
{
$unwind:'$contracts'
},
{
$project:{
contract:'$contracts',
_id: 0
}
},
{
$match:{
'contract.bike.id': ObjectId("575b7c0b0419c906e262d54b")
}
},
{
$match:{
$or: [
{'contract.lapse': {$eq: ISODate("2049-01-31T00:00:00.000Z")}},
{'contract.lapse': {$eq: ISODate("2050-02-01T00:00:00.000Z")}},
{'contract.lapse': {$eq: ISODate("2050-02-02T00:00:00.000Z")}}
]
}
}
])
The query in mongo shell works fine, but the dates are generated dynamically from-till and I can not find the way to get this done using query builder.
My query builder:
public function hasContracts(string $bikeId, \DateTime $from, \DateTime $till): bool
{
$filterDate = \DateTimeImmutable::createFromMutable($from);
$days = $from->diff($till)->days;
$qb = $this->createAggregationBuilder();
$qb->unwind('$contracts');
$qb->project()
->field('contract')
->expression('$contracts')
->field('_id')
->expression(0);
$qb->match()->field('contract.bike.id')->equals(new ObjectId($bikeId));
for($i;$days){ //$i menor or equal $days
$qb->match()->addOr(
$qb->matchExpr()->field('contract.lapse')->equals(
new UTCDateTime(
$filterDate->add(
\DateInterval::createFromDateString(sprintf('%d day', $i)))
->setTime(0, 0)->getTimestamp() * 1000
)
)
);
}
return 0 !== $qb->execute()->count();
}
The query that generates the odm is the following:
{
"aggregate": true,
"pipeline": [
{
"$unwind": "$contracts"
},
{
"$project": {
"contract": "$contracts",
"_id": 0
}
},
{
"$match": {
"contract.bike.id": {
"$oid": "575b7c0b0419c906e262d54b"
}
}
},
{
"$match": {
"$or": [
{
"contract.lapse": {
"$date": {
"$numberLong": "2524780800000"
}
}
}
]
}
},
{
"$match": {
"$or": [
{
"contract.lapse": {
"$date": {
"$numberLong": "2524867200000"
}
}
}
]
}
},
{
"$match": {
"$or": [
{
"contract.lapse": {
"$date": {
"$numberLong": "2524953600000"
}
}
}
]
}
},
{
"$match": {
"$or": [
{
"contract.lapse": {
"$date": {
"$numberLong": "2525040000000"
}
}
}
]
}
},
{
"$match": {
"$or": [
{
"contract.lapse": {
"$date": {
"$numberLong": "2525126400000"
}
}
}
]
}
},
{
"$match": {
"$or": [
{
"contract.lapse": {
"$date": {
"$numberLong": "2525212800000"
}
}
}
]
}
},
{
"$match": {
"$or": [
{
"contract.lapse": {
"$date": {
"$numberLong": "2525299200000"
}
}
}
]
}
},
{
"$match": {
"$or": [
{
"contract.lapse": {
"$date": {
"$numberLong": "2525385600000"
}
}
}
]
}
},
{
"$match": {
"$or": [
{
"contract.lapse": {
"$date": {
"$numberLong": "2525472000000"
}
}
}
]
}
},
{
"$match": {
"$or": [
{
"contract.lapse": {
"$date": {
"$numberLong": "2525558400000"
}
}
}
]
}
},
{
"$match": {
"$or": [
{
"contract.lapse": {
"$date": {
"$numberLong": "2525644800000"
}
}
}
]
}
},
{
"$match": {
"$or": [
{
"contract.lapse": {
"$date": {
"$numberLong": "2525731200000"
}
}
}
]
}
},
{
"$match": {
"$or": [
{
"contract.lapse": {
"$date": {
"$numberLong": "2525817600000"
}
}
}
]
}
},
{
"$match": {
"$or": [
{
"contract.lapse": {
"$date": {
"$numberLong": "2525904000000"
}
}
}
]
}
},
{
"$match": {
"$or": [
{
"contract.lapse": {
"$date": {
"$numberLong": "2525990400000"
}
}
}
]
}
},
{
"$match": {
"$or": [
{
"contract.lapse": {
"$date": {
"$numberLong": "2526076800000"
}
}
}
]
}
},
{
"$match": {
"$or": [
{
"contract.lapse": {
"$date": {
"$numberLong": "2526163200000"
}
}
}
]
}
},
{
"$match": {
"$or": [
{
"contract.lapse": {
"$date": {
"$numberLong": "2526249600000"
}
}
}
]
}
},
{
"$match": {
"$or": [
{
"contract.lapse": {
"$date": {
"$numberLong": "2526336000000"
}
}
}
]
}
},
{
"$match": {
"$or": [
{
"contract.lapse": {
"$date": {
"$numberLong": "2526422400000"
}
}
}
]
}
},
{
"$match": {
"$or": [
{
"contract.lapse": {
"$date": {
"$numberLong": "2526508800000"
}
}
}
]
}
},
{
"$match": {
"$or": [
{
"contract.lapse": {
"$date": {
"$numberLong": "2526595200000"
}
}
}
]
}
},
{
"$match": {
"$or": [
{
"contract.lapse": {
"$date": {
"$numberLong": "2526681600000"
}
}
}
]
}
},
{
"$match": {
"$or": [
{
"contract.lapse": {
"$date": {
"$numberLong": "2526768000000"
}
}
}
]
}
},
{
"$match": {
"$or": [
{
"contract.lapse": {
"$date": {
"$numberLong": "2526854400000"
}
}
}
]
}
},
{
"$match": {
"$or": [
{
"contract.lapse": {
"$date": {
"$numberLong": "2526940800000"
}
}
}
]
}
},
{
"$match": {
"$or": [
{
"contract.lapse": {
"$date": {
"$numberLong": "2527027200000"
}
}
}
]
}
},
{
"$match": {
"$or": [
{
"contract.lapse": {
"$date": {
"$numberLong": "2527113600000"
}
}
}
]
}
},
{
"$match": {
"$or": [
{
"contract.lapse": {
"$date": {
"$numberLong": "2527200000000"
}
}
}
]
}
},
{
"$match": {
"$or": [
{
"contract.lapse": {
"$date": {
"$numberLong": "2527286400000"
}
}
}
]
}
},
{
"$match": {
"$or": [
{
"contract.lapse": {
"$date": {
"$numberLong": "2527372800000"
}
}
}
]
}
},
{
"$match": {
"$or": [
{
"contract.lapse": {
"$date": {
"$numberLong": "2527459200000"
}
}
}
]
}
}
],
"options": {
"cursor": true
},
"db": "store",
"collection": "BikeStore"
}
How do I add the dates dynamically into the match and not duplicate the match ?
Thx for you help!!!
Each time you call $qb->match() you're creating a new $match stage. This should do:
$qb->match();
for($i;$days){ //$i menor or equal $days
$qb->addOr(/* ... */);
}