FrozenTime in where clause

Need Help
1

I have the following sample code:

$query = TableRegistry::getTableLocator()->get('Decedents')->find('all')
                        ->contain(['Categories']);
                        
                $baseExpression = $query->newExpr()->or([
                                             $query->newExpr()->between('Appointments.dtBeginn', $begin, $end),
                                             $query->newExpr()->between('Appointments.dtEnde', $begin, $end),
                                  ]);
                                  
                    
                $ressourcen = $query
                        ->select([
                          'nAppointmentCount' => $query->func()->count('Appointments.id'), 
                          'nRessourcen' => $query->func()->sum('Categories.nRessourcen')
                        ])
                        ->innerJoinWith('Appointments', function($q) use ($baseExpression, $begin) {
                          return $q->where($baseExpression)
                          ->where(['Appointments.dtBeginn' => FrozenTime::now()])
                          ->where(['Appointments.dtBeginn' => $begin]);

                        })
                        ->where(['Appointments.dtBeginn' => FrozenTime::now()])
                        ->first();

And here’s the resulting SQL query:

SELECT TOP 1 (COUNT(Appointments.id)) AS nAppointmentCount, (SUM(Categories.nRessourcen)) AS nRessourcen FROM Verstorbene Decedents INNER JOIN TrmBelegung Appointments ON ((Appointments.dtBeginn BETWEEN '26.04.24, 10:01' AND '26.04.24, 12:59' OR Appointments.dtEnde BETWEEN '26.04.24, 10:01' AND '26.04.24, 12:59') AND Appointments.dtBeginn = '2024-04-23 11:58:58' AND Appointments.dtBeginn = '2024-04-26 08:01:00' AND Appointments.nVNr = Decedents.nVNr) LEFT JOIN TrmKategorie Categories ON ((Decedents.nFNr = Categories.nFNr OR Categories.nFNr IS NULL) AND (Categories.mandant_id = Decedents.mandant_id AND Categories.nSchl = Decedents.nKategorie) AND (Categories.deleted) IS NULL) WHERE Appointments.dtBeginn = '2024-04-23 11:58:58'

The variables $begin and $end are FrozenTime objects with a Timezone property. In the database, I persist UTC datetimes, so these values have to be converted.

Sometimes the values were converted to utc by the framework and formatted correctly, sometimes not. But why?

2

Very weird that the between clauses are formatting the same variable differently than the other where clause does.

3

okay, some new insights:

$query = TableRegistry::getTableLocator()->get('Decedents')->find('all')
                        ->contain(['Categories', 'Appointments']);
                
                $query
                    ->select([
                        'nTermine' => $query->func()->count('Appointments.id'), 
                        'nRessourcen' => $query->func()->sum('Categories.nRessourcen')
                    ]);
                
                   
                $baseExpression = $query->newExpr()->or([
                                             $query->newExpr()->between('Appointments.dtBeginn', $uhrzeitbeginn, $uhrzeitende),
                                             $query->newExpr()->between('Appointments.dtEnde', $uhrzeitbeginn, $uhrzeitende),
                                             $query->newExpr()->and([
                                                $query->newExpr()->eq('Appointments.dtBeginn', $term->dtBeginn),
                                                $query->newExpr()->eq('Appointments.dtEnde', $term->dtEnde)
                                             ])
                                  ]);
                    
                $ressourcen = $query
                        ->where($baseExpression)
                        ->where(['Decedents.mandant_id' => $this->mandant_id])
                        ->where($query->newExpr()->in('Appointments.kBelegt', ['R', 'B']))
                        /*
                        ->matching('Appointments', function($q) use ($baseExpression) {
                            return $q->where($baseExpression);
                        })*/
                        /*
                        ->matching('Appointments.Groups', function ($q) use ($group) {
                            return $q->where(['Groups.nId' => $group->nId]);
                        })*/
                        ->first();

SELECT TOP 1 (COUNT(Appointments.id)) AS nTermine, (SUM(Categories.nRessourcen)) AS nRessourcen FROM Verstorbene Decedents LEFT JOIN TrmKategorie Categories ON ((Decedents.nFNr = Categories.nFNr OR Categories.nFNr IS NULL) AND (Categories.mandant_id = Decedents.mandant_id AND Categories.nSchl = Decedents.nKategorie) AND (Categories.deleted) IS NULL) LEFT JOIN TrmBelegung Appointments ON (Appointments.mandant_id = Decedents.mandant_id AND Appointments.nVNr = Decedents.nVNr) WHERE ((Appointments.dtBeginn BETWEEN ‘2024-04-26 08:01:00’ AND ‘2024-04-26 10:59:00’ OR Appointments.dtEnde BETWEEN ‘2024-04-26 08:01:00’ AND ‘2024-04-26 10:59:00’ OR (Appointments.dtBeginn = ‘2024-04-26 09:00:00’ AND Appointments.dtEnde = ‘2024-04-26 10:00:00’)) AND Decedents.mandant_id = 3015 AND Appointments.kBelegt IN (‘R’,‘B’))

The values can be converted and formatted correctly when I include the appointment association in the contain() method.

However, I have a hasAndBelongsToMany association to Groups, and when I uncomment the matching method to Appointments.Groups, another exception occurs.

When I attempt to execute the matching('Appointments', ...) method, it cannot format the DateTime fields correctly.

4

Very weird, but this works. I needed to include the type parameter in both the between and eq expressions.

$query->newExpr()->between('Appointments.dtBeginn', $uhrzeitbeginn, $uhrzeitende, 'datetime')

When you add the association ‘Appointments’ to contain() and you have no other associations it works without the ‘datetime’ parameter. However, i have matching(‘Appointments.Groups’ in my query, which includes to load the ‘appointments’ association, and then it doesn’t work without the 4th parameter.