laravel常用查询

女爷i 2023-01-06 01:07 182阅读 0赞

### 插入 ###

DB::table('t_admin_users')->insert([
        [
            'role_id'    => $allData['roleId'],
            'username'   => $allData['userName'],
            'real_name'  => $allData['realName'],
            'avatar'     => "",
            'created_at' => date("Y-m-d H:i:s", time()),
            'group_id'   => $adminUser->group_id,
            'password'   => md5(getRandString(10))
        ]
    ]);

### 删除 ###

DB::table('t_admin_users')->where('id', $allData['toUserId'])->delete();

### 只取某个值 ###

$day = DB::table('t_backend_user_disable_record')->where('user_id', $user->user_id)->value('day');

### 联查 ###

$user = DB::connection('appsql')->table('k_users')
          ->leftJoin("k_user_wallet", "k_user_wallet.user_id", "=", "k_users.id")
          ->leftJoin("k_user_infos", "k_user_infos.user_id", "=", "k_users.id")
          ->leftJoin("kl_admin.t_backend_user_disable_record", "kl_admin.t_backend_user_disable_record.user_id", "=", "k_users.id")
          ->select("*", "k_users.status as banned_status", "k_users.id as user_id")
          ->where('k_users.id', $allData['toUserId'])->first();

### 更新 ###

DB::connection('appsql')->table('k_users')
          ->where('id', $toUserId)
          ->update(['invite_type' => $inviteType]);

### 自定义count和sum ###

$logs = DB::connection('appsql')->table('k_user_infos')
          ->leftJoin('k_user_wallet', 'k_user_infos.user_id', '=', 'k_user_wallet.user_id')
          ->leftJoin('k_users', 'k_users.id', '=', 'k_user_infos.user_id')
          ->select(
              DB::raw("COUNT(k_user_infos.`device_name`= 'ios' or null) as total_ios"),
              DB::raw("COUNT(k_user_infos.`device_name`= 'aos' or null) as total_aos"),
              DB::raw("COUNT(k_user_infos.`device_name`= 'ios' and k_user_wallet.`total_recharge` > 0 or null) as ios_recharge_num"),
              DB::raw("COUNT(k_user_infos.`device_name`= 'aos' and k_user_wallet.`total_recharge` > 0 or null) as aos_recharge_num"),
              DB::raw("sum(case when k_user_infos.`device_name`= 'ios' then k_user_wallet.`total_recharge` else 0 end) as ios_recharge_sum"),
              DB::raw("sum(case when k_user_infos.`device_name`= 'aos' then k_user_wallet.`total_recharge` else 0 end) as aos_recharge_sum"),
              DB::raw("COUNT(k_user_infos.`device_name`= 'ios' and k_users.`reg_time` > '{$date}' or null) as ios_today_num"),
              DB::raw("COUNT(k_user_infos.`device_name`= 'aos' and k_users.`reg_time` > '{$date}' or null) as aos_today_num"),
              DB::raw("COUNT(k_user_infos.`device_name`= 'ios' and k_users.`reg_time` > '{$date}' and k_user_wallet.`total_recharge` > 0 or null) as ios_today_recharge_num"),
              DB::raw("COUNT(k_user_infos.`device_name`= 'aos' and k_users.`reg_time` > '{$date}' and k_user_wallet.`total_recharge` > 0 or null) as aos_today_recharge_num")
          )
          ->get();

### 按类型查询 ###

//获取查询类型
    function getTimeTye($type) {
        switch ($type) {
            case 1://小时
                $format = "DATE_FORMAT(time,'%Y-%m-%d-%H')";
                break;
            case 2://天
                $format = "DATE_FORMAT(time,'%Y-%m-%d')";
                break;
            case 3://周
                $format = "DATE_FORMAT(time,'%Y-%u')";
                break;
            case 4://月
                $format = "DATE_FORMAT(time,'%Y-%m')";
                break;
            case 5://分钟
                $format = "DATE_FORMAT(time,'%Y-%m-%d-%H-%i')";
                break;
        }
        return $format;
    }

### having 判断自定义字段 ###

$users = DB::connection('appsql')->table('k_users')
           ->select("*", DB::raw("count(k_users.id) as num"))
           ->where("reg_ip", "<>", "unknown")
           ->where("reg_ip", "<>", "")
           ->where("reg_ip", "<>", "127.0.0.1")
           ->groupBy("reg_ip")
           ->having('num', '>', 3)
           ->limit(50)->get();