Question 302994 in Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel – Filter hasOne ofMany query

I am trying to use Laravel’s hasOne(Model::class)->ofMany('relationship') but I would also like to additionally filter the considered results.

My problem in a bit more detail:

I have these two simplified tables that I use in a WYSIWYG style authoring tool:

documents: id (PK),...

document revisions table:
id (PK),document_id (FK),revision_id,is_autosave,...

In the revisions table the combination document_id, revision_id, is_autosave is unique meaning that a document can have the same revision_id for an autosaved and a manually saved revision.

I am trying to relate the latest manually saved revision with the document model using the code below:

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

I can call the above e.g. like Documents::with('mainRevision')->where(...)->get().
The intention is to eager-load the latest manual revision and only the latest manual revision for all matched document rows.

However this generates the following query:

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

While I can see the intention here, there is a problem in that the where autosave=0 is added outside the joined table. This results in the situation where if the latest revision_id happens to only correspond to an autosave, then it is not picked up.

I would ideally like the query to be like:

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`

Does anyone know if this is possible?

Tags:

2

Answers


  1. 0

    Try the following with the autosave = 0 condition applied within the subquery that determines the latest revision. Here is the revised code

    public function mainRevision() {
    return $this->hasOne(DocumentRevision::class)
        ->selectRaw('document_revisions.*, max(document_revisions.revision_id) as max_revision_id')
        ->where('autosave', false)
        ->groupBy('document_revisions.document_id')
        ->orderBy('max_revision_id', 'desc');
}
    Login or Signup to reply.
  2. 0

    According to the documentation, you can add conditions to the ofMany() query by passing a closure as the second parameter. This also requires the aggregate columns to be passed in the first parameter using the array notation. Example below:

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

    Unrelated, but Laravel also has another way of defining "one of many" you may prefer, by converting the existing hasMany() relationship to a hasOne(). There is no difference between this and what you have, just a preference of style. Assuming you have hasMany() relationship defined as documentRevisions(), you can call the one() method on it to convert it to a hasOne() relationship:

    public function mainRevision() {
    return $this->documentRevisions()
        ->one()
        ->ofMany([
            'revision_id' => 'max'
        ], function (Builder $query) {
            return $query->where('autosave', false);
        });
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search