phpsqlcakephpquery-buildercakephp-4.x

Get all dates within a current month CakePHP 4 Query Builder


Trying to get all dates within a given month using CakePHP 4 Query Builder. The following raw query works, and gets the correct dates. Dates are stored in the format Y-m-d

 $connection = ConnectionManager::get('default');
        $exams = $connection->execute('SELECT * FROM ielts_exam_dates WHERE MONTH(date_of_exam) = MONTH(CURRENT_DATE);')
        ->fetchAll('assoc');

Whereas the following, and most variations of doesn't work and returns only an empty set. There are no errors, simply just not found any dates. Think I've tried date formatting as well.

    $venues = $this->IeltsVenues->find()
            ->contain([
                'Venues', 
                'Venues' => [
                    'VenueAddresses', 
                    'VenueDetails'
                ],
                'IeltsExamDates' => function (Query $q) use ($moduleId) {
                    return $q->where(['AND' => [
                        'ielts_module_type_id' => $moduleId,
                        'MONTH(date_of_exam)' => 'MONTH(CURRENT_DATE)', 
                        ]])
                    ->contain(['IeltsModuleTypes', 'IeltsExamTypes']);
                }
            ])
            ->where(['ielts_city_id' => $cityId]);

Whilst I can do the raw joins and sql, it would be nice to see if this is possible with the query builder. I have looked through the docs, and not quite seen anything similar to it. Any help would be appreciated


Solution

  • Got the answer from someone else on the Official CakePHP Discord, but once parameterized it's like so. End function is for a calendar, otherwise its 'CURRENT_DATE' where ':exam_date' is.

    'IeltsExamDates' => function (Query $q) use ($moduleId, $date) {
                        return $q->where(['AND' => [
                            'ielts_module_type_id' => $moduleId,
                            'MONTH(date_of_exam) = MONTH(:exam_date)', 
                            ]])->bind(':exam_date', $date, 'date')
                        ->contain(['IeltsModuleTypes', 'IeltsExamTypes']);