how to get record from database within multiple conditions of between max and min value

GACHTOU NourEddine 0
Viewed 49

I have a table that contains products, and I want to automate pricing. I've tried to create a table called price. The price table is structured like so :

  • max_height
  • max_width
  • max_long
  • max_weight
  • min_height
  • min_width
  • min_long
  • min_weight
  • price

I want to retreive the price depending on the ( height - width - long - weight ) of product

I've tried this way :

  • From the controller :
<?php

namespace App\Http\Controllers;


use App\Models\Coli;
use App\Models\Pricing;
use Illuminate\Pipeline\Pipeline;


class ColiPriceController extends Controller
{



    public static $data = [];
 public static function price($id){
    
       ColiPriceController::setData($id);

        $price = app(Pipeline::class)
            ->send(Pricing::query())
            ->through([
               
                \App\Filters\Pricing\MaxHeightPriceFilter::class,
                \App\Filters\Pricing\MinHeightPriceFilter::class,
                
                \App\Filters\Pricing\MaxLongPriceFilter::class,
                \App\Filters\Pricing\MinLongPriceFilter::class,
                
                \App\Filters\Pricing\MaxWidthPriceFilter::class,
                \App\Filters\Pricing\MinwidthPriceFilter::class,
                
                \App\Filters\Pricing\MaxWeightPriceFilter::class,
                \App\Filters\Pricing\MinWeightPriceFilter::class,
        
                ])
            ->thenReturn()
            
            ->first();
}

protected static  function setData($id)
    {
        $coli = Coli::find($id);

        $coli->height = ($coli->height) ? intval($coli->height) : 0;
        $coli->width = ($coli->width) ? intval($coli->width) : 0;
        $coli->longeur = ($coli->longeur) ? intval($coli->longeur) : 0;
        $coli->wieght = ($coli->wieght) ? intval($coli->wieght) : 0;

        $data = [
            'height'    => $coli->height,
            'width'     => $coli->width,
            'long'      => $coli->longeur,
            'weight'     => $coli->wieght,
        ];
       
        return ColiPriceController::$data = $data;

    }
}

From MaxHeightFilter :


<?php

namespace App\Filters\Pricing;

use Closure;

class MaxHeightPriceFilter extends PriceFilter
{
    public $column = "max_height";
    public $dataColumn = "height";
    public $operator = "<=";

    public function handle($request, Closure $next)
    {
      
        return $this->filter($request, $next);
    }

    
}

From PriceFilter :


<?php

namespace App\Filters\Pricing;

use Illuminate\Database\Eloquent\Builder;
use App\Http\Controllers\ColiPriceController;


class PriceFilter 
{

    public $column = "max_weight";
    public $dataColumn = "weight";
    public $operator = "<=";

    
    protected  function filter($request, $next)
    {
       
        if($this->chequePriceToContinue($request)){
         
            return $next(static::removeWhere($request, $this->column));
           }
           
        return $next($request);
        
        // return $next($request->where($this->column, $this->operator, ':'.ColiPriceController::$data[$this->dataColumn]));
       
    }

    public function chequePriceToContinue($request){
        
        $price = $request->where($this->column, $this->operator,  ColiPriceController::$data[$this->dataColumn] )->get();
        if(is_array($price)){
            return true;
        }
        
        return false;
    }


     /**
     * @param Builder $builder
     * @param $whereColumn
     * @return Builder
     */
    public static function removeWhere(Builder $builder, $whereColumn)
    {
        $bindings = $builder->getQuery()->bindings['where'];
        $wheres = $builder->getQuery()->wheres;

        $whereKey = false;
        foreach ($wheres as $key => $where) {
            if ($where['column'] == $whereColumn) {
                $whereKey = $key;
                break;
            }
        }

        if ($whereKey !== false) {
            unset($bindings[$whereKey]);
            unset($wheres[$whereKey]);
        }

        $builder->getQuery()->wheres = $wheres;
        $builder->getQuery()->bindings['where'] = $bindings;

        return $builder;
    }


    
}
eloquent database sql php laravel
1 Answers

Ok, based on your info, I think your solution should look something like this:

First, make sure you have a Price model if you don't have it already. Second, build your query in your controller with your model like so:

//ColiPriceController.php

function findPrices(Request $request) {
    //If id is provided, take coli from db
    if($request->has('id')) {
        //FindOrFail returns 404 if not found
        $coli = Coli::findOrFail($request->id);
    }
    //Start query.
    $query = Price::query();
    //Set values based on coli if set OR by request parameters
    $height = $coli->height ?? $request->height;
    $width = $coli->width ?? $request->width;
    $long = $coli->long ?? $request->long;
    $weight = $coli->weight ?? $request->weight;

    //If the initialized height/width/long/weight is greater than 0, add the where clauses to query
    if($height > 0) {
        $query
            ->where('max_height', '>', $height)
            ->where('min_height', '<', $height);
    }
    if($width > 0) {
        $query
            ->where('max_width', '>', $width)
            ->where('min_width', '<', $width);
    }
    if($long > 0) {
        $query
            ->where('max_long', '>', $long)
            ->where('min_long', '<', $long);
    }
    if($weight > 0) {
        $query
            ->where('max_weight', '>', $weight)
            ->where('min_weight', '<', $weight);
    }
    //I'm using get() and not first() because im not sure if you can guarantee you only find 1 result and not multiple.
    //If you are confident you only get 1 result per query, feel free to make it first()
    return $query->get();
}

So here I only add the where clauses if you initialized the respective variable. This is not mandatory but seemed logical from my point of view. The findPrices function should be called by a route. You can pass id, height, width,long, weight as request parameters. If id is set and the coli is found, the other parameters are ignored.

I hope it helps, any feedback is welcome

Rob Biermann0
Related