用最粗糙的方法，写出了查询简单的商品筛选的 SQL 语句，请问我该如何简化？？

Question

下面是我写的查询语句，可愁死我了，功能实现倒是实现了，写的太难受了，重复好多，请问我该如何简化呢，或者该怎么传入变量查询，或者有什么其他方法简化，请举例，或演示部分，谢谢 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; }