Laravel 5.4: List information from 4 tables related to the WITH clause

Question

As you can see in convenio controller, I am listing the information of each agreement along with the information that are in other tables(phones, addressee), but I would also like to list the specialty and the type of service each.

Different from the table telephones and address that has a direct link to the table covenant, the specialty and service do not (but have the table conv_serv to make that call).

I would like to know how I could bring the information of these two tables, through the clause with.

At the end of the post there is an image explaining better the relationship of the tables.

MODEL CONVENIO

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Convenio extends Model
{
    protected $primaryKey = 'id';
    protected $table = 'convenios';
    protected $fillable = ['nome', 'descricao', 'id_cidade'];
    protected $dates = ['created_at', 'updated_at'];
    public $timestamps = true;

    public function telefones()
    {
        return $this->hasMany(Telefone::class, 'id_convenio', 'id');
    }

    public function endereco()
    {
        return $this->hasOne(Endereco::class, 'id_convenio', 'id');
    }

    public function convServ()
    {
        return $this->hasOne(ConvServ::class, 'id_convenio','id');
    }

    public function cidade()
    {
        return $this->belongsTo(Cidade::class, 'id_cidade', 'id');
    }
}

MODEL CONVSERV

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class ConvServ extends Model
{
    protected $primaryKey = 'id';
    protected $table = 'conv_servs';
    protected $fillable = ['id_convenio', 'id_especialidade'];
    public $timestamps = false;

    public function convenio()
    {
        return $this->belongsTo(Convenio::class, 'id_convenio', 'id');
    }

    public function especialidade()
    {
        return $this->belongsTo(Especialidade::class, 'id_especialidade', 'id');
    }
}

SPECIALTY MODEL

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Especialidade extends Model
{
    protected $primaryKey = 'id';
    protected $table = 'especialidades';
    protected $fillable = ['nomeEsp', 'id_servico'];
    protected $dates = ['created_at', 'updated_at'];
    public $timestamps = true;

    public function convServ()
    {
        return $this->hasOne(ConvServ::class, 'id_especialidade','id');
    }
}

MODEL SERVICE

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Servico extends Model
{
    protected $primaryKey = 'id';
    protected $table = 'servicos';
    protected $fillable = ['nomeServ'];
    protected $dates = ['created_at', 'updated_at'];
    public $timestamps = true;

    public function especialidade()
    {
        return $this->hasOne(Especialidade::class, 'id_servico', 'id');
    }
}

CONVENIO CONTROLLER

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Models\Convenio;
use App\Models\Cidade;
use App\Models\Servico;
use App\Models\Especialidade;
use App\Models\Endereco;
use App\Models\ConvServ;
use App\Models\Telefone;
use DB;

class ConvenioController extends Controller
{
    private $convenio;

    public function __construct(Convenio $convenio)
    {
        $this->convenio = $convenio;
    }

    public function index(Request $request)
    {
        if($request->segment(1) == 'busca')
        {
            $title = 'Busca';
            $view = 'site.busca';
        }

        else
        {
            $title = 'Convênios';
            $view = 'painel.convenio.index';
        }

        $cidades = Cidade::pluck('nomeCidade', 'id')->all();

        $servicos = Servico::pluck('nomeServ', 'id')->all();

        $convenios = Convenio::with('telefones', 'endereco', 'cidade')
                            ->cidade($request->get('cidade'))
                            ->servico($request->get('servico'))
                            ->buscar($request->get('buscar'))
                            ->especialidade($request->get('especialidade'))
                            ->orderby('nome', 'asc')
                            ->paginate(10);

        return view($view, compact('title', 'convenios', 'cidades', 'servicos'));
    }
}

Answer 1

To illustrate, I will create a minimum example only with the relationship that needs to navigate between entities, note:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Convenios extends Model
{
    protected $primaryKey = 'id';
    protected $table = 'convenios';
    protected $fillable = ['name'];
    public $timestamps = false;

    public function especialidades()
    {
        return $this->belongsToMany(Especialidades::class,
            'convenios_especialidades',
            'convenio_id',
            'especialidade_id');
    }
}

---

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Especialidades extends Model
{
    protected $primaryKey = 'id';
    protected $table = 'especialidades';
    protected $fillable = ['name', 'servico_id'];
    public $timestamps = false;

    public function servico()
    {
        return $this->belongsTo(Servicos::class, 'servico_id', 'id');
    }

    public function convenios()
    {
        return $this->belongsToMany(Convenios::class,
            'convenios_especialidades',
            'especialidade_id',
            'convenio_id');
    }
}

---

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Servicos extends Model
{
    protected $primaryKey = 'id';
    protected $table = 'servicos';
    protected $fillable = ['name'];
    public $timestamps = false;
}

These relationships would be upon the relationships of their tables in the image offered in your question, if you want to bring the relationships that are made from Convenios do:

App\Models\Convenios::with('especialidades.servico')->get();

Upshot:

=> Illuminate\Database\Eloquent\Collection {#725
     all: [
       App\Models\Convenios {#743
         id: 1,
         name: "Con 1",
         especialidades: Illuminate\Database\Eloquent\Collection {#757
           all: [
             App\Models\Especialidades {#745
               id: 1,
               name: "Esp 1",
               servico_id: 1,
               pivot: Illuminate\Database\Eloquent\Relations\Pivot {#753
                 convenio_id: 1,
                 especialidade_id: 1,
               },
               servico: App\Models\Servicos {#759
                 id: 1,
                 name: "Serv 1",
               },
             },
           ],
         },
       },
     ],
   }

Explanation: to bring the relationship that is in the next entity model separate by dot passing the name of the linking method in the case especialidades.servico, which is explained in the documentation: Nested Eager Loading.

Remember that your model is different from this, but, I did according to the list of tables offered in the diagram.

_Reference:

• Nested Eager Loading

Answer 2

If I understand correctly, you want to use the relationship of the final tables until you reach the table "Father"... Therefore, the list of covenants has the reference of conv_servs and so on... Then the solution to go filtering in the entities below to last arrive at the "convenios" is through: Querying Relationship Existence.

As in the example of documentation:

$posts = Post::with(['votes', 'comments' => function ($query) { $query->where('content', 'like', 'foo%'); }])->get();

And it goes on like this until you reach the level you intend.