I have the following structure:
Animals[id, weight]
Records[date, animal_id, weight]
An animal has a lot of records on different dates and the hasMany association is working fine.
But in some cases, I just want to show the last record (according to date) for each animal. I’ve tried creating a hasOne record association with no success.
When Im using “$this->Animals->get($id, [‘contain’=> [‘Record’]]);” the animal->record its not the latest one but the first one.
When Im using “$this->Animals->find()->contain([‘Record’])->all();” all the animals->record are null except for the first animal.
$this->hasOne('Record', [
'className' => 'Records',
'foreignKey' => 'animal_id',
'strategy' => 'select',
])->setFinder('latestDateRecord');
// Add this custom finder in Model/Table/RecordsTable.php
public function findLatestDateRecord(Query $query, array $options)
{
$query->where([ 'add in your own conditions here'])
->order(['Records.date' => 'DESC'])
->limit(1);
// debug your query here for more info
debug( $query->sql() );
return $query;
}
@yousuo That won’t work, a custom finder doesn’t change how the records are looked up, the select strategy will still use a single query to retrieve all associated records (like SELECT ... FROM records WHERE parent_id IN (1,2,3,4, ....), and that’s where the limit will be applied, thus it will still result in only 1 overall associated record to be retrieved, not 1 associated record per parent.