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

Question

下面是我写的查询语句，可愁死我了，功能实现倒是实现了，写的太难受了，重复好多，请问我该如何简化呢，或者该怎么传入变量查询，或者有什么其他方法简化，请举例，或演示部分，谢谢
publicfunctionpostTreat(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();
}elseif($_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();
}elseif($_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();
}elseif($_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)asassess_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();
}elseif($_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();
}elseif($_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();
}elseif($_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();
}elseif($_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)asassess_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();
}elseif($_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($dataas$key=>$value){
if($value->goods_num>10000){
$value->goods_num=round(($value->goods_num)/10000,1).'万';//将销量转换
}
}
return$data;
}