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

1

Hello,

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,
Philipp

2

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

3

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, employees.id FROM employees

INNER JOIN (

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 = employees.id AND emServiceRelation.service_id = 1)

4

Maybe something like

$employees->find()
    ->contain('EmServiceRelations')
    ->order(['EmServiceRelations.date_from' => 'DESC'])
    ->where(['EmServiceRelations.date_from <=' => FrozenDate::now()])
    ->limit(1);

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

5

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])
->orderDesc(‘date_from’)
->limit(1);

})->where([
‘finderEmServiceFacilityRelations.service_facility_id’ => 46
]);

return $Query;

Best regards,
Philipp

6

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])
    ->select('finderEmServiceFacilityRelations.service_facility_id')
    ->orderDesc('finderEmServiceFacilityRelations.date_from')
    ->limit(1);

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

  return $Query->where([
    'finderEmServiceFacilityRelations.service_facility_id' => $FinderEmServiceFacilityRelations
  ])->andWhere([
    'finderEmServiceFacilityRelations.service_facility_id' => 46
  ]);

});