如何使用Laravel Eloquent查询构造器执行指定数据库查询？

Hey there! Let's break down both of your questions clearly with practical examples since that's the best way to grasp how to work with queries in Laravel.

1. 使用Laravel Eloquent Query Builder实现指定查询 #

The Query Builder is Laravel's fluent, chainable tool for building SQL queries without writing raw SQL (though you can still drop in raw bits when needed). Let's use a real-world scenario: say you want to fetch active users created in the last 30 days who have at least one published post, sorted by creation date (newest first).

Here's how you'd build that with Query Builder:

$users = DB::table('users')
    // 基础条件：筛选活跃用户
    ->where('is_active', true)
    // 筛选30天内创建的用户
    ->where('created_at', '>=', now()->subDays(30))
    // 关联posts表
    ->join('posts', 'users.id', '=', 'posts.user_id')
    // 筛选已发布的帖子
    ->where('posts.status', 'published')
    // 指定返回字段，同时统计每个用户的已发布帖子数
    ->select('users.*', DB::raw('COUNT(posts.id) as post_count'))
    // 按用户ID分组
    ->groupBy('users.id')
    // 只保留至少有1篇帖子的用户
    ->having('post_count', '>=', 1)
    // 按创建时间倒序排序
    ->orderBy('users.created_at', 'desc')
    // 获取结果集
    ->get();

For more complex scenarios like subqueries, you can nest Query Builder instances too:

// 先构建子查询：获取7天内发布过帖子的用户ID
$recentPosterIds = DB::table('posts')
    ->where('created_at', '>=', now()->subDays(7))
    ->select('user_id');

// 用子查询筛选用户
$users = DB::table('users')
    ->whereIn('id', $recentPosterIds)
    ->get();

2. 以Laravel Eloquent的方式运行目标查询 #

Eloquent is an ORM (Object-Relational Mapper) that wraps the Query Builder, adding object-oriented features like model relationships, scopes, and events. It's the more "Laravel-native" way to work with your data when you have models set up.

First, make sure you have your models defined with relationships. For our example, here's the User model with a posts relationship:

// app/Models/User.php
namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class User extends Model
{
    // 定义与Post模型的一对多关联
    public function posts()
    {
        return $this->hasMany(Post::class);
    }
}

Now, let's re-implement the same query from earlier using Eloquent:

$users = User::where('is_active', true)
    ->where('created_at', '>=', now()->subDays(30))
    // 筛选存在已发布帖子的用户（替代join的关联筛选）
    ->whereHas('posts', function ($query) {
        $query->where('status', 'published');
    })
    // 统计每个用户的已发布帖子数，自动添加post_count字段
    ->withCount(['posts' => function ($query) {
        $query->where('status', 'published');
    }])
    ->orderBy('created_at', 'desc')
    ->get();

Eloquent also lets you clean up repeated query logic with local scopes. For example, add these scopes to your User model:

// 在User模型中添加
public function scopeActive($query)
{
    return $query->where('is_active', true);
}

public function scopeRecent($query, $days = 30)
{
    return $query->where('created_at', '>=', now()->subDays($days));
}

And if your Post model has a published scope:

// app/Models/Post.php
public function scopePublished($query)
{
    return $query->where('status', 'published');
}

Your query becomes much cleaner:

$users = User::active()
    ->recent()
    ->whereHas('posts', fn($q) => $q->published())
    ->withCount('publishedPosts') // 可以给关联起别名，需要在User模型中定义这个关联
    ->orderBy('created_at', 'desc')
    ->get();

内容的提问来源于stack exchange，提问作者Cody