Using the roughest method, write a SQL statement to query simple product screening. How can I simplify it?

the following is the query sentence I wrote, but I am worried to death, but the function is realized. It is too hard to write. I repeat a lot. May I ask how to simplify it, or how to pass in the variable query, or if there are any other methods to simplify it? please give me an example, or the demonstration part, thank you

. 
public function postTreat(Request $request) 
{
    $_page = $request->input("_page"); //
    $_path = $request->input("_path"); // path
    $_sortType = $request->input("_sortType"); //

    $_sales = $request->input("_sales"); //
    $_priceSmall = $request->input("_priceSmall"); //
    $_priceBig = $request->input("_priceBig"); //

    $page=($_page-1)*4;

    // 
    if(empty($_priceSmall)&&empty($_priceBig)){
        // 
        if(empty($_sales)){
            //  
            if($_sortType=="composite" || $_sortType==""){ //  
                $data = DB::table("shop_goods")
                            ->where("goods_cid",$_path)
                            ->where("goods_status",1) // 0 1 2
                            ->where("goods_state",0) // 0 1
                            ->where("goods_recycle",0) // 0 1
                            ->skip($page)
                            ->take(4)
                            ->get();
            }else if($_sortType=="price_up"){ //
                $data = DB::table("shop_goods")
                            ->where("goods_cid",$_path)
                            ->where("goods_status",1) // 0 1 2
                            ->where("goods_state",0) // 0 1
                            ->where("goods_recycle",0) // 0 1
                            ->orderBy("goods_price","asc") // 
                            ->skip($page)
                            ->take(4)
                            ->get();
            }else if($_sortType=="price_down"){ //
                $data = DB::table("shop_goods")
                            ->where("goods_cid",$_path)
                            ->where("goods_status",1) // 0 1 2
                            ->where("goods_state",0) // 0 1
                            ->where("goods_recycle",0) // 0 1
                            ->orderBy("goods_price","desc") //  
                            ->skip($page)
                            ->take(4)
                            ->get();
            }else if($_sortType=="assess_down"){ // 
                // , 
                $data = DB::table("shop_goods")
                            ->leftJoin("shop_assess", "shop_goods.goods_id", "=", "shop_assess.assess_gcode")
                            ->selectRaw("shop_goods.*,COUNT(shop_assess.assess_id) as assess_num")
                            ->where("shop_goods.goods_cid",$_path)
                            ->where("shop_goods.goods_status",1) // 0 1 2
                            ->where("shop_goods.goods_state",0) // 0 1
                            ->where("shop_goods.goods_recycle",0) // 0 1
                            ->groupBy("shop_goods.goods_id")
                            ->orderBy("assess_num","desc")
                            ->get();
            }else if($_sortType=="publish_new"){ //
                $data = DB::table("shop_goods")
                            ->where("goods_cid",$_path)
                            ->where("goods_status",1) // 0 1 2
                            ->where("goods_state",0) // 0 1
                            ->where("goods_recycle",0) // 0 1
                            ->orderBy("goods_time","desc") // 
                            ->skip($page)
                            ->take(4)
                            ->get();
            }
        }else{
            $data = DB::table("shop_goods")
                        ->where("goods_cid",$_path)
                        ->where("goods_status",1) // 0 1 2
                        ->where("goods_state",0) // 0 1
                        ->where("goods_recycle",0) // 0 1
                        ->orderBy("goods_num","desc") // 
                        ->skip($page)
                        ->take(4)
                        ->get();
        }
    }else{
        // 
        if(empty($_sales)){
            //  
            if($_sortType=="composite" || $_sortType==""){
                $data = DB::table("shop_goods")
                            ->where("goods_cid",$_path)
                            ->where("goods_status",1) // 0 1 2
                            ->where("goods_state",0) // 0 1
                            ->where("goods_recycle",0) // 0 1
                            ->whereBetween("goods_price",[$_priceSmall,$_priceBig]) // 
                            ->skip($page)
                            ->take(4)
                            ->get();
            }else if($_sortType=="price_up"){
                $data = DB::table("shop_goods")
                            ->where("goods_cid",$_path)
                            ->where("goods_status",1) // 0 1 2
                            ->where("goods_state",0) // 0 1
                            ->where("goods_recycle",0) // 0 1
                            ->whereBetween("goods_price",[$_priceSmall,$_priceBig]) // 
                            ->orderBy("goods_price","asc") // 
                            ->skip($page)
                            ->take(4)
                            ->get();
            }else if($_sortType=="price_down"){
                $data = DB::table("shop_goods")
                            ->where("goods_cid",$_path)
                            ->where("goods_status",1) // 0 1 2
                            ->where("goods_state",0) // 0 1
                            ->where("goods_recycle",0) // 0 1
                            ->whereBetween("goods_price",[$_priceSmall,$_priceBig]) // 
                            ->orderBy("goods_price","desc") //  
                            ->skip($page)
                            ->take(4)
                            ->get();
            }else if($_sortType=="assess_down"){
                $data = DB::table("shop_goods")
                            ->leftJoin("shop_assess", "shop_goods.goods_id", "=", "shop_assess.assess_gcode")
                            ->selectRaw("shop_goods.*,COUNT(shop_assess.assess_id) as assess_num") //
                            ->where("shop_goods.goods_cid",$_path)
                            ->where("shop_goods.goods_status",1) // 0 1 2
                            ->where("shop_goods.goods_state",0) // 0 1
                            ->where("shop_goods.goods_recycle",0) // 0 1
                            ->whereBetween("shop_goods.goods_price",[$_priceSmall,$_priceBig]) // 
                            ->groupBy("shop_goods.goods_id")
                            ->orderBy("assess_num","desc")
                            ->get();
            }else if($_sortType=="publish_new"){
                $data = DB::table("shop_goods")
                            ->where("goods_cid",$_path)
                            ->where("goods_status",1) // 0 1 2
                            ->where("goods_state",0) // 0 1
                            ->where("goods_recycle",0) // 0 1
                            ->whereBetween("goods_price",[$_priceSmall,$_priceBig]) // 
                            ->orderBy("goods_time","desc") // 
                            ->skip($page)
                            ->take(4)
                            ->get();
            }
        }else{
            $data = DB::table("shop_goods")
                        ->where("goods_cid",$_path)
                        ->where("goods_status",1) // 0 1 2
                        ->where("goods_state",0) // 0 1
                        ->where("goods_recycle",0) // 0 1
                        ->whereBetween("goods_price",[$_priceSmall,$_priceBig])
                        ->orderBy("goods_num","desc")
                        ->skip($page)
                        ->take(4)
                        ->get();
        }
    }

    foreach($data as $key => $value){
        if($value->goods_num>10000){
            $value->goods_num = round(($value->goods_num)/10000,1).""; //
        }
    }
    return $data;
}
Windows visual-studio-code mysql php laravel
Mar.17,2021

