Laravel – 搜尋資料用法筆記

with() 及 join 使用，with() 需要在 model 中定義相關的 function ，其主要就是使用 where in 方式將相關的資料帶出。

$products = ProductDB::with('image')->join('vendors','vendors.id','products.vendor_id')
->where('vendors.is_on',1)
->whereIn('products.status',[1,-3]);

where()中使用 function

$now = date('Y-m-d H:i:s');
$curation = CurationDB::where('is_on',1)->where(function ($query) use ($now) {
    $query->where([['start_time','<=',$now],['end_time','>=',$now]])
        ->orWhere([['start_time','<=',$now],['end_time',null]])
        ->orWhere([['start_time',null],['end_time',null]])
        ->orWhere([['start_time',null],['end_time','>=',$now]]);
});

$products = $products->where(function ($query) use ($keyword,$lang) {
    if($lang){
        $pIds = ProductLangDB::where([['lang',$lang],['name','like',"%$keyword%"]])->select('product_id');
        $vIds = VendorLangDB::where([['lang',$lang],['name','like',"%$keyword%"]])->select('vendor_id');
        $query->orWhereIn('products.id',$pIds)
            ->orWhereIn('vendors.id',$vIds);
    }else{
        $query->where('vendors.name','like',"%$keyword%")
        ->orWhere('products.name','like',"%$keyword%");
    }
});

FIND_IN_SET 用法，當資料內容為 1,2,3,4,5,6 逗號分隔時使用。

if(isset($type) && $type == 'vendor'){
    foreach ($categories as $category) {
        $vendors = VendorDB::whereRaw("FIND_IN_SET('$category->id',vendors.categories)");
        $vendors = $vendors->orderBy('id','asc')->select(['id','name']);
        if($lang != '' && in_array($lang,$langs)){
            $vendors = $vendors->addSelect([
                'name_'.$lang => VendorLangDB::whereColumn('vendors.id', 'vendor_langs.vendor_id')
                ->select(DB::raw("(CASE WHEN lang = '$lang' THEN name END)"))->limit(1),
            ]);
        }
        $category->vendors = $vendors->get();
    }
}

if(isset($categoryIds) || isset($vendorIds)){
    $c = '';
    if(isset($categoryIds)){
        $categoryIds = ltrim(rtrim($categoryIds,','),','); //去除左右邊逗點
        $cIds = explode(',',$categoryIds);
        $c = 'vendors.categories is null ';
        for($i=0;$i<count($cIds);$i++){
            $c .= " OR FIND_IN_SET('$cIds[$i]',vendors.categories) ";
        }
    }
    if (isset($vendorIds)) {
        $vendorIds = ltrim(rtrim($vendorIds,','),','); //去除左右邊逗點
        if ($c) {
            $c .= " OR vendors.id in ( $vendorIds ) ";
        } else {
            $c = " vendors.id in ( $vendorIds ) ";
        }
    }
    $products = $products->where(function($query)use($c){
        $query->whereRaw($c);
    });
}

計算資料後撈出

if(isset($register) && $register > 0){
    $codes = UserDB::where('refer_code','!=','')->select([
                'refer_code',
                DB::raw('count(id) as count'),
            ])->groupBy('refer_code')
            ->having('count', '>', $register)
            ->get()->pluck('refer_code')->all();
    $referCodes = $referCodes->whereIn('code',$codes);
}