CakePHP query: where condition with calculated fields

266 Views Asked by At

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.

0

There are 0 best solutions below