How to join two tables with a pivot table using Laravel?

I have these three tables:

tbl_lista_contactabilidad  tbl_equipo_postventaatc  users
-------------------------  -----------------------  -----
id                         id                       id
usuarios_id                asesor_id                name

tbl_lista_contactabilidad.usuarios_id should be related with tbl_equipo_postventaatc.asesor_id. asesor_id should be the “pivot” between tbl_lista_contactabilidad.usuarios_id and users.id to make the relation.

I want to make this relation so I tried to do this relation in this way (I will put only the relation of the model)

Tbl_Lista_Contactabilidad (Model 1)

public function postventaatc(){

return $this->belongsTo('AppModelsTbl_EquipoPostventaatc','usuarios_id');

}

Tbl_Equipo_Postventaatc (Model 2) -> This should be the pivot model

public function contactabilidad(){

return $this->hasMany('AppModelsTbl_Lista_Contactabilidad','usuarios_id');

}

public function user(){

return $this->belongsTo('AppModelsUser','asesor_id');

}

User (Model 3)

public function postventaatc(){

return $this->hasMany('AppModelsTbl_Lista_Postventaatc','asesor_id');

}

EXAMPLE:

As you see in the image… if I relate usuarios_id with users directly I will get another name and I don’t want that… I want the relation just like in the image

Answer

A pivot table is a structure used to join two separate models together with a single relationship. This is called a many-to-many relationship in Eloquent.

From what you’ve described, this is not the case here. Rather, it looks like a has-many-through relationship.

If I’m understanding correctly, your relationships should look like this:

<?php

namespace AppModels;

use IlluminateDatabaseEloquentModel;

class Tbl_Lista_Contactabilidad extends Model {
    protected $table = 'tbl_lista_contactabilidad';
    public function postventaatc() {
        return $this->belongsTo(Tbl_EquipoPostventaatc::class, 'usuarios_id');
    }
}

class Tbl_EquipoPostventaatc extends Model {
    protected $table = 'tbl_equipo_postventaatc';
    public function contactabilidad() {
        return $this->hasMany(Tbl_Lista_Contactabilidad::class, 'usuarios_id');
    }
}

class User extends Model {
    public function postventaatc() {
        return $this->belongsTo(Tbl_EquipoPostventaatc::class, 'asesor_id');
    }
    public function contactabilidad() {
        return $this->hasManyThrough(Tbl_Lista_Contactabilidad::class, Tbl_EquipoPostventaatc::class, 'asesor_id', 'usuarios_id');
    }
}

Obviously this is easier for a native English speaker, but I cannot stress how much easier this would be if you were following the Laravel rules around naming your models, tables, and columns. Why does usuarios_id column relate to a table called tbl_equipo_postventaatc? Why use asesor_id instead of user_id? ????????‍♂️ Those names have nothing to do with each other, and make it hard to figure out what is going on.