Php 筛选器具有多个查询之一

我正在try 使用拉威尔的hasOne(Model::class)->ofMany('relationship')，但我也想额外过滤考虑过的结果.

更详细地说，我的问题:

我有这两个简化的表格，我在所见即所得风格的创作工具中使用:

文件:id (PK),...份

文档修订表: id (PK),document_id (FK),revision_id,is_autosave,...个

在修订表中，组合document_id, revision_id, is_autosave是唯一的，这意味着对于自动保存的修订和手动保存的修订，文档可以具有相同的Revision_id.

我正在try 使用以下代码将最新手动保存的修订与文档模型相关联:

public function mainRevision() {
   return $this->hasOne(DocumentRevision::class)
      ->where('autosave', false)
      ->ofMany('revision_id', 'max');
}

我可以拨打上面的电话，比如Documents::with('mainRevision')->where(...)->get(). 其目的是为所有匹配的文档行紧急加载最新的手动修订，并且仅加载最新的手动修订.

但是，这会生成以下查询:

select
    `document_revisions`.*
from
    `document_revisions` inner join (
        select
            max(`document_revisions`.`id`) as `id_aggregate`,
            min(`document_revisions`.`revision_id`) as `revision_id_aggregate`,
            `document_revisions`.`document_id`
        from
            `document_revisions`
                inner join (
                    select max(`document_revisions`.`revision_id`) as `revision_id_aggregate`,`document_revisions`.`document_id`
                    from `document_revisions` 
                    where `document_revisions`.`document_id` in (`<list of matched ids from the 1st query>`)
                group by
                    `document_revisions`.`document_id`
            ) as `mainVersion`
            on `mainVersion`.`revision_id_aggregate` = `document_revisions`.`revision_id` and `mainVersion`.`document_id` = `document_revisions`.`document_id`
        group by `document_revisions`.`document_id`) as `mainVersion` 
    on `mainVersion`.`id_aggregate` = `document_revisions`.`id`
        and `mainVersion`.`revision_id_aggregate` = `document_revisions`.`revision_id`
        and `mainVersion`.`document_id` = `document_revisions`.`document_id`
where
    `autosave` = 0

虽然我在这里看到了意图，但有一个问题，那就是where autosave=0是在连接表之外添加的.这会导致这样一种情况，即如果最新版本id恰好仅对应于自动保存，则不会拾取它.

理想情况下，我希望查询如下:

select
    `document_revisions`.*
from
    `document_revisions` inner join (
        select
            max(`document_revisions`.`id`) as `id_aggregate`,
            min(`document_revisions`.`revision_id`) as `revision_id_aggregate`,
            `document_revisions`.`document_id`
        from
            `document_revisions`
                inner join (
                    select max(`document_revisions`.`revision_id`) as `revision_id_aggregate`,`document_revisions`.`document_id`
                    from `document_revisions` 
                    where `autosave` = 0 --<<----------- change here
                     and `document_revisions`.`document_id` in (`<list of matched ids from the 1st query>`) 
                group by
                    `document_revisions`.`document_id`
            ) as `mainVersion`
            on `mainVersion`.`revision_id_aggregate` = `document_revisions`.`revision_id` and `mainVersion`.`document_id` = `document_revisions`.`document_id`
        group by `document_revisions`.`document_id`) as `mainVersion` 
    on `mainVersion`.`id_aggregate` = `document_revisions`.`id`
        and `mainVersion`.`revision_id_aggregate` = `document_revisions`.`revision_id`
        and `mainVersion`.`document_id` = `document_revisions`.`document_id`

有人知道这是否可能吗？