Home / Questions / 437220
Accepted
Miguel
Asked: 2022-03-22 09:58:29 +0800 CST 2022-03-22 09:58:29 +0800 CST

Relations between three tables

  • 772

Hello good afternoon and happy Sunday. As always I have problems between relationships with tables.

My users table

go yam Photo
1 name1 photo.jpg
1 name2 photo.jpg

My clients table

go center
1 center1
1 center2

my message table

go client_id message admin_id
1 10 message1 1
1 10 message2 1

As you can see I have a table with the clients, and a relation to get the messages that I leave in each client, there can be several in the same client. But as you can see in the message table I have a field called admin_idthat is where the administrator inserts the message. So I get the messages with relationships great, I get the messages that a client has without any problem, the problem is getting the photo of the administrator that is in the table users...

Client Model

class Cliente extends Model
{
    //Aquí me conecto a los mensajes del cliente//
    public function mensajes()
    {
        return $this->hasMany('App\Mensaje', 'cliente_id');
    }
    
}

Model Message

class Mensaje extends Model
{
    protected $table = 'mensajes';
    protected $primaryKey='id';

    //Aquí la relación con el cliente//
    public function cliente()
    {
        return $this->belongsTo('App\Cliente', 'id');
    }
    //Aquí intento que saque la foto//
    public function user()
    {
        return $this->hasOne('App\User', 'id');
    }
}

Model User

protected $table = 'users';
protected $primaryKey='id';


public function mensaje()
{
    return $this->belongsTo('App\Mensaje', 'admin_id');
}

That is, a client can have many messages, and the message corresponds to a client and an administrator.

Once I iterate the clients, I iterate the messages like this:

@foreach ($clie->mensajes as $item){

@endforeach

That returns me this:

#original: array:7 [▼
        "id" => 25
        "cliente_id" => 310
        "mensaje" => "mensaje"
        "admin_id" => 10
        "fecha" => "01/03/2021"
        "created_at" => "2021-03-01 11:32:17"
        "updated_at" => "2021-03-01 11:32:17"
      ]

How can I get the photo of the administrator and his name??? Thank you...

The query in the Controller to pull the clients:

$clientes = Cliente::join('tecnologias', 'clientes.interes', '=', 
'tecnologias.id')
->join('interes', 'clientes.interesCliente', '=', 'interes.id')
->select('clientes.*', 'tecnologias.name AS nombreEquipo', 'interes.name as nameInteres', 'interes.fondo', 'interes.color')
->whereIn('clientes.tipo_cliente', [0, 3])->get();

With that I show the clients in a table, then I iterate a modal for each one to see the messages.

Iteration test with @BetaM's answer

@foreach ($clie->mensajesDeUsuarios as $item)
   <div>{{ $item->name }}</div>
     @foreach ($item->pivot as $item2)
        <div class="stiloMensaje">{{ $item2->mensaje }}
          <div class="metadata">
           <span class="time"></span>
          </div>
        </div>
     @endforeach
@endforeach

Also in the message table there is a field fecha que es de donde saco la fecha de publicación del mensaje. From what I saw in the models you created you do this:

(Cliente::class, 'mensajes', 'admin_id', 'cliente_id')
(User::class, 'mensajes', 'cliente_id', 'admin_id')

I added the field there fechabut it seems that the relationship no longer exists.

**The error of not being able to iterate the messages, is because it is already iterating, mensajesDeUsuariosso it is enough to access {{ $item->pivot->mensaje }}and it already iterates the messages correctly. There was no need to iterate twice, well 3 if you count the one you iterate to the client.

laravel

1 Answers

  1. Best Answer

    I will try to comment a little beyond the exposed doubt and later try to offer a possible solution .

    1. The relationships defined look wrong since messages looks like an intermediate table by storing the primary keys of both of Userand of .Cliente
    2. Given the comment above, then:
      • You don't need a model to Mensajes( unless you have a requirement that justifies it and if so then you should stick to the Eloquent conventions )
      • If so, then the relationship you define is not the right one, instead you should use: belongsToManyon each of the entities that feed the intermediate table
      • I recommend reading the doc. on the use of the methodwithPivot

    Made the previous comments, then your entities could be like this:

    Client Model

    <?php 

class Cliente extends Model
{
    public function mensajesDeUsuarios()
    {
        return $this->belongsToMany(User::class, 'mensajes', 'cliente_id', 'admin_id')->withPivot('mensaje');
    }
 }

    Model User

    <?php 

class User extends Model
{
    public function mensajesDeClientes()
    {
        return $this->belongsToMany(Cliente::class, 'mensajes', 'admin_id', 'cliente_id')->withPivot('mensaje');
    }
}

    Now the query:

    $usuariosConMensajes = User::with('mensajesDeClientes')->get();

    Make the same one dd($usuariosConMensajes);so that you can see the information that is returning to you

    Edition

    Once the previous query returns values, you should iterate them like this ( based on my example )

    So your query:

    $clientesConMensajes = Cliente::with('mensajesDeUsuarios')->get();

    This is how you would iterate:

    @foreach($clientesConMensajes as $clienteConMensaje)
    {{ $clienteConMensaje->propiedadClienteUno }}
    {{ $clienteConMensaje->propiedadClienteN }}
    @foreach($clienteConMensaje->mensajesDeUsuarios as $mensajeDeUsuario)
    {{ $mensajeDeUsuario->pivot->propiedadMensajeUno }}
    {{ $mensajeDeUsuario->pivot->propiedadMensajeN }}
    @endforeach
@endforeach

    Remember that:

    • Both the property propiedadMensajeUnoand propiedadMensajeNare the extra columns of the pivot table that you want to show and that in order to access them you must declare them as arguments in the methodwithPivot
    • 2