provide a more elegant code
write a rough code, you need to rewrite the details 

     protected $orderByMap = [
        'assess_down' => ['assess_num', 'desc'],
        'price_up' => ['goods_price', 'asc'],
        'price_down' => ['goods_price', 'desc'],
        'publish_new' => ['goods_time', 'desc'],
    ];

    /**
     * @param Request $request
     * @return mixed
     */
    public function postTreat(Request $request)
    {
        // 
        $_page = $request->input("_page");
        $page = ($_page-1)*4;

        /**
         *  Query
         *
         * @var $shopGoodsQuery \Illuminate\Database\Query\Builder
         */
        $shopGoodsQuery = DB::table('shop_goods')
                            ->where('goods_status', 1)// 0 1 2
                            ->where('goods_state', 0)// 0 1
                            ->where('goods_recycle', 0); // 0 1;

        // 
        $shopGoodsQuery->when($request->input('_priceSmall'), function (Builder $query, $priceSmall) {
            $query->where('goods_price', '>', $priceSmall);
        });

        // 
        $shopGoodsQuery->when($request->input('_priceBig'), function (Builder $query, $priceBig) {
            $query->where('goods_price', '<', $priceBig);
        });

        // 
        $shopGoodsQuery->when($request->input("_sales"), function (Builder $query, $sales) {
            $query->where('goods_num', 'desc');
        }, function (Builder $query, $sales) {
            // ,  else

        });

        //  map 
        $shopGoodsQuery->when($request->input("_sortType"), function (Builder $query, $sortType) {
            //  
            list($field, $orderBy) = $this->orderByMap[$sortType] ?? array_first($this->orderByMap);
            $query->orderBy($field, $orderBy);
        });


        // 
        $data = $shopGoodsQuery->skip($page)->take(4)->get();

        dd($data);
    }

