Two tables without relationship filter - Laravel

Question

I’m having trouble filtering with two different models. The problem is that there is no relationship between the tables.

That’s right =/

The modeling was done by someone else, who said that it would not be possible to do the relationship and that I would have to do with what I have. Thus, in some modules I needed to make some queries and more queries to remedy this and make some data available to the user.

I have a view (oracle) of tools

And I have an order table, where I register the inventory_item_id and the part_number of the requested tool

I have a view with a filter:

<!-- Filtro -->
    <div class="card shadow mb-4">
        <div class="card-header py-3">
          <h6 class="m-0 font-weight-bold text-primary">Filtro</h6>
        </div>
        <div class="card-body">
          <form action="/historico" method="get">
            <div class="row">

                <div class="col-md-2 px-1">
                    <label for="estado">Estado</label>
                    <select class="form-control" name="estado">
                        <option value="" selected disabled>Selecione um status</option>
                        <option value="Aberto" >Aberto</option>
                        <option value="Submetido" >Submetido</option>
                        <option value="Entregue" >Entregue</option>
                        <option value="Cancelado" >Cancelado</option>
                    </select>
                </div>

                <div class="col-md-2 px-1">
                    <label for="data">Data</label>
                    <input type="date" class="form-control" value="" name="data" >
                </div>

                <div class="col-md-3 px-1">
                    <label for="item">Item</label>
                    <input type="text" class="form-control" value="" name="item" >
                </div>

                <div class="col-md-4 px-1">
                    <label for="descricao">Descrição da ferramenta</label>
                    <input type="text" class="form-control" value="" name="descricao" >

                </div>

                <div class="col-md-1  align-self-end px-1 button">
                  <button type="submit" class="btn btn-secondary"><i class="fa fa-search fa-1x"></i></button>
                </div>


            </div>
          </form>

        </div>
    </div>

And my controller:

$pedidos = DetalhePedido::when(Request::input('estado'),function($query){
        $query->where('estado_linha',Request::input('estado'));
    })
    ->when(Request::input('data'),function($query){
        $query->where('creation_date',  'like', '%' . strtoupper(Request::input('data')) . '%');
    })
    ->when(Request::input('item'),function($query){
        $query->where('item',  'like', '%' . strtoupper(Request::input('item')) . '%');
    })
    ->when(Request::input('descricao'),function($query){
        $query->where('description','like', '%' . strtoupper(Request::input('descricao')) . '%');
    })->paginate(10);


    $estado = Request::input('estado');
    $data = Request::input('data');
    $item = Request::input('item');
    $descricao = Request::input('descricao');


    return view('historico', compact('pedidos', 'estado', 'data', 'item', 'descricao'));

Filtering by State and for Date works normally. The problem is that they also want filter by Item and Description, which are oracle view columns and which have no relationship.

Is there any way to fix it?

Answer 1

I managed to solve it. It was a lot of work because I thought of several possibilities, but then I saw that it was simple.

Just query the tool table with the input data and store it in a variable, then the normal query with the inventory_item_id field of the first element.

Stayed like this:

// Busca por itens solicitados
    $pedidos = DetalhePedido::when(Request::input('estado'),function($query){
        $query->where('estado_linha',Request::input('estado'));
    })
    ->when(Request::input('data'),function($query){
        $query->where('creation_date',  'like', '%' . strtoupper(Request::input('data')) . '%');
    })
    ->when(Request::input('item'),function($query){
        $frmtItem= Ferramenta::where('item',  'like', '%' .  Request::input('item'). '%')->get();
        $query->where('inventory_item_id', '=',  $ferramenta[0]->inventory_item_id );
    })
    ->when(Request::input('descricao'),function($query){
        $frmtDescription = Ferramenta::where('description',  'like', '%' .  strtoupper(Request::input('descricao')). '%')->get();
        $query->where('inventory_item_id', '=',  $frmt[0]->inventory_item_id );
    })->paginate(10);

Answer 2

Here’s an example of code you might be using:

$pedidos = DetalhePedido::where('estado_linha', $request->estado)
           ->where('creation_date', 'like', '%'.strtoupper($request->data).'%')
           ->whereHas('ferramenta', function($query) use ($request){ 
               //WhereHas se usa para where relacionados caso não esteja relacionado pode usar o where como descrito antes do whereHas e puxar como você fez em baixo criando uma variável antes e passando os dados que deseja.
               $query->where('item', 'like', '%'.$request->item.'%');
            })
            ->whereHas('ferramenta', function($query) use ($request){
                $query->where('description', 'like', '%'.strtoupper($request->descricao).'%');
            })->paginate(10);

Another tip I give you is that when pulling only a specific data use the ->first() in place of ->get() so take the first record.

The only reinforcing Wherehas is for relationship I see what tool would need is related to detail to help you, but in case you are not using the previous form with ->first() and go to Where as shown in the example, shorter and cleaner code.