hasMany association but using limit(1) ('hasOne')

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.

class AnimalsTable extends Table {

    $this->hasMany('Records', [
        'bindingKey' => 'id',
        'foreignKey' => 'animal_id',
    ]);

    $this->hasOne('Record', [
        'className' => 'Records',
        'foreignKey' => 'animal_id',
        'strategy' => 'select',
        'sort' => ['Records.date' => 'DESC'],
        'conditions' => function ($e, $query) {
            $query->limit(1);
            return [];
        }
    ]);
}

PD: There’s a very good explanation from mdn in cakephp - How to limit contained associations per record/group? - Stack Overflow but it is from 6 years ago.

But I’ve updated it relatively recently with a new plugin solution, and the rest is still the status quo I’m afraid :wink:

Try using a finder instead (hasOne association)

$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.

1 Like