Laravel 8嵌套模型关系

Question

​

这是我的第一个Laravel项目，请容忍我们。我试图显示一个父->子层次结构(不是递归的)，如下所示：

我无法理解如何编写查询来获取链接到subStatement的资源，而不是顶级类别，基本上是与SubStatement相关的内部连接。它目前显示为这样；

    {
        "id": 1,
        "name": "Category 1: Practising plumbing overview",
        "description": "<p>Category 1: Practising plumbing overview</p>",
        "created_at": "2022-01-06 15:48:49",
        "updated_at": "2022-01-06 15:48:49",
        "deleted_at": null,
        "plumbing_area_id": 1,
        "category_id": 1,
        "SubStatement": [
            {
                "id": 1,
                "title": "Plying narrative for Category 1",
                "description": "<p>Body of content.</p>",
                "theme_id": 1,
                "plumbing_area_id": 1
            },
            {
                "id": 2,
                "title": "Lay narrative for Category 1",
                "description": "<p>body description.</p>",
                "theme_id": 1,
                "plumbing_area_id": 1
            }
        ],
        "resources": []
    },
    {
        "id": 2,
        "name": "Category 2: AAV Practising plumbing introduction",
        "description": "<p><strong>Category 2:</strong> AAV Practising plumbing introduction</p>",
        "created_at": "2022-01-06 15:49:12",
        "updated_at": "2022-01-06 15:49:12",
        "deleted_at": null,
        "plumbing_area_id": 1,
        "category_id": 1,
        "SubStatement": [
            {
                "id": 3,
                "title": "Plying narrative for Category 2",
                "description": "<p>Body of content;</p>",
                "theme_id": 2,
                "plumbing_area_id": 1
            },
            {
                "id": 4,
                "title": "Lay narrative for Category 2",
                "description": "<p>Body of content</p>",
                "theme_id": 2,
                "plumbing_area_id": 1
            }
        ],
        "resources": []
    },
    {
        "id": 3,
        "name": "Category 3: AAV Practising plumbing design",
        "description": "<p><strong>Category 3: </strong>AAV Practising plumbing design</p>",
        "created_at": "2022-01-06 15:49:47",
        "updated_at": "2022-01-06 15:49:47",
        "deleted_at": null,
        "plumbing_area_id": 1,
        "category_id": 1,
        "SubStatement": [
            {
                "id": 5,
                "title": "Plying narrative for Category 3",
                "description": "<p>Body of content;</p>",
                "theme_id": 3,
                "plumbing_area_id": 1
            },
            {
                "id": 6,
                "title": "Lay narrative for Category 3",
                "description": “Blah blah</p>",
                "theme_id": 3,
                "plumbing_area_id": 1
            }
        ],
        "resources": []
    },
    {
        "id": 4,
        "name": "Category 4: another “category,
        "description": “Body</p>",
        "created_at": "2022-01-06 15:50:04",
        "updated_at": "2022-01-06 15:50:04",
        "deleted_at": null,
        "plumbing_area_id": 1,
        "category_id": 1,
        "SubStatement": [
            {
                "id": 7,
                "title": "Plying narrative for Category 4",
                "description": "<p>sdfsdfsdf;</p>",
                "theme_id": 4,
                "plumbing_area_id": 1
            },
            {
                "id": 8,
                "title": "Lay narrative for Category 4",
                "description": "<p> sdfsdfsdfsdf</p>",
                "theme_id": 4,
                "plumbing_area_id": 1
            }
        ],
        "resources": []
    },
   ]

我正在寻找类似于下面的内容，请注意资源节点所在的位置：

[
  {
    "id": 1,
    "name": "Category 1: Practising plumbing overview",
    "description": "<p>Category 1: Practising plumbing overview</p>",
    "created_at": "2022-01-06 15:48:49",
    "updated_at": "2022-01-06 15:48:49",
    "deleted_at": null,
    "plumbing_area_id": 1,
    "category_id": 1,
    "SubStatement": [
      {
        "id": 1,
        "title": "Plying narrative for Category 1",
        "description": "<p>Body of content.</p>",
        "theme_id": 1,
        "plumbing_area_id": 1,
        "resources": [
          {
            "id": 1,
            "resource_title": "The name of the resource"
          }
        ]
      }
    ]
  }
]

//主题模型

{
   return $this->hasMany(Statement::class, 'theme_id', 'id');
}
public function resources()
{
 return $this->belongsToMany(Resource::class);
}

//下面的Controller输出上面的JSON

{
 $output = Theme::where ( 'category_id', '=', $category_id )
                    ->with(['subStatement' =>
                        fn ($query) =>
                        $query->select('id','title','description','theme_id','therapy_area_id')

                    ])
                    ->with(['resources' =>
                        fn ($query) =>
                        $query->select('id','temporary_url')])
                    ->get();
}

//我试图写这样的东西。它引发了一个“完整性约束违反:字段列表中的1052列'id‘是模棱两可的’错误‘”错误。

{
    return $this->hasOneThrough(Statement::class, Resource::class);
}

Answer 1

您的主要问题是需要嵌套查询。

• 我将首先根据您的模式向您的模型添加这些关系。

• ，如果我理解您试图正确地实现什么，我将添加嵌套的基本查询。

模型

Theme.php

public function subStatements()
{
    return $this->hasMany(Statement::class); // 1-N
}

public function statements()
{
    return $this->belongsToMany(Statement::class); // N-N
}

public function resources
{
    return $this->belongsToMany(Resource::class); // N-N
}

Statement.php

public function theme()
{
    return $this->belongsTo(Theme::class); // 1-N
}

public function themes()
{
    return $this->belongsToMany(Theme::class); // N-N
}

public function resources
{
    return $this->belongsToMany(Resource::class); // N-N
}

Resource.php

public function themes()
{
    return $this->belongsToMany(Theme::class); // N-N
}

public function statements()
{
    return $this->belongsToMany(Statement::class); // N-N
}

查询

$themes = Theme::query()
               ->where('category_id', $category_id)
               ->with([
                  'subStatements' => fn ($query) => $query->with('resources') 
               ])
               ->get();