Consult with related tables?

0

I am trying to generate a query with related tables, I have a call deposits, which keeps the id of other 4 tables, users, states, banks and types, but when I do the query, I only manage to bring the ids of the others tables, and not the names, I already have the relationships in the models.

Now I explain the problem better, if I make a general query, regardless of the user who made the deposit, if I can bring the data I need, the problem is when I only want to bring the deposits of a specific user, because there only I bring the ids of the other tables, and not the names that is what interests me.

At the moment I have my query as follows:

 public function index()
{

  $depositos = DB::table('depositos')
  ->select('depositos.*','depositos.id as id_deposito', 'depositos.monto','depositos.fechaboleta','depositos.detalles','depositos.observaciones','depositos.created_at','depositos.updated_at','depositos.noboleta')
  ->where( 'id_usuario','=', Auth::id())
  ->join('bancos','bancos.id','=','depositos.id_banco')
  ->join('tipos','tipos.id','=','depositos.id_tipo')
  ->join('estados','estados.id','=','depositos.id_estado')
  ->paginate(5);
  return view('depositos.index', compact('depositos'));

}

If someone can help me, I would really appreciate it.

    
asked by JoséOrdoñez 01.12.2017 в 17:04
source

2 answers

1

When doing the select you are delimiting the query to the specified columns. It will also show the columns you are looking for (where). To get the data from the other tables you have to specify them:

public function index()
{
  $depositos = DB::table('depositos')
       ->select('depositos.*', 'bancos.*', 'tipos.*', 'estados.*')
       ->where( 'id_usuario','=', Auth::id())
       ->join('bancos','bancos.id','=','depositos.id_banco')
       ->join('tipos','tipos.id','=','depositos.id_tipo')
       ->join('estados','estados.id','=','depositos.id_estado')
       ->paginate(5);

  return view('depositos.index', compact('depositos'));

}

Edited to extend

But if you do this type of query to the database, you will not have a Collection of objects, but of arrays.

Returns the error: Undefined property: stdClass::$banco because it cuts with the first error, but if it continues it would also return: Undefined property: stdClass::$tipo and Undefined property: stdClass::$estado .

To be able to work as you are doing in the view you have to have defined the relationships in the model and, then, make the query with the model:

Deposit Model

public function banco(){
     return $this->belongsTo('App\Models\Bancos', 'id_banco');
}

public function tipo(){
     return $this->belongsTo('App\Models\Tipos', 'id_tipo');
}

public function estado(){
     return $this->belongsTo('App\Models\Estados', 'id_estado');
}

Driver:

public function index()
    {
        $depositos = Depositos::where('id_usuario', '=', Auth::id())
            ->paginate(5);

        return view('depositos.index', compact('depositos'));
    }

And in the view: Here you can use $deposito->banco->nombre , $deposito->tipo->tipo and $deposito->estado->estado because $deposito is an object with its relationships, also objects with properties and methods.

@foreach($depositos as $deposito)
<tr>
    <td>{{ $deposito->id }}</td>
    <td>{{ $deposito->noboleta }}</td>
    <td>{{ $deposito->monto }}</td>
    <td>{{ $deposito->banco->nombre }}</td>
    <td>{{ $deposito->tipo->tipo }}</td>
    <td>{{ $deposito->fechaboleta }}</td>
    <td>{{ $deposito->detalles }}</td>
    <td>{{ $deposito->observaciones }}</td>
    <td>{{ $deposito->created_at }}</td>
    <td>{{ $deposito->estado->estado }}</td>
    <td>{{ $deposito->updated_at }}</td>
</tr>
@endforeach
    
answered by 02.12.2017 / 09:44
source
0

so I try to show the data, but I get the following error

<td>{{ $deposito->id}}</td>
    <td>{{ $deposito->noboleta}}</td>
    <td>{{ $deposito->monto}}</td>
    <td>{{ $deposito->banco->nombre}}</td>
    <td>{{ $deposito->tipo->tipo}}</td>
    <td>{{ $deposito->fechaboleta}}</td>
    <td>{{ $deposito->detalles}}</td>
    <td>{{ $deposito->observaciones}}</td>
    <td>{{ $deposito->created_at}}</td>
    <td>{{ $deposito->estado->estado}}</td>
    <td>{{ $deposito->updated_at}}</td>
    <td>

Undefined property: stdClass :: $ bank

    
answered by 04.12.2017 в 23:31