Laravel子查询？子查询如何构建使用？

whereExists()

groupBy

distinct

EXISTS

// 查找至少有一篇已发布文章的用户
$usersWithPublishedPosts = User::whereExists(function ($query) {
    $query->select(DB::raw(1)) // 只需要检查是否存在，所以选择一个常量即可
          ->from('posts')
          ->whereColumn('posts.user_id', 'users.id') // 关联条件
          ->where('posts.status', 'published');
})->get();

// 或者，更简洁的写法，Laravel会自动处理关联
$usersWithPublishedPosts = User::whereHas('posts', function ($query) {
    $query->where('status', 'published');
})->get();
// 尽管whereHas在底层可能生成JOIN，但在某些情况下，它也可以优化为EXISTS
// 但如果你需要更细粒度的控制，直接用whereExists也是很好的选择。

selectSub()

selectSub()

// 查询所有用户及其最新订单日期
$usersWithLatestOrderDate = User::select('id', 'name')
    ->selectSub(function ($query) {
        $query->select('created_at')
              ->from('orders')
              ->whereColumn('user_id', 'users.id')
              ->latest() // 获取最新的订单
              ->limit(1); // 只需要一条记录
    }, 'latest_order_date') // 将子查询结果命名为latest_order_date
    ->get();

fromSub()

// 假设我们想找出每个用户最近10笔订单的平均金额
$averageOrderAmounts = DB::table(function ($query) {
    $query->select('user_id', 'amount')
          ->from('orders')
          ->orderBy('created_at', 'desc')
          ->limit(10); // 这里需要注意，limit在子查询中可能需要配合group by才能达到预期
}, 'recent_orders') // 将子查询结果命名为recent_orders
->select('user_id', DB::raw('AVG(amount) as avg_amount'))
->groupBy('user_id')
->get();

// 更实际的场景，通常会结合窗口函数来处理“最近N条”的问题
// 比如，找出每个用户最新的订单金额
$usersWithLatestOrderAmount = DB::table(function ($query) {
    $query->select('user_id', 'amount', DB::raw('ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) as rn'))
          ->from('orders');
}, 'ranked_orders')
->where('rn', 1)
->select('user_id', 'amount as latest_order_amount')
->get();

whereIn()

// 查找所有购买过“Laravel课程”的用户
$usersWhoBoughtLaravelCourse = User::whereIn('id', function ($query) {
    $query->select('user_id')
          ->from('orders')
          ->where('product_name', 'Laravel课程');
})->get();

selectSub

GROUP BY

whereExists

LEFT JOIN ... WHERE ... IS NOT NULL

selectSub()

// 获取每个部门的员工，并显示部门平均工资和员工在部门内的薪资排名
$employeesData = Employee::select('id', 'name', 'department_id', 'salary')
    ->selectSub(function ($query) {
        $query->select(DB::raw('AVG(salary)'))
              ->from('employees')
              ->whereColumn('department_id', 'employees.department_id');
    }, 'department_avg_salary')
    ->selectSub(function ($query) {
        $query->select(DB::raw('COUNT(id) + 1')) // 简单模拟排名，实际生产会用窗口函数
              ->from('employees as e2')
              ->whereColumn('e2.department_id', 'employees.department_id')
              ->whereColumn('e2.salary', '>', 'employees.salary');
    }, 'salary_rank_in_department')
    ->get();

RANK()

ROW_NUMBER()

DB::raw()

// 假设有一个必修课程列表
$requiredCourseIds = Course::where('is_required', true)->pluck('id');

// 找出所有购买了所有必修课程的用户
$usersWhoBoughtAllRequiredCourses = User::whereHas('orders', function ($query) use ($requiredCourseIds) {
    // 这里的子查询是检查用户订单中是否包含所有必修课程
    // 这是一个比较复杂的逻辑，需要确保用户购买的课程数量等于必修课程的总数，
    // 并且这些课程都在必修课程列表中
    $query->select(DB::raw('COUNT(DISTINCT course_id)'))
          ->whereIn('course_id', $requiredCourseIds)
          ->groupBy('user_id')
          ->havingRaw('COUNT(DISTINCT course_id) = ?', [count($requiredCourseIds)]);
}, '>=', 1) // 确保至少有一个这样的用户记录，但这里逻辑可能需要调整
->get();

// 更准确的做法可能是这样：
$users = User::all()->filter(function ($user) use ($requiredCourseIds) {
    $userCourseIds = $user->orders->pluck('course_id')->unique();
    return $requiredCourseIds->every(fn ($rcId) => $userCourseIds->contains($rcId));
});
// 这种在应用层过滤的方式在数据量大时效率不高。
// 纯SQL实现通常会更复杂，需要多个JOIN和GROUP BY或者EXCEPT/MINUS操作。
// 子查询可以作为构建复杂SQL逻辑的中间步骤。

// 另一种思路：找出所有用户，然后筛选出那些“没有”购买某个必修课程的用户，再取反。
// 这通常涉及 NOT EXISTS 或 LEFT JOIN ... IS NULL。
$users = User::whereDoesntHave('orders', function ($query) use ($requiredCourseIds) {
    $query->whereIn('course_id', $requiredCourseIds);
})->get(); // 这只会找出没有购买任何必修课程的用户，不是我们想要的。

// 这是一个典型的需要更复杂SQL或多个子查询来解决的问题，通常会结合 EXISTS 和 NOT EXISTS
// 找出所有购买了所有必修课程的用户
$users = User::whereExists(function ($query) use ($requiredCourseIds) {
    $query->select(DB::raw(1))
          ->from(DB::raw('(SELECT course_id FROM courses WHERE is_required = true) as required_courses'))
          ->whereNotIn('course_id', function ($subQuery) {
              $subQuery->select('course_id')
                       ->from('orders')
                       ->whereColumn('user_id', 'users.id');
          });
})->get();
// 这里的逻辑是：找出那些存在“必修课程”但该用户“没有”购买的必修课程的用户，然后用 NOT EXISTS 包裹。
// 也就是说，如果“不存在”任何一个用户没有购买的必修课程，那么这个用户就购买了所有必修课程。
// 这种双重否定在SQL中很常见，但理解起来需要一点时间。

explain()

toSql()

EXPLAIN

EXPLAIN ANALYZE

// 查看生成的SQL
$query = User::whereExists(function ($query) {
    $query->select(DB::raw(1))
          ->from('posts')
          ->whereColumn('posts.user_id', 'users.id')
          ->where('posts.status', 'published');
});
dump($query->toSql()); // 复制SQL到数据库客户端执行 EXPLAIN

WHERE

ON

whereExists

whereIn

selectSub

WHERE IN (SUBQUERY)

WHERE IN

WHERE IN (SUBQUERY)

INNER JOIN

// 效率可能不高，如果子查询返回大量ID
$users = User::whereIn('id', function ($query) {
    $query->select('user_id')->from('large_orders_table')->where('amount', '>', 1000);
})->get();

// 优化为JOIN
$users = User::join('large_orders_table as lot', 'users.id', '=', 'lot.user_id')
             ->where('lot.amount', '>', 1000)
             ->select('users.*') // 选择用户表的所有列，避免重复
             ->distinct() // 如果一个用户有多笔大额订单，需要去重
             ->get();

DB::raw()