Laravel 13 查询构建器增强详解

摘要

Laravel 13 对查询构建器进行了多项增强，提供了更强大的查询能力。本文将深入讲解 Laravel 13 的查询构建器增强，包括：

高级查询方法
子查询与联合查询
向量查询支持
查询优化技巧
实战案例与最佳实践

本文适合希望掌握高级查询技巧的 Laravel 开发者。

1. 基础查询

1.1 基本查询

use Illuminate\Support\Facades\DB;

$users = DB::table('users')->get();

$user = DB::table('users')->where('id', 1)->first();

$value = DB::table('users')->where('id', 1)->value('email');

1.2 聚合查询

$count = DB::table('users')->count();

$price = DB::table('orders')->max('price');

$average = DB::table('orders')->avg('price');

$sum = DB::table('orders')->sum('total');

1.3 分块查询

DB::table('users')->chunk(100, function ($users) {
    foreach ($users as $user) {
        // 处理用户
    }
});

2. 高级查询

2.1 条件查询

$users = DB::table('users')
    ->when($request->status, function ($query, $status) {
        $query->where('status', $status);
    })
    ->when($request->search, function ($query, $search) {
        $query->where('name', 'like', "%{$search}%");
    })
    ->get();

2.2 存在性查询

$users = DB::table('users')
    ->whereExists(function ($query) {
        $query->select(DB::raw(1))
            ->from('orders')
            ->whereColumn('orders.user_id', 'users.id');
    })
    ->get();

2.3 子查询

$latestPosts = DB::table('posts')
    ->select('user_id', DB::raw('MAX(created_at) as latest_post'))
    ->groupBy('user_id');

$users = DB::table('users')
    ->joinSub($latestPosts, 'latest_posts', function ($join) {
        $join->on('users.id', '=', 'latest_posts.user_id');
    })
    ->get();

2.4 联合查询

$first = DB::table('users')
    ->whereNull('first_name');

$users = DB::table('users')
    ->whereNull('last_name')
    ->union($first)
    ->get();

3. 向量查询

3.1 相似性搜索

$documents = DB::table('documents')
    ->whereVectorSimilarTo('embedding', $queryEmbedding)
    ->limit(10)
    ->get();

3.2 相似度分数

$documents = DB::table('documents')
    ->select('*')
    ->selectRaw('embedding <=> ? AS distance', [$queryEmbedding])
    ->orderBy('distance')
    ->limit(10)
    ->get();

3.3 余弦相似度

$documents = DB::table('documents')
    ->select('*')
    ->selectRaw('1 - (embedding <=> ?) AS similarity', [$queryEmbedding])
    ->orderByDesc('similarity')
    ->limit(10)
    ->get();

4. 排序与分页

4.1 排序

$users = DB::table('users')
    ->orderBy('name')
    ->orderByDesc('created_at')
    ->get();

// 随机排序
$users = DB::table('users')->inRandomOrder()->get();

4.2 分页

$users = DB::table('users')->paginate(15);

$users = DB::table('users')->simplePaginate(15);

$users = DB::table('users')->cursorPaginate(15);

4.3 游标分页

$users = DB::table('users')
    ->orderBy('id')
    ->cursorPaginate(15);

// 下一页
$nextPage = $users->nextCursor();

// 上一页
$prevPage = $users->previousCursor();

5. 插入与更新

5.1 插入

DB::table('users')->insert([
    'name' => 'John',
    'email' => 'john@example.com',
]);

DB::table('users')->insert([
    ['name' => 'John', 'email' => 'john@example.com'],
    ['name' => 'Jane', 'email' => 'jane@example.com'],
]);

$id = DB::table('users')->insertGetId([
    'name' => 'John',
    'email' => 'john@example.com',
]);

5.2 更新

DB::table('users')
    ->where('id', 1)
    ->update(['name' => 'John Doe']);

// 增量更新
DB::table('users')->increment('votes');
DB::table('users')->decrement('votes', 5);

5.3 Upsert

DB::table('users')->upsert([
    ['email' => 'john@example.com', 'name' => 'John'],
    ['email' => 'jane@example.com', 'name' => 'Jane'],
], ['email'], ['name']);

6. 删除

6.1 基本删除

1
2
3

DB::table('users')->where('id', 1)->delete();

DB::table('users')->truncate();

7. 事务

7.1 基本事务

DB::transaction(function () {
    DB::table('users')->update(['votes' => 0]);
    DB::table('posts')->delete();
});

7.2 手动事务

DB::beginTransaction();

try {
    DB::table('users')->update(['votes' => 0]);
    DB::table('posts')->delete();
    DB::commit();
} catch (\Exception $e) {
    DB::rollBack();
    throw $e;
}

8. 实战案例

8.1 复杂查询

class OrderReportService
{
    public function generate(Request $request)
    {
        return DB::table('orders')
            ->select([
                'users.name as customer',
                DB::raw('COUNT(*) as total_orders'),
                DB::raw('SUM(total) as total_amount'),
                DB::raw('AVG(total) as average_amount'),
            ])
            ->join('users', 'orders.user_id', '=', 'users.id')
            ->when($request->date_from, function ($query, $date) {
                $query->where('orders.created_at', '>=', $date);
            })
            ->when($request->date_to, function ($query, $date) {
                $query->where('orders.created_at', '<=', $date);
            })
            ->groupBy('users.id', 'users.name')
            ->having('total_orders', '>', 0)
            ->orderByDesc('total_amount')
            ->paginate(20);
    }
}

9. 最佳实践

9.1 查询优化

// 选择必要字段
$users = DB::table('users')->select(['id', 'name', 'email'])->get();

// 使用索引
$users = DB::table('users')->where('email', $email)->first();

// 避免 SELECT *
$users = DB::table('users')->select(['id', 'name'])->get();

9.2 分块处理

// 大数据量使用 chunk
DB::table('users')->chunk(1000, function ($users) {
    foreach ($users as $user) {
        // 处理用户
    }
});

// 或使用 cursor
foreach (DB::table('users')->cursor() as $user) {
    // 处理用户
}