mysql - 3 个一对多表的嵌套 json 响应

Question

我正在按以下方式设计数据模型:

Table 1:
     id: number
    somedata: String
    somedata: String

Table 2:
    id: number
    Table1_id: number
    somedata: String
    somedata: String

Table 3:
    id: number
    Table2_id: number
    somedata: String

第一个表以一对多关系连接到第二个表。第二个表以一对多关系连接到第三个表。我使用的是java、JPA。我必须通过 REST API 以以下格式返回 JSON:

{
  "table1_id": 1,
  "somedata": "somedata",
  "table2_data": [
    {
      "table2_id": 1,
      "somedata": "somedata",
      "table3_data": [
        {
          "table3_id": 1,
          "somedata": "somedata"
        },
        {
          "table3_id": 2,
          "somedata": "somedata"
        }
      ]
    },
    {
      "table2_id": 2,
      "somedata": "somedata",
      "table3_data": [
        {
          "table3_id": 3,
          "somedata": "somedata"
        },
        {
          "table3_id": 4,
          "somedata": "somedata"
        }
      ]
    }
  ]
}

如果我使用 mysql 和上面的表结构，我将需要使用至少 3 个数据库调用来分别从 3 个表中获取数据，并且必须构建我的 json。有没有一种方法可以减少数据库调用并减少构建 json 的工作量？我也愿意更改数据库(mysql)。我只需要快速、优化和最佳的解决方案。请注意，table1、table2 和 table3 有不同的字段，因此我无法将它们非规范化为单个表。

抱歉，json 格式错误。无法弄清楚如何在此处显示它。

我已经看过这个问题，但我的问题与此不同:Nested JSON from 3 one-to-many Tables

编辑:我在这里得到答案:https://dba.stackexchange.com/questions/164370/nested-json-response-from-3-one-to-many-tables

Answer 1

我建议您首先对所有三个表执行连接，然后您可以根据您的要求操作数据库查询的结果(取决于您的选择语句)，因为我看到您创建了表的 json 对象。例如有三个表 Category Subcategory 和 Items 让我们看看我如何执行连接，然后根据要求获取 json 数据:-

    $query = "SELECT
items.id, items.category_id, items.sub_category_id, items.price_per_quantity, items.discounted_price, items.total_quantity, items.image, items.status, items.name as name, sub_categories.name as sub_category_name, categories.name as category_name
FROM items
    INNER JOIN sub_categories ON sub_categories.id = items.sub_category_id
    INNER JOIN categories ON categories.id  = items.category_id";