Laravel 10中如何从查询结果生成含嵌套creator对象的JSON？

你期望返回的JSON结构如下：

{
        "title": "Nueva noticia",
        "category": "Deportes",
        "body": "Nueva noticia de deportes",
        "image_name": "Imagen",
        "publication_date": "2024-02-16 00:00:00",
        "id": 1,
        "creator":{
           "name": "Benjamin Camacho",
          "email": "benjamin.camacho@email.com"
         }
}

你尝试用SQL别名（比如creator::name、creator/email）来生成嵌套对象，但这种方式行不通——SQL查询返回的是扁平结果集，没法直接生成嵌套结构。下面提供两种可行的解决方法：

方法一：用集合map手动转换结构 #

使用查询构建器获取扁平结果后，通过map方法遍历集合，手动构建嵌套的creator对象：

$noticias = DB::table('blogs')
            ->leftJoin('category', 'blogs.category_id', '=', 'category.id')
            ->leftJoin('users', 'blogs.user_id', '=', 'users.id')
            ->select(
                'blogs.title',
                'category.name as category',
                'blogs.body',
                'blogs.image_name',
                'blogs.publication_date',
                'blogs.id',
                'users.name as creator_name',
                'users.email as creator_email'
            )
            ->get()
            ->map(function ($item) {
                return [
                    'title' => $item->title,
                    'category' => $item->category,
                    'body' => $item->body,
                    'image_name' => $item->image_name,
                    'publication_date' => $item->publication_date,
                    'id' => $item->id,
                    'creator' => [
                        'name' => $item->creator_name,
                        'email' => $item->creator_email
                    ]
                ];
            });

return $noticias;

方法二：使用Eloquent关联（推荐） #

如果你的项目使用Eloquent模型，先在Blog模型中定义关联关系：

// app/Models/Blog.php
public function creator()
{
    return $this->belongsTo(User::class, 'user_id');
}

public function category()
{
    return $this->belongsTo(Category::class);
}

然后通过with预加载关联数据，再整理成目标结构：

$noticias = Blog::with(['creator:id,name,email', 'category:id,name'])
            ->select('title', 'body', 'image_name', 'publication_date', 'id', 'category_id', 'user_id')
            ->get()
            ->map(function ($blog) {
                return [
                    'title' => $blog->title,
                    'category' => $blog->category->name,
                    'body' => $blog->body,
                    'image_name' => $blog->image_name,
                    'publication_date' => $blog->publication_date,
                    'id' => $blog->id,
                    'creator' => [
                        'name' => $blog->creator->name,
                        'email' => $blog->creator->email
                    ]
                ];
            });

return $noticias;

这种方式更符合Laravel的开发规范，代码可读性和可维护性更强。

内容的提问来源于stack exchange，提问作者Benjamín Camacho Castro