I have table fireman joined one to many with table certificate. Latter table has field validity. I’d like to create additional relation between fireman and single certificate - the one having top date. I tried to do:
public function getLastCertificate()
{
return $this->hasOne(Certificate::className(), ['id_fireman' => 'id_fireman'])->orderBy('validity DESC');
}
But it doesn’t work in CertificateSearch module with sorting, because of that orderBy statement.
So i guess i need to do a subquery. But how? Using $this->id_fireman returns null.
I tried to add ->where(‘validity = (SELECT max(validity) FROM certificate WHERE certificate.id_fireman=fireman.id_fireman)’)
but it doesn’t work even though it’s correct subquery (i copied it from executed queries to phpmyadmin for test).
It tries to execute some other query that ends with error
SELECT * FROM `certificate` WHERE (validity = (SELECT max(validity) FROM certificate WHERE certificate.id_fireman=fireman.id_fireman)) AND (`id_fireman` IN ('3', '4', '5'))