SQL to JSON、ORM for PHP 7和Laravel

Question

我正在开发一个REST API，在这个API中我必须查询信息并返回适当的JSON对象。我正在寻找PHP/Laravel库，可以轻松地完成这项工作。

带有表连接的普通SQL查询会给出多行信息

SQL

    SELECT H.fname AS `fname`,
    H.sname AS `sname`,
    S.id AS `students.id`,
    A.topic AS `students.tasks.topic`,
    A.wordcount AS `students.tasks.wordcount`
    FROM `humans` AS `H`
    LEFT JOIN `students` AS `S` ON H.id = S.human
    LEFT JOIN `courses` AS `C` ON S.course = C.id
    LEFT JOIN `tasks` AS `T` ON T.student = S.id
    LEFT JOIN `assignments` AS `A` ON T.assignment = A.id
    WHERE H.email = "user@email.com"

以JSON编码的响应行：

[
    {
        "fname": "Donnie",
        "sname": "Ashok",
        "students.id": "987152e7b3056f1b96c964614b0b6328",
        "students.tasks.topic": "How to dismantle an Anti-matter injector manifold? ",
        "students.tasks.wordcount": 1500
    },
    {
        "fname": "Donnie",
        "sname": "Ashok",
        "students.id": "987152e7b3056f1b96c964614b0b6328",
        "students.tasks.topic": "How to intercept sub-space transmission using Dilithium Matrices? ",
        "students.tasks.wordcount": 1500
    },
    {
        "fname": "Donnie",
        "sname": "Ashok",
        "students.id": "3ea4f7b2e1e4bcc26ae544dd14d107fe",
        "students.tasks.topic": null,
        "students.tasks.wordcount": null
    }
]

然而，我需要一个像这样的合适的JSON对象：

[
    {
        "fname": "Donnie",
        "sname": "Ashok",
        "students": [
            {
                "id": "987152e7b3056f1b96c964614b0b6328",
                "tasks": [
                    {
                        "topic": "How to intercept sub-space transmission using Dilithium Matrices? ",
                        "wordcount": 1500
                    },
                    {
                        "topic": "How to dismantle an Anti-matter injector manifold?",
                        "wordcount": 1500
                    }
                ]
            },
            {
                "id": "3ea4f7b2e1e4bcc26ae544dd14d107fe",
                "tasks": []
            }
        ]
    }
]

有没有什么PHP库或ORM可以用来组织SQL行？

Answer 1

我将JSON API规范用于API，并且我认为它很好用。这是一个常用的规范。要实现它，可以使用nilportugues/laravel5-jsonapi包。

也可以看看GraphQL。它是一种API的查询语言。Folkloreatelier/laravel-graphql是一个用于实现GraphQL的包。

这两种方法都将使您的API数据格式清晰。

JsonAPI示例数据

{
  "data": {
    "type": "articles",
    "id": "1",
    "attributes": {
      "title": "To TDD or Not"
    }
  }
}

GraphQL示例数据

{
  "data": {
    "human": {
      "name": "Luke Skywalker",
      "height": 1.72
    }
  }
}

正如其他人在评论中提到的那样，php联盟的fractal也是一个很好的选择。

希望这能有所帮助