Laravel 数据库交互 - 查询构造器

Laravel框架
606
0
0
2023-03-14
结合 Bootstrap + Vue 组件实现 Laravel 异步分页功能
上一篇
Laravel实战之请求频率限制(Throttle中间件)
下一篇

获取结果

从表中检索所有行

$articles = DB::table('article')->get();
foreach ($articles as $article) {
    echo $article->title.PHP_EOL;
}

从表中检索单行或单列

#通过id字段获取一行
$article = DB::table('article')->find(3);
#获取一行
$article = DB::table('article')->where('title', '339911y')->first();
echo $article->content.PHP_EOL;
#从纪录中提取单个值
echo DB::table('article')->where('title', '339911y')->value('content').PHP_EOL;

获取某一列的值

$titles = DB::table('article')->pluck('title');
foreach ($titles as $title) {
    echo $title.PHP_EOL;
}
#从表中检索单行或单列
$regions = DB::connection('mysql2')->table('regions')->pluck('name', 'code');
 
foreach ($regions as $code => $name) {
    echo $code.' => '.$name.PHP_EOL;
}

分块结果

#以一次 1000 条记录的块为单位检索整个 regions 表。
DB::connection('mysql2')->table('regions')->orderBy('id')->chunk(1000, function ($regions) {
    foreach ($regions as $region) {
        echo $region->code.' => '.$region->name.PHP_EOL;
    }
    //您可以通过从闭包中返回 false 来停止处理其余的块
    //return false;
});
 
#如果您打算在分块时更新检索到的记录，最好使用 chunkById 方法
DB::connection('mysql2')->table('regions')->chunkById(100, function ($regions) {
    foreach ($regions as $region) {
//                echo $region->code.' => '.$region->name.PHP_EOL;
        DB::table('users')->where('id', $region->id)->update(['views' => 1]);
    }
});

Lazily 流式传输结果

DB::table('article')->orderBy('id')->lazy()->each(function ($article) {
    echo $article->title.PHP_EOL;
});
 
#如果您打算在迭代它们时更新检索到的记录，最好使用 lazyById 或 lazyByIdDesc 方法。
DB::table('users')->where('active', false)->lazyById()->each(function ($user) {
    DB::table('users')
        ->where('id', $user->id)
        ->update(['active' => true]);
});

聚合函数

$users = DB::table('users')->count();
$price = DB::table('orders')->max('price');
$price = DB::table('orders')->where('finalized', 1)->avg('price');

判断记录是否存在

if (DB::table('orders')->where('finalized', 1)->exists()) {
    // ...
}
 
if (DB::table('orders')->where('finalized', 1)->doesntExist()) {
    // ...
}

Select 语句

#筛选字段
$users = DB::table('users')->select('name', 'email as user_email')->get();
#去重
$users = DB::table('users')->distinct()->get();
 
#addSelect
$query = DB::table('users')->select('name');
$users = $query->addSelect('age')->get();

原生表达式

$users = DB::table('users')
    ->select(DB::raw('count(*) as user_count, status'))
    ->where('status', '<>', 1)
    ->groupBy('status')
    ->get();
#可以使用以下方法代替 DB::raw
#selectRaw
#whereRaw / orWhereRaw
#havingRaw / orHavingRaw
#orderByRaw
#groupByRaw

Joins

#Inner Join 语句
$users = DB::table('users')
    ->join('contacts', 'users.id', '=', 'contacts.user_id')
    ->join('orders', 'users.id', '=', 'orders.user_id')
    ->select('users.*', 'contacts.phone', 'orders.price')
    ->get();
 
#Left Join / Right Join 语句
$users = DB::table('users')
    ->leftJoin('posts', 'users.id', '=', 'posts.user_id')
    ->get();
 
$users = DB::table('users')
    ->rightJoin('posts', 'users.id', '=', 'posts.user_id')
    ->get();

Where 语句

$users = DB::table('users')
    ->where('votes', '=', 100)
    ->where('age', '>', 35)
    ->get();
 
$users = DB::table('users')
    ->where('votes', '>=', 100)
    ->get();
 
$users = DB::table('users')
    ->where('votes', '<>', 100)
    ->get();
 
$users = DB::table('users')
    ->where('name', 'like', 'T%')
    ->get();
 
$users = DB::table('users')->where([
    ['status', '=', '1'],
    ['subscribed', '<>', '1'],
])->get();
 
#Or Where 语句
$users = DB::table('users')
    ->where('votes', '>', 100)
    ->orWhere('name', 'John')
    ->get();
 
#JSON Where 语句
$users = DB::table('users')
    ->where('preferences->dining->meal', 'salad')
    ->get();
 
#whereBetween / orWhereBetween
$users = DB::table('users')
    ->whereBetween('votes', [1, 100])
    ->get();
 
#whereNotBetween / orWhereNotBetween
$users = DB::table('users')
    ->whereNotBetween('votes', [1, 100])
    ->get();
 
#whereIn / whereNotIn / orWhereIn / orWhereNotIn
$users = DB::table('users')
    ->whereIn('id', [1, 2, 3])
    ->get();
$users = DB::table('users')
    ->whereNotIn('id', [1, 2, 3])
    ->get();
 
