I've set a relation Has One Of Many in 'advanced way', it's almost identical to the example on Laravel site (eloquent-relationships#advanced-has-one-of-many-relationships).
class DocumentType extends Model
{
use HasFactory, SoftDeletes;
// Some other code here...
public function currentRevision () {
return $this->hasOne(DocumentTypeRevision::class, 'document_type_id')
->ofMany (['revision_number' => 'max', 'revision_date' => 'max'],
function ($qry) {
$qry->where('revision_date', '<=', now()->startOfDay());
});
}
}
When i use:
$revision = DocumentType::find(1)->currentRevision;
i get the error.
The problem is that the generated SQL contains some fields in SELECT section that are not present in GROUP BY section, so the error SQLSTATE[42000]. I've found a temporary solution which is to disable 'ONLY_FULL_GROUP_BY' option in the database connection (database.php) but I would like to avoid the root problem.
Here the generated SQL for currentRevision:
select `document_types_revisions`.*
from `document_types_revisions`
inner join (
select MAX(`document_types_revisions`.`id`) as `id_aggregate`,
`document_types_revisions`.`revision_date` as `revision_date_aggregate`,
`document_types_revisions`.`revision_number` as `revision_number_aggregate`,
`document_types_revisions`.`document_type_id`
from `document_types_revisions`
inner join (
select max(`document_types_revisions`.`revision_date`) as `revision_date_aggregate`,
`document_types_revisions`.`revision_number` as `revision_number_aggregate`,
`document_types_revisions`.`document_type_id`
from `document_types_revisions`
inner join (
select max(`document_types_revisions`.`revision_number`) as `revision_number_aggregate`,
`document_types_revisions`.`document_type_id`
from `document_types_revisions`
where `revision_date` <= '2023-03-08 00:00:00'
and `document_types_revisions`.`document_type_id` = 3
and `document_types_revisions`.`document_type_id` is not null
and `document_types_revisions`.`deleted_at` is null
group by `document_types_revisions`.`document_type_id`
) as `currentRevision` on `currentRevision`.`revision_number_aggregate` = `document_types_revisions`.`revision_number`
and `currentRevision`.`document_type_id` = `document_types_revisions`.`document_type_id`
where `revision_date` <= '2023-03-08 00:00:00'
and `document_types_revisions`.`deleted_at` is null
group by `document_types_revisions`.`document_type_id`
) as `currentRevision` on `currentRevision`.`revision_date_aggregate` = `document_types_revisions`.`revision_date`
and `currentRevision`.`revision_number_aggregate` = `document_types_revisions`.`revision_number`
and `currentRevision`.`document_type_id` = `document_types_revisions`.`document_type_id`
where `revision_date` <= '2023-03-08 00:00:00'
and `document_types_revisions`.`deleted_at` is null
group by `document_types_revisions`.`document_type_id`
) as `currentRevision` on `currentRevision`.`id_aggregate` = `document_types_revisions`.`id`
and `currentRevision`.`revision_date_aggregate` = `document_types_revisions`.`revision_date`
and `currentRevision`.`revision_number_aggregate` = `document_types_revisions`.`revision_number`
and `currentRevision`.`document_type_id` = `document_types_revisions`.`document_type_id`
where `document_types_revisions`.`document_type_id` = 3
and `document_types_revisions`.`document_type_id` is not null
and `document_types_revisions`.`deleted_at` is null
limit 1;
