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

获取结果

从表中检索所有行

        $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