Is it possible to get a full-fledged orm-structure of models through multiple JOIN?

Question

Laravel

Hello members!
There was a task to make sorting on the connected models . I dug around the Internet and came to the conclusion that you need to do a join.
Wrote this footcloth:

$document = Document::select('documents.*', 'c.*', 't.*')
            ->join('document_customs as c', 'documents.id', '=', 'c.document_id')
            ->join('document_templates as t', function($query){
                $query->on('t.document_type_id', '=', 'documents.type_id');
                $query->on('t.field_code', '=', 'c.field_code');
            })
            ->where('documents.id', '=', $document->id)
            ->orderBy('t.field_order', 'DESC')
            ->get();

        dd($document);

As a result, I got a collection of objects of type Document

Collection {#784 ▼
  #items: array:15 [▼
    0 => Document {#785 ▶}
    1 => Document {#786 ▶}
    2 => Document {#787 ▶}
    3 => Document {#788 ▶}
    4 => Document {#789 ▶}
    5 => Document {#790 ▶}
    6 => Document {#791 ▶}
    7 => Document {#792 ▶}
    8 => Document {#793 ▶}
    9 => Document {#794 ▶}
    10 => Document {#795 ▶}
    11 => Document {#796 ▶}
    12 => Document {#797 ▶}
    13 => Document {#798 ▶}
    14 => Document {#799 ▶}
  ]
}

where all the values ​​from the selection were stupidly stuffed into objects of the Document type:

#attributes: array:21 [▼
        "id" => 7
        "active" => 1
        "number" => "С140ХТ1501111"
        "name" => "Новый документ1111"
        "start_date" => "2019-11-08 00:00:00"
        "end_date" => "2019-11-30 00:00:00"
        "type_id" => 4
        "status_id" => 1
        "author_id" => 5
        "workflow_id" => null
        "workflow_point_id" => null
        "approval_user_id" => null
        "created_at" => null
        "updated_at" => null
        "deleted_at" => null
        "document_id" => 7
        "field_code" => "radio"
        "field_value" => "{"10": "Десять", "20": "Двадцать", "30": "Тридцать"}"
        "document_type_id" => 4
        "field_order" => 55
        "model" => null
      ]

In short, I am sad. I need to sort related models that depend on each other, but in the end it turns out that the ORM does not know how to do this.
How to solve the problem here?

Answer 1

Do not
Document::select('documents.*', 'c.*', 't.*')
but
Document::select('documents.*'')
and
->groupBy('documents.id')
of another There is no option as far as I know.

Answer 2

Loading Eloquent Relationships Using Left Join Together...

Answer 3

Here is sorting by related models

Document::with(['document_customs', 'document_templates'])
            ->withCount(['document_templates as for_sort' => function ($q) {
                $q->select('field_order');
            }])
            ->where('id', '=', $document->id)
            ->orderBy('for_sort', 'DESC')
            ->get();

Answer 4

You need to first get the list of ids of the required documents in the desired order with joins, and then request the models, and in this order:

$ids = \DB::table('documents')
   ->select(['documents.id'])
   ->join('templates', <тут связь>)
   ->orderBy('templates.field_order')
   ->pluck('id');

$list = $ids->implode(',');
$docs = Document::whereIn('id', $ids->toArray())
   ->orderByRaw("find_in_set(id, '$list')")
   ->get();