lnowaczek.com

Complex queries can be pretty hard to translate. I tried to convert a PostgreSQL query for grouping by a boolean and a date at the same time. Here is my original Eloquent query:

ModelName::selectRaw('COUNT(*) AS "count", boolean_one, date_trunc(\'day\', created_at) as date')
    ->where('created_at', '>=', Carbon::now()->subMonth())
    ->where('boolean_two', '=', $booleanTwo)
    ->where('string_value', 'LIKE', $searchForString . '%')
    ->groupBy('boolean_one')->groupBy('date')
    ->orderBy('date')->get();

And here’s the same query translated, using Moloquent:

ModelName::raw(function ($collection) {
    return $collection->aggregate([
        [
            '$match' => [
                'created_at'   => ['$gt' => new MongoDate(Carbon::now()->subMonths(1)->timestamp)],
                'boolean_two'  => $booleanTwo,
                'string_value' => ['$regex' => new MongoRegex('/.*' . $searchForString . '.*/')]
            ],
        ],
        [

            '$group' => [
                '_id'   => [
                    'month'       => ['$month' => '$created_at'],
                    'day'         => ['$dayOfMonth' => '$created_at'],
                    'year'        => ['$year' => '$created_at'],
                    'boolean_one' => '$boolean_one'
                ],
                'count' => [
                    '$sum' => 1
                ]
            ]
        ]
    ]);
});;