Search and bring id from another table

Question

The search field for the problem is working, but I would like to search for the customer name as they are related.

controller search method

public function busca(OrdemRequest $request)
{
    $problema = $request->problema;
    $ordens = Ordem::where('problema', 'LIKE', "%{$problema}%")->get();
    return view('ordem.listaordens',compact('ordens'));
}

Route:

Route::post('/busca','OrdemController@busca');

View:

<form action="/ordem/busca"method="post">   
    <div class="form-group">
        {{csrf_field()}}
        <input type="text" class="form-control" name="problema" placeholder="Buscar...">

Model order:

class Ordem extends Model
{

    protected $fillable = ['id','cliente_id','valor','data','problema','servico_id'];
    protected $table = 'ordens'; 
    public function produto()
    {
        return $this->belongsToMany(produto::class, 'produto_id');
    }
    public function servico()
    {
        return $this->belongsTo(servico::class,'ordem_id','servico_id');
    }
    public function cliente()
    {
        return $this->belongsTo(Cliente::class,'cliente_id');
    }   
}

Customer model:

class Cliente extends Model
{
    protected $fillable = ['id','nome','cpf','endereco','telefone','email'];
    protected $table = 'clientes';
    public function ordem()
    {
        return $this->hasMany(ordem::class,'cliente_id');

Answer 1

When you need to do a type of search that has a relationship, use the method Join of the Query Builder as follows:

public function busca(OrdemRequest $request)
{
    $problema = $request->problema;
    $ordens = Ordem::join('clientes', 'clientes.id','=','ordems.cliente_id')
        ->where('clientes.nome', 'LIKE', "%{$problema}%")
        ->get();
    return view('ordem.listaordens',compact('ordens'));
}

thus making the list of tables and having the possibility to use filters in fields related to relations.

Another way would also be with whereExists as follows:

public function busca(OrdemRequest $request)
{
    $problema = $request->problema;
    $ordens = Ordem::whereExists(function($query) use($problema)
       {
           $query->select(\DB::raw(1))
                 ->from('clientes')
                 ->whereRaw('clientes.id = ordems.cliente_id')
                 ->where('clientes.nome','LIKE',"%{$problema}%")
       })  
        ->get();
    return view('ordem.listaordens',compact('ordens'));
}

_References:

• Database: Query Builder - Joins
• Database: Query Builder - whereExists