Query with date range works when executed directly on Mongo but fails when used from Laravel

Question

I have two collections called promotions and coupons
Coupons has promotion._id as reference to promotion collection.

I want to fetch the promotions within a date range. For this, I have two fields valid_from and apply_till.

"valid_from" : ISODate("2018-09-10T06:15:02.000Z"),
"apply_till" : ISODate("2018-10-20T06:15:02.000Z")

This direct MongoDb query works when executed directly:

db.getCollection('coupons').aggregate([
        {"$match":{"code":"PYDY25"}},
        {"$lookup":{"from":"promotions","localField":"promotion_id","foreignField":"_id","as":"promotion"}},
        {"$match":{
            "promotion.valid_from":{"$lte": ISODate("2018-09-12 06:31:30")},
            "promotion.apply_till":{"$gt": ISODate("2018-09-12 06:31:30")}
          }
        },
        {"$project":{"_id":0,"code":1,"promotion":1}}
    ])

However when I try to do the same via Laravel it doesn't work. The ISODate formatting is missing in the query generated by Laravel.(Have given the generated query below)

In Laravel:

Promotion Model have:

protected $dates = [
  'valid_from',
  'valid_till',
  'apply_till',
  'created_at',
  'updated_at'
];

Raw query used for aggregation in Laravel:

// Filter for code.
$aggregateBy[] = [
  '$match' => [
    'code' => $coupon->code
  ]
];

// JOINing table (promotion) for coupon table.
$aggregateBy[] = [
  '$lookup' => [
    'from' => 'promotions',
    'localField' => 'promotion_id',
    'foreignField' => '_id',
    'as' => 'promotion'
  ]
];

$aggregateBy[] = [
  '$unwind' => '$promotion'
];

$currentTime = Carbon::now()->format('Y-m-d\TH:i:s');
//$currentTime = new UTCDateTime((new \DateTime($currentTime))->getTimestamp());

$aggregateBy[] = [
  '$match' => [
   'promotion.valid_from' => [
      '$lte' => $currentTime,
    ],
    'promotion.apply_till' => [
      '$gt' => $currentTime,
    ]
  ]
];

// SELECT ed fields.
$aggregateBy[] = [
  '$project' => [
    '_id' => 0,
    'code' => 1,
    'promotion' => 1,
  ]
];

\DB::connection('mongodb')->enableQueryLog();

$couponPromotion = Coupon::raw(function ($collection) use ($aggregateBy) {
  return $collection->aggregate($aggregateBy);
});

\Log::info(\DB::connection('mongodb')->getQueryLog());

When I add $match condition for promotion.valid_from and/or promotion.apply_till it does not gives the result. My variable $currentTime is working in Model::where() conditions but not working here.

In the log the query generated as:

db.getCollection('coupons').aggregate([
    {"$match":{"code":"PYDY25"}},
    {"$lookup":{"from":"promotions","localField":"promotion_id","foreignField":"_id","as":"promotion"}},
    {"$match":{
        "promotion.valid_from":{"$lte":"2018-09-12 06:31:30"},
        "promotion.apply_till":{"$gt":"2018-09-12 06:31:30"}}
    },
    {"$project":{"_id":0,"code":1,"promotion":1}}
])

How can I pass ISODate to the generated query?

Query with date range works when executed directly on Mongo but fails when used from Laravel

0 Answers0