CODEWITHSUNDEEP
mysqllaravel
Lincoln Binda
Lincoln Binda

Reputation: 199

Using between correctly

Well, anyone could help me with that? I have the following form :

<div class="form-group col-md-12 col-sm-12 col-xs-12">        
<div class="col-md-2 col-sm-2 col-xs-2 form-label">
    {{Form::label('nrEmpenho', 'Nº Empenho')}}
</div>         
<div class="col-md-6 col-sm-10 col-xs-10">       
    <div class="input-group">
        <span class="input-group-addon" id="interval">Inicial</span>
            {{Form::text('nrEmpenho', null, ['class' => 'form-control', 'min' => 1])}}
            <span class="input-group-addon" id="interval">Final</span>
            {{Form::text('nrEmpenhooo', null, ['class' => 'form-control', 'min' => 1])}}
    </div>
</div>

So I have this query doing the search of data in the database :

$query = DB::table('empenho as emp')                
        ->join('despesa', 'emp.fichaOrcId', '=', 'despesa.id')
        ->join('pessoa', 'emp.fornecedorId', '=', 'pessoa.id')
        ->join('fonte', 'emp.fonteId', '=', 'fonte.id')
        ->select('emp.nrEmpenho as a', 'emp.date as b', 'emp.tipo as c', 'pessoa.nome as d', 'emp.valor as e',
                'emp.id as f', 'despesa.ficha as g', 'emp.ano as h', 'fonte.id as i')
        ->orderby('emp.nrEmpenho');

Then do an if to catch the data in the database :

if ($request->nrEmpenho) $query->where('emp.id', $request->nrEmpenho);

Thus works perfectly , only taking the field passed as the identifier "nrEmpenho" but I would like to make a range selection , taking the value between "nrEmpenho" and "nrEmpenhooo" as I do that with between? It would be something like this?/

if ($request->nrEmpenho and $request->nrEmpenhooo) $query->where('emp.id', $request->between, $request->nrEmpenho and $request->nrEmpenhooo);

PS: When I create 2 text fields , only one filter , even through different identifiers . can anybody help me? Any questions about the code I put everything it takes to become clearer . PSs: My english is very bad, i'm using translater, forgive me, i need much this help.

Upvotes: 1

Views: 37

Answers (1)

num8er
num8er

Reputation: 19372

use whereBetween() function:

$query->whereBetween('emp.id', [$request->nrEmpenho, $request->nrEmpenhooo]);

but if there will be performance problem with between then try to use this example:

$query
->where('emp.id', '>=', $request->nrEmpenho)
->where('emp.id', '<=', $request->nrEmpenhooo);

or maybe a set of ids?

so:

$query->whereIn('emp.id', [$request->nrEmpenho, $request->nrEmpenhooo]);

Upvotes: 2

Related Questions