I have the followwing problem with creating a query in CakePHP 3:
For the Entity Recordings I want to calculate the age as the difference between the year of the two dates "collection date" and "birthdate"
$query = $this->Recordings->find()
->select(['age' => 'Year(Recordings.collection_date) - Year(Athletes.birthdate)'])
->select($this->Recordings)
->select($this->Recordings->Athletes);
When I try to filter with the following where clause
$query = $query->where(['Year(Recordings.collection_date) - Year(Athletes.birthdate) =' => $age]);
The SQL Code which is created has the Athletes beeing modified to lowercase.
WHERE
( Year(Recordings.collection_date) - year(athletes.birthdate) = '17' )
How do I have to write the where clause correctly? I havent found a way to use Identifiers or query functions like
$year = $q->func()->year(['Athletes.birthdate' => 'identifier']);
to bild the conditions. Any hints are welcome.