Two tables without relationship filter - Laravel

Asked

Viewed 228 times

-1

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 inserir a descrição da imagem aqui

And I have an order table, where I register the inventory_item_id and the part_number of the requested tool inserir a descrição da imagem aqui

I have a view with a filter: inserir a descrição da imagem aqui

<!-- 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?

  • I see that your code can get smaller, and why did the guy say you can’t relate to him being dumb or what? Just can’t relate if the bank isn’t relational if it’s my friend, can send the guy back to algorithms because he’s not fit for the market

  • It is. It gave me a lot of work, but I managed to solve.

  • Which version of Laravel?

  • Oops, Lucas. It’s 5.8

  • Improve this code to spend 4 lines that your code in this version

  • What is your suggestion?

  • I will redo your code in today’s mode and you evaluates can be?

  • Yes yes, of course!!

Show 3 more comments

2 answers

0


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);

0

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.

  • 1

    Ah, great. I’ll test it. As for ->get(), it was intentional. When I pasted the code, I hadn’t finished yet. I had to do another validation, since there can be several tools with the same input stretch. This way I needed the get to load all. Anyway. Thanks for the touch. I will test your suggestion here.

  • 1

    Yes, as already mentioned there is no relationship. That’s why I had to fail before setting the query.

  • You can pass the query before and it would still be smaller than your previous code

Browser other questions tagged

You are not signed in. Login or sign up in order to post.