Sorting the results of a query - Laravel 5.1

Question

I currently have a code that receives data from registered companies and for each company it lists their reports as follows:

@foreach($empresas as $empresa) // Recebe as empresas
   @foreach($empresa->CgrtInfo as $relatorio) // Busca Relatórios da empresa
      <tr>
         <td>{{ $relatorio->id }}</td>
         <td>{{ $relatorio->Empresa->razao_social }}</td>
      </tr>
   @endforeach
@endforeach

I’m doing the Controller search as follows:

$empresas = $cliente->Empresa()->where('razao_social', 'ILIKE', '%'.$busca.'%')->paginate(10);

Cgrtinfo Table (Reporting Information):

$table->increments('id');
$table->integer('id_empresa')->unsigned();//Foreign key
$table->integer('id_cliente')->unsigned();//Foreign key

Table Company:

$table->increments('id');
$table->integer('id_cliente')->unsigned();//Foreign key
$table->string('razao_social', 128);

Model Cgrtinfo:

public function Empresa(){ 
   return $this->belongsTo('SIST\Models\Client\Empresa', 'id_empresa'); 
}

Model Empresa:

public function CgrtInfo(){ 
   return $this->hasMany('SIST\Models\Client\Cgrt\CgrtInfo', 'id_empresa');
}

Only this way the id of the reports ends up being out of order, because if the report nº 10 was from company 1, and the report 5 was from company 2, ends up staying the 10 in front of the 5.

"Ah, and why don’t you search directly for the reports?"

I honestly could not, because when the listing is based on a search, I have to search for the company’s social reason, and I do not have and did not think it necessary to put this information in the model of reports, but if it is not good...

Answer 1

You can bring all the reports in one SELECT:

<?php 
    use DB;

    ...

    $relatorios = DB::table('CgrtInfo')
                      ->join('empresa', 'empresa.id', '=', 'CgrtInfo.id_empresa')
                      ->orderBy('empresa.id')
                      ->where('razao_social', 'ILIKE', '%'.$busca.'%')
                      ->paginate(10);

    ?>