一些sql-优快云博客

此函数展示了如何使用Eloquent ORM通过左连接查询cu_report_info和customer_users表，筛选出特定用户($cu_id)的正常状态报告，已输入的数据，并按检查日期降序分页返回。

public function trendList($cu_id)
{
    $res = CuReport::leftjoin('cu_report_info', 'cu_report_info.cur_id', '=', 'cu_report.id')
        ->leftjoin('customer_users', 'customer_users.id', '=', 'cu_report.cu_id')
        ->where(['cu_id' => $cu_id, 'cu_report.status' => CuReport::STATUS_NORMAL, 'is_input' => CuReport::IS_INPUT_YES])->select('cu_report.id', 'customer_users.lastname', 'customer_users.sex', DB::raw('DATE_FORMAT(check_date,"%Y-%m-%d") as check_date'))->orderBy('cu_report_info.check_date', 'desc')->paginate(config('admin.globals.PAGESIZE'));
    return response_json(200, 'success', $res);

}

DB::RAW的用法

public function scListBase($uid){

    $where = [
        'shopping_cart_sort.user_id'     => $uid,
        'shops.status'=> GymShops::STATUS_MORMAL,
        'shops.check_status'=>GymShops::CHECK_STATUS_PASS,
        'shops.shop_status'=>GymShops::SHOP_STATUS_ON
    ];

    $with_select = ['shopping_cart.sd_id','shops_dishes.dish_name','shops_dishes.pic_url', 'shops_dishes.price', 'shops_dishes.num', 'shops_dishes.discount_price', 'shops_dishes.up_status', 'shops_dishes.dish_up_time', 'shops_dishes.status',
        'shopping_cart.id','shopping_cart.s_id','shopping_cart.sd_num','shopping_cart.sd_up'];
    $select = ['shopping_cart_sort.s_id', 'shops.shop_name', 'shops.start_time', 'shops.end_time', 'shops.status'];

    $data = ShoppingCartSort::leftJoin('shops', 's_id', '=', 'shops.id')
       ->where($where)
        ->with(['shoppingCart'=>function($query) use($with_select, $uid) {
            $query->leftJoin('shops_dishes', 'sd_id', '=', 'shops_dishes.id')
                ->orderBy('shopping_cart.updated_at', 'desc')
                ->where('shopping_cart.user_id', $uid )
                ->select($with_select);
        }])
        ->orderBy('up_time', 'desc')
        ->select($select)
        ->get();


    $res = [
        'url_prefix' => 'https://'. env('OSS_BUCKET') . '.'. basename(env('OSS_ENDPOINT')) .'/',
        'data' => $data
    ];


    return [200, $res];


}

$channels_info= NewUsersChannels::where(['user_id'=>$v->id])->pluck('able_id')->toArray();
$v->able_id=implode(',',$channels_info);

//开头

$query = ActivityUser::select('au_id', 'au_nickname', 'au_info_phone', 'head_pic', 'au_end_at','unionid','openid');

if (empty($data['privacy'])){
    if (isset($data['au_nickname'])) {
        $query = $query->where('activity_user.au_nickname', $data['au_nickname']);
    }
}
if ($data['privacy'] == 1){
    if (isset($data['au_nickname'])) {
        $query = $query->where('activity_user.au_nickname','like', '%' . $data['au_nickname'] . '%');
    }
}


if (isset($data['au_info_phone'])) {
    $query = $query->where('activity_user.au_info_phone', $data['au_info_phone']);
}

//拉取 type类型是8的信息 ，和type类型是1的信息 且type1中  某一字段信息不能为空
$query->where(function ($query) {
    $query->where('type',8)->orWhere(function ($query) {
        $query->where('type',1)->whereNotNull('au_info_phone');
    });
});

$result = $query->orderby('au_id', 'desc')->paginate(config('setting.PAGESIZE'));;

如果数据库某一字段是二进制类型的，你想更新某一个值可以用concat 函数，具体提如下

update tbl_userbase set ub_nickname=concat('对对对') where ub_id=3558;