Display results of Join in view with Laravel

Question

I’m learning laravel and I have a relatively simple question regarding a consultation using join in Laravel.
How do I access the field value categoria.name in my view.
Follow the consultation and the view respectively.

Function of the Controller

public function list()
{
    $products = produto::join('categoria','category_id','=','id')->select('produtos.*','categoria.name');
    return view('Product/list',compact('products'));
}

View

@extends('layout.app')

@section('body')
@if (count($products) > 0)
<table class="table">
    <thead class="thead-dark">
    <tr>
        <th scope="col">ID</th>
        <th scope="col">NAME</th>
        <th scope="col">STOQUE</th>
        <th scope="col">PRICE</th>
        <th scope="col"></th>
    </tr>
    </thead>
    <tbody>
        @foreach ($products as $p)
        <tr>        
        <td>{{$p->id}}</td>
        <td>{{$p->name}}</td>
        <td>{{$p->stoque}}</td>
        <td>{{$p->price}}</td>
        <td>
                <a href="/product/{{$p->id}}/edit"  class="btn btn-dark">EDIT</a>
                <a href="/product/delete/{{$p->id}}"  class="btn btn-danger">DELETE</a>
        </td>
        </tr>
        @endforeach 
    </tbody>
</table>
@else
<div class="alert alert-danger alert-dismissible fade show" role="alert">
        Não existem produtos cadastrados
        <button type="button" class="close" data-dismiss="alert" aria-label="Close">
          <span aria-hidden="true">&times;</span>
        </button>
      </div>
@endif
@endsection

Answer 1

Imagining the scenario that a category has several products, and several products may belong to the same category, we have a relationship n:m.

I recommend the following:

Filing cabinet Product.php:

<?php
namespace App\Models;

use App\Models\Categoria;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsToMany;

class Produto extends Model {
    // o código existente da sua classe aqui

    /**
     * Recupera as categorias do produto.
     * @see https://laravel.com/docs/5.7/eloquent-relationships
     */
    public function categorias(): BelongsToMany {
        return $this->belongsToMany(Categoria::class);
    }
}

Filing cabinet Category.php:

<?php
namespace App\Models;

use App\Models\Produto;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsToMany;

class Categoria extends Model {
    // o código existente da sua classe aqui

    /**
     * Recupera os produtos da categoria.
     * @see https://laravel.com/docs/5.7/eloquent-relationships
     */
    public function categorias(): BelongsToMany {
        return $this->belongsToMany(Produto::class);
    }
}

Having your models defined, on the controller you could do so:

Filing cabinet Productocontroller.php:

<?php
namespace App\Http\Controllers;

use App\Http\Controllers\Controller;
use Illuminate\View\View;
use App\Models\Produto;

class ProdutoController extends Controller {
    // seu código existente aqui

    public function listar(): View {
        // https://laravel.com/docs/5.7/eloquent-relationships#eager-loading
        $produtos = Produto::all()->load('categoria');
        return view('Product/list', ['products' => $produtos]);
    }
}

There in your view you display the data in the way you think best. In case you want to display the name of the categories:

@foreach ($products as $p)
    <tr>        
        <td>{{$p->id}}</td>
        <td>{{$p->name}}</td>
        <td>{{$p->stoque}}</td>
        <td>{{$p->price}}</td>
        <td>
        @forelse ($p->categorias as $c)
        {{ $c->name }} <br>
        @empty
        Nenhuma categoria relacionada a este produto.
        @endforelse
        </td>
    </tr>
@endforeach

The reading I did is that a category can have n related products and vice versa, so you can display in the product listing view you can display all categories that the same product is related to. In case the application business rules you are developing define that a product may be related to only one category, it will be necessary to change the relationship methods from Eloquent, from belongsToMany to hasMany() (from Product to Category - models) and belongsTo() (from Product Category - models). Read more about.

Try to adapt and see if you get any results. Importantly, note that this is an elegant approach, very direct to the point and cast. When you are more familiar with the framework, use your conventions, helpers, paging, dependency injection, project patterns too, recommend the repository pattern for you to better structure your classes and avoid code couplings that will give you headaches in future maintenance!

PS: I made the codes from the head (adaptation of copy&paste) and did not test I am without appropriate environment at the moment. If you make a mistake, put it there so we can try to find the solution!

Answer 2

If the product table and category table have a field called name, you must create a alias or nickname for one of the fields, so it would look like this:

$products = produto::join('categoria','category_id','=','id')->select('produtos.*','categoria.name as nome_categoria');

In this example I gave a nickname for the field name of the second table, calling the nome_categoria.
Ai na view you can call it:

<td>{{$p->nome_categoria}}</td>

I suggest you read about the Eloquent and their relationships. Because it helps a lot in these Join processes and makes the code less complex. Follow the doc’s link on Eloquent:
Eloquent, Relationships in the Eloquent.