使用 DB::raw 用 CONCAT 附加其它資料到欄位中，及使用 GROUP_CONCAT
$categories =$categories->select([
'categories.id',
'categories.name',
'categories.intro',
DB::raw("(CASE WHEN logo is not null THEN CONCAT('$host',logo) END) as logo"),
DB::raw("(CASE WHEN cover is not null THEN CONCAT('$host',cover) END) as cover"),
'categories.sort',
]);
$user = $user->addSelect([
'favoriteProducts' => UserFavoriteProductDB::where('user_id',$id)->select([
DB::raw("GROUP_CONCAT(product_id) as favoriteProducts")
]),
'favoriteVendors' => UserFavoriteVendorDB::where('user_id',$id)->select([
DB::raw("GROUP_CONCAT(vendor_id) as favoriteVendors")
]),
]);addSelect 選擇其它資料表資料，可使用原生語法
if($lang != '' && in_array($lang,$langs)){
$curations = $curations->addSelect([
// DB::raw("(SELECT main_title from curation_langs where curations.id = curation_langs.curation_id and lang = '$lang' limit 1 ) as main_title_$lang"),
// DB::raw("(SELECT sub_title from curation_langs where curations.id = curation_langs.curation_id and lang = '$lang' limit 1 ) as sub_title_$lang"),
// DB::raw("(SELECT caption from curation_langs where curations.id = curation_langs.curation_id and lang = '$lang' limit 1 ) as caption_$lang"),
'main_title_'.$lang => CurationLangDB::whereColumn('curations.id', 'curation_langs.curation_id')
->where('lang',$lang)->select('main_title')->limit(1),
'sub_title_'.$lang => CurationLangDB::whereColumn('curations.id', 'curation_langs.curation_id')
->where('lang',$lang)->select('sub_title')->limit(1),
'caption_'.$lang => CurationLangDB::whereColumn('curations.id', 'curation_langs.curation_id')
->where('lang',$lang)->select('caption')->limit(1),
]);
$vendors = $vendors->addSelect([
'name_'.$lang => VendorLangDB::whereColumn('vendors.id', 'vendor_langs.vendor_id')
->select(DB::raw("(CASE WHEN lang = '$lang' THEN name END) as name"))->limit(1),
'description_'.$lang => VendorLangDB::whereColumn('vendors.id', 'vendor_langs.vendor_id')
->select(DB::raw("(CASE WHEN lang = '$lang' THEN description END) as description"))->limit(1),
]);
}按類型撈取Model裡的關聯
foreach ($curations as $curation) {
$curation->background_image ? $curation->background_image = env('AWS_FILE_URL').$curation->background_image : '';
if($curation->type == 'image'){ //圖片版型
$curation->images = $curation->images;
}
if($curation->type == 'event'){ //活動版型
$curation->events = $curation->events;
}
if($curation->type == 'block'){ //宮格版型
$curation->blocks = $curation->blocks;
}
if($curation->type == 'nowordblock'){ //宮格(無字)版型
$curation->noWordBlocks = $curation->noWordBlocks;
}
if($curation->type == 'vendor'){ //品牌版型
$curation->vendors = $curation->vendors;
}
if($curation->type == 'product'){ //產品版型
$curation->products = $curation->products;
}
}在模型中也可以這樣用
protected $langs;
protected $lang = '';
protected $awsFileUrl;
public function __construct()
{
$this->langs = ['','en','jp','kr','th'];
$this->awsFileUrl = env('AWS_FILE_URL');
$this->lang = request()->lang;
}
public function products()
{
$products = $this->hasMany(CurationProductDB::class)->with('image')
->join('products','products.id','curation_products.product_id')
->join('vendors','vendors.id','products.vendor_id')
->whereIn('products.status',[1,-3])
->select([
'curation_products.id',
'curation_products.curation_id',
'curation_products.sort',
'products.id as product_id',
'products.name',
'products.curation_text_top',
'products.curation_text_bottom',
DB::raw("(CASE WHEN products.fake_price > 0 THEN products.fake_price END) as fake_price"),
'products.price',
'products.status',
'vendors.name as vendor_name',
//由於 product images 資料量非常多 此種方式會拖垮整體速度 改用 with 方式讀取
// DB::raw("(SELECT CONCAT('$this->awsFileUrl',filename) from product_images where products.id = product_images.product_id order by sort asc limit 1 ) as images"),
// 'image' => ProductImageDB::whereColumn('products.id', 'product_images.product_id')
// ->select(DB::raw("(CASE WHEN filename is not null THEN (CONCAT('$this->awsFileUrl',filename)) END) as image"))->orderBy('sort','asc')->limit(1),
]);
if($this->lang != '' && in_array($this->lang,$this->langs)){
$products = $products->addSelect([
// DB::raw("(SELECT name from vendor_langs where vendors.id = vendor_langs.vendor_id and lang = '$this->lang' limit 1 ) as vendor_name_$this->lang"),
// DB::raw("(SELECT name from product_langs where products.id = product_langs.product_id and lang = '$this->lang' limit 1 ) as name_$this->lang"),
// DB::raw("(SELECT curation_text_top from product_langs where products.id = product_langs.product_id and lang = '$this->lang' limit 1 ) as curation_text_top_$this->lang"),
// DB::raw("(SELECT curation_text_bottom from product_langs where products.id = product_langs.product_id and lang = '$this->lang' limit 1 ) as curation_text_bottom_$this->lang"),
'vendor_name_'.$this->lang => VendorLangDB::whereColumn('vendors.id', 'vendor_langs.vendor_id')
->where('lang',$this->lang)->select('name')->limit(1),
'name_'.$this->lang => ProductLangDB::whereColumn('products.id', 'product_langs.product_id')
->where('lang',$this->lang)->select('name')->limit(1),
'curation_text_top_'.$this->lang => ProductLangDB::whereColumn('products.id', 'product_langs.product_id')
->where('lang',$this->lang)->select('curation_text_top')->limit(1),
'curation_text_bottom_'.$this->lang => ProductLangDB::whereColumn('products.id', 'product_langs.product_id')
->where('lang',$this->lang)->select('curation_text_bottom')->limit(1),
]);
}
return $products;
}在 with 中可以這樣用
$curation = CurationDB::with([
'langs',
'vendors' => function($q){
$q->join('vendors', 'vendors.id', '=', 'curation_vendors.vendor_id')
->where('vendors.is_on',1)
->select(
'curation_vendors.*',
'vendors.name',
'vendors.img_logo',
'vendors.img_cover',
'vendors.curation',
'vendors.is_on',
)->orderBy('sort', 'asc');
},
'vendors.langs',
'products',
'products.curationImage',
'images',
'blocks',
'events',
])->findOrFail($id);使用三元運算切換選擇的欄位
$product = ProductModelDB::join('products','products.id','product_models.product_id')
->where('status',1)
->select([
'allow_country',
$shippingMethod = 1 ? 'airport_days as stockDays' : 'hotel_days as stockDays',
])->findOrFail($id);當欄位類別非常多時，例如:狀態、國家…，直接用迴圈產生出 WHEN … THEN … 方式來取代欄位資料
$row = 'status';
$replaceStatus = '';
$status = [0 => '尚未付款', 1 => '待出貨', 2 => '集貨中', 3 => '已出貨', 4 => '已完成', -1 => '已取消'];
foreach ($status as $key => $value) {
$replaceStatus .= " WHEN $row = $key THEN '$value' ";
}
return $this->hasMany(OrderDB::class)
->select([
'id',
'user_id',
'order_number',
DB::raw("(CASE WHEN NOW() >= DATE_ADD(created_at, INTERVAL 6 HOUR) THEN 0
ELSE 1 END) as in_six_hour"),
DB::raw("(DATE_FORMAT(created_at,'%Y-%m-%d')) as create_date"),
DB::raw("(amount - spend_point - discount + shipping_fee + parcel_tax) as price"),
DB::raw("(CASE $replaceStatus END) as order_status"),
'status',
])->orderBy('created_at','desc')->limit(100);