by the way, send the Laravel technology exchange group number: 584453488

< hr >

add that when has the same effect as if . If there is no when , replace

with if . 
 // 
$_page = $request->input("_page");
$page = ($_page-1)*4;

/**
 *  Query
 *
 * @var $shopGoodsQuery \Illuminate\Database\Query\Builder
 */
$shopGoodsQuery = DB::table('shop_goods')
                    ->where('goods_status', 1)// 0 1 2
                    ->where('goods_state', 0)// 0 1
                    ->where('goods_recycle', 0); // 0 1;

// 
if ($request->filled('_priceSmall')) {
    $shopGoodsQuery->where('goods_price', '>', $request->input('_priceSmall'));
}

// 
if ($request->filled('_priceBig')) {
    $shopGoodsQuery->where('goods_price', '>', $request->input('_priceBig'));
}
There are many repetitions in the

code, so the key is to find out what the non-repetition is, and then put the repetition into a piece, and then subdivide it according to different conditions.

for example, many of your above statements are sorted according to $_ sortType. Naturally, you can first use a parameter to store the most basic query statements; 

$basic = DB::table('shop_goods')
            ->where('goods_cid',$_path)
            ->where('goods_status',1) // 0 1 2
            ->where('goods_state',0) // 0 1
            ->where('goods_recycle',0) // 0 1

then judge the condition based on $_ sortType: 

if($_sortType=="composite" || $_sortType==""){ //  
    $data = $basic
                ->skip($page)
                ->take(4)
                ->get();
}else if($_sortType=="price_up"){ //
    $data = $basic
                ->orderBy('goods_price','asc') // 
                ->skip($page)
                ->take(4)
                ->get();
}else if($_sortType=="price_down"){ //
    $data = $basic
                ->orderBy('goods_price','desc') //  
                ->skip($page)
                ->take(4)
                ->get();
}else if($_sortType=="assess_down"){ // 
    $data = DB::table('shop_goods')
                ->leftJoin('shop_assess', 'shop_goods.goods_id', '=', 'shop_assess.assess_gcode')
                ->selectRaw('shop_goods.*,COUNT(shop_assess.assess_id) as assess_num')
                ->where('shop_goods.goods_cid',$_path)
                ->where('shop_goods.goods_status',1) // 0 1 2
                ->where('shop_goods.goods_state',0) // 0 1
                ->where('shop_goods.goods_recycle',0) // 0 1
                ->groupBy('shop_goods.goods_id')
                ->orderBy('assess_num','desc')
                ->get();
}else if($_sortType=="publish_new"){ //
    $data = $basic
                ->orderBy('goods_time','desc') // 
                ->skip($page)
                ->take(4)
                ->get();
}
This is the general idea of

.

I have an idea to pull out the shared ones. I see that the things you repeat are database link instances, where and order, you can do if else outside and then combine where and order, except for the other things you want to link query directly

. 
$db->where($where)->order($order)->skip($page)->take(4)->get();

or you may feel uncomfortable if you write too much code. You can write some processing in the Model layer so that the code looks much simpler

.

Previous: The @ click bound method in vue cannot be executed immediately.

Next: What is the cause of this warning in the console, reactjs?

css mysql arrays josn react html typescript webpack npm sass R objective-c .net sql-server jquery python-3.x angularjs django angular excel regex iphone ajax linux xml pandas vba spring database wordpress string wpf xcode windows bash postgresql oracle multithreading eclipse list firebase algorithm macos forms image scala visual-studio azure bootstrap spring-boot react-native python-2.7 docker performance function winforms matlab powershell apache dataframe api sqlite numpy rest shell selenium flutter dart maven loops qt swing android-studio csv express file class tensorflow sorting codeigniter perl
© 2021 CodesHelper
Advertising Contact us About us
Menu