mysql 查詢

有個新需求，      

一張表有兩個相同的商品排序信息，不同的是 type值和sort不同，如果有type等於1那就連接type等於一的那條商品信息進行排序，如果沒有等於0的那就連排序等於0的進行排序

下麵是代碼，親測有效
  $resultGoods = ProductSales::with('homeGood')
//            ->leftJoin('goods as g','g.goodsId','=','productId')
            ->leftJoin('goods as g',function ($join){
                $join->on('g.goodsId','=','productId')
                    ->where('g.goodsStatus','=',1)
                    ->where('g.isSale','=',1)
                    ->where('g.dataFlag','=',1);
            })
            ->join('good_recommend as gr',function($join){
                $join->on('gr.goods_id','=','g.goodsId')
                    ->where(['gr.status'=>0,'gr.type'=>DB::raw("(SELECT DISTINCT CONCAT(IF(EXISTS(SELECT * FROM tgs_good_recommend WHERE goods_id=productId AND TYPE=1 and status=0),1,0)) AS c FROM tgs_good_recommend)")]);
            }, null,null,'left')
            ->where('product_sales.status',1)
            ->where('saleStartDatetime', '>', $saleStarTime)
            ->where('saleStartDatetime', '<=', $endTime)
            ->orderBy('saleEndDatetime', 'desc')
            ->orderBy('sort','desc')
            ->groupBy('productId')
            ->paginate($size, ['productId', 'currentSalesVolume', 'saleStartDatetime', 'saleEndDatetime', 'salesPeriodNumber','g.shopId','gr.sort'], 'page', $page);