#whereNull / whereNotNull / orWhereNull / orWhereNotNull
$users = DB::table('users')
    ->whereNull('updated_at')
    ->get();
$users = DB::table('users')
    ->whereNotNull('updated_at')
    ->get();
 
#whereDate / whereMonth / whereDay / whereYear / whereTime
$users = DB::table('users')
    ->whereDate('created_at', '2016-12-31')
    ->get();
$users = DB::table('users')
    ->whereMonth('created_at', '12')
    ->get();
$users = DB::table('users')
    ->whereDay('created_at', '31')
    ->get();
$users = DB::table('users')
    ->whereYear('created_at', '2016')
    ->get();
$users = DB::table('users')
    ->whereTime('created_at', '=', '11:20:45')
    ->get();
 
#whereColumn / orWhereColumn
$users = DB::table('users')
    ->whereColumn('first_name', 'last_name')
    ->get();
$users = DB::table('users')
    ->whereColumn('updated_at', '>', 'created_at')
    ->get();
$users = DB::table('users')
    ->whereColumn([
        ['first_name', '=', 'last_name'],
        ['updated_at', '>', 'created_at'],
    ])->get();
 
#逻辑分组
$users = DB::table('users')
    ->where('name', '=', 'John')
    ->where(function ($query) {
        $query->where('votes', '>', 100)
            ->orWhere('title', '=', 'Admin');
    })
    ->get();
 
#子查询 Where 语句
$users = User::where(function ($query) {
    $query->select('type')
        ->from('membership')
        ->whereColumn('membership.user_id', 'users.id')
        ->orderByDesc('membership.start_date')
        ->limit(1);
}, 'Pro')->get();

Ordering, Grouping, Limit & Offset

#排序
$users = DB::table('users')
    ->orderBy('name', 'desc')
    ->get();
$users = DB::table('users')
    ->orderBy('name', 'desc')
    ->orderBy('email', 'asc')
    ->get();
#latest 和 oldest 方法可以方便让你把结果根据日期排序。查询结果默认根据数据表的 created_at 字段进行排序 。或者，你可以传一个你想要排序的列名
$user = DB::table('users')
    ->latest()
    ->first();
 
#随机排序
$randomUser = DB::table('users')
    ->inRandomOrder()
    ->first();
 
#groupBy 和 having 方法
$users = DB::table('users')
    ->groupBy('account_id')
    ->having('account_id', '>', 100)
    ->get();
$report = DB::table('orders')
    ->selectRaw('count(id) as number_of_orders, customer_id')
    ->groupBy('customer_id')
    ->havingBetween('number_of_orders', [5, 15])
    ->get();
$users = DB::table('users')
    ->groupBy('first_name', 'status')
    ->having('account_id', '>', 100)
    ->get();
 
#Limit 和 Offset
$users = DB::table('users')->skip(10)->take(5)->get();
#或者，你可以使用 limit 和 offset 方法。这些方法在功能上等同于 take 和 skip 方法，如下:
$users = DB::table('users')->offset(10)->limit(5)->get();

条件语句 when

#when 方法只有当第一个参数为 true 的时候才执行给定的闭包
$role=1;
$users = DB::table('users')
    ->when($role, function ($query, $role) {
        return $query->where('role_id', $role);
    })
    ->get();
 
#只有当第一个参数的计算结果为 false 时，这个闭包才会执行
$sortByVotes = 0;
$users = DB::table('users')
    ->when($sortByVotes, function ($query, $sortByVotes) {
        return $query->orderBy('votes');
    }, function ($query) {
        return $query->orderBy('name');
    })
    ->get();

插入语句

DB::table('users')->insert([
    'email' => 'kayla@example.com',
    'votes' => 0
]);
DB::table('users')->insert([
    ['email' => 'picard@example.com', 'votes' => 0],
    ['email' => 'janeway@example.com', 'votes' => 0],
]);
#自增 IDs
$id = DB::table('users')->insertGetId(
    ['email' => 'john@example.com', 'votes' => 0]
);

Update 语句

$affected = DB::table('users')
    ->where('id', 1)
    ->update(['votes' => 1]);
DB::table('users')
    ->updateOrInsert(
        ['email' => 'john@example.com', 'name' => 'John'],
        ['votes' => '2']
    );
#更新 JSON 字段
$affected = DB::table('users')
    ->where('id', 1)
    ->update(['options->enabled' => true]);
 
#自增与自减
DB::table('users')->increment('votes');
DB::table('users')->increment('votes', 5);
DB::table('users')->decrement('votes');
DB::table('users')->decrement('votes', 5);

删除语句

$deleted = DB::table('users')->where('votes', '>', 100)->delete();

悲观锁

DB::table('users')
    ->where('votes', '>', 100)
    ->sharedLock()
    ->get();
DB::table('users')
    ->where('votes', '>', 100)
    ->lockForUpdate()
    ->get();

调试

DB::table('users')->where('votes', '>', 100)->dd();
 
DB::table('users')->where('votes', '>', 100)->dump();

参考

https://learnku.com/docs/laravel/9.x/queries/12246

结合 Bootstrap + Vue 组件实现 Laravel 异步分页功能
上一篇
Laravel实战之请求频率限制(Throttle中间件)
下一篇