Best practise for a trivial Problem (orm, mapper, finder, reducer)


I have a question. In my software there is a table “employees”. This has several “hasMany” associations in which there is a “date_from”. I need now from the table always the association, which fit to a certain date. eg: The date is “2020-01-05” there are associated records with “2020-01-01” and one with “2020-02-01”. The associated record should now be only the one with the date “2020-01-01”, since that is valid on that date. I can’t solve it via a “finder” now, I thought of a “mapper”.Problem: I can only call it separately and not register it in the “finder”. The “mapReducer” always changes the structure (that’s what it’s for), but I can’t register a “normal mapper” in the “finder”. The question now is, is there some kind of “best practice” to solve such a relatively trivial problem? I am thankful for any answer.

Kind regards,

Can you not do this directly in the query by finding the record with the maximum date_from that’s less than today?

Thanks for your answer! Yes, it was a thinking error of mine, it would work but I don’t know how to implement it with CakePHP (4.2). This is how the SQL would have to look like:

SELECT employees.last_name, employees.first_name, FROM employees


SELECT MAX(em_service_relations.date_from), em_service_relations.* FROM em_service_relations WHERE em_service_relations.date_from <= ‘2021-01-04’

) emServiceRelation ON (emServiceRelation.employee_id = AND emServiceRelation.service_id = 1)

Maybe something like

    ->order(['EmServiceRelations.date_from' => 'DESC'])
    ->where(['EmServiceRelations.date_from <=' => FrozenDate::now()])

On second thought, this is almost certainly wrong, but maybe gives you a starting point…

Thanks for your help. I have now been able to solve it this way:

$Date = new FrozenDate(‘2021-02-01’);

$Query->innerJoinWith(‘finderEmServiceFacilityRelations’, function($Query) use($Date) {

return $Query->where([‘date_from <=’ => $Date])

‘finderEmServiceFacilityRelations.service_facility_id’ => 46

return $Query;

Best regards,

The first solution is wrong, this one is correct: :slight_smile:

$Date = new FrozenDate(‘2021-01-31’);

$FinderEmServiceFacilityRelations =
  $this->getAssociation('finderEmServiceFacilityRelations')->find()->where(['finderEmServiceFacilityRelations.date_from <=' => $Date])

$Query->innerJoinWith('finderEmServiceFacilityRelations', function(Query $Query) use($Date, $FinderEmServiceFacilityRelations) {

  return $Query->where([
    'finderEmServiceFacilityRelations.service_facility_id' => $FinderEmServiceFacilityRelations
    'finderEmServiceFacilityRelations.service_facility_id' => 46