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);