Create a HasOne association for the most recent record

Is is possible to create a HasOne association to get the lastest record only?

I have the following association - a Member may have several warrants

$this->hasMany('MemberWarrants', [
    'foreignKey' => 'member_id'
]);

I want to be able to select a list of members, and also return the latest MemberWarrant for each Member.

I had tried limits on the query but it just returned 1 MemberWarrant for the whole selection.

I’ve also created a hasOne association

$this->hasOne('CurrentMemberWarrant', [
    'className' => 'MemberWarrants',
    'conditions' => function ($e, $query) {
        $query
            ->order(['CurrentMemberWarrant.date_issued' => 'DESC'])
        ->limit(1);
        return $e;
    }
]);

But it always just returns each Member Warrant and duplicate entries for the Member.

I’ve looked through the SQL Log and I’ve also tried to create what I want in SQL but can’t get it :frowning:

What I’d done previously in CakePHP 2.x was to create a binding key in the Member record and then in the afterSave for the associated record, update the member binding key with the last record.

This is an example we have for the Member Units - a Member might move to a different Unit, we have all their history and also a simple relationship to get the current Unit. It worked - although I was hoping with the ORM where might be a better way to handle this. Apparently not?

$this->hasMany('MemberUnits', [
    'foreignKey' => 'member_id'
]);

...

$this->hasOne('CurrentMemberUnits', [
    'className' => 'MemberUnits',
    'foreignKey' => 'id',
    'bindingKey' => 'current_member_unit_id'
]);

I was never here…

:slight_smile:
Thanks - we’re on MariaDB so appears there might be issue with partitionable but will check it out
I also found that Stack Overflow page - tried some of the workarounds but didn’t help alas.

Once again thanks - no need now to keep banging my head on the keyboard trying to figure it out!