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

1

As you can see in the controller , I am listing the information for each agreement, along with information that is in other tables (phones, address ), to list the specialty and type of service of each.

Unlike the phones and address table that has a direct connection to the agreement table, > do not have (but have the conv_serv table to make this connection).

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

At the end of the post has an image explaining better the relation of the tables.

MODEL CONVENTION

<?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 SERVICO

<?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');
    }
}

CONTROLLER AGREEMENT

<?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'));
    }
}

    
asked by anonymous 02.08.2017 / 21:52

2 answers

1

To illustrate, I'll create a minimal 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 through the relationships of your 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();

Result:

=> 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 up the relationship that is in the next entity model separates by point by passing the name of the method that makes the connection in the especialidades.servico case, which is explained in the documentation: Nested Eager Loading .

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

02.08.2017 / 22:45
0

If I understand correctly, you want to use the relationship of the final tables until you get to the table "Father" ... So, for the list of agreements has the reference of conv_servs and so on ... So the solution to to filter out the entities below to finally reach the "agreements" is through: Querying Relationship Existence .

As in the documentation example:

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

And keep going until you get to the level you want.

    
03.08.2017 / 10:29