来自3个一对多表的嵌套JSON [英] Nested JSON from 3 one-to-many Tables

问题描述

我正在构建一个Sencha-Touch 2应用程序，从服务器端(mysql DB)恢复数据时遇到了一些麻烦.

I'm building a Sencha-Touch 2 app and I have some trouble with the recuperation of my data from server side (mysql DB).

这是我的数据模型:

Table1 : 
     ID:int
     description:varchar(100)

Table2 : 
     ID:int
     description:varchar(100)
     table1_ID:int

Table3 : 
     ID:int
     name:varchar(100)
     info:varchar(100)
     table2_ID:int

表1以一对多关系连接到表2，并且在表2和表3之间相同.

Table1 is join to Table2 with a one-to-many relationship and same between Table2 and Table3.

我要从服务器获取的是一个嵌套的JSON，它看起来像这样:

What I want from server is a nested JSON who looks like this :

[
   Table1_object1_ID: 'id' : {
      Table1_object1_description: 'description',
      Table2_Objects : [
            'Table2_object1': {
                Table2_object1_id : 'id',
                Table2_object1_description : 'description'
                Table3_Objects : [
                      table3_object1: {
                          Table3_object1_name : 'name',
                          Table3_object1_info : 'info',
                      },
                      table3_object2: {
                          Table3_object2_name : 'name',
                          Table3_object2_info : 'info',
                      },
                      table3_object3: {
                          Table3_object3_name : 'name',
                          Table3_object3_info : 'info',
                      },
                      etc...
                ],

            },
            'Table2_object2': {
                Table2_object2_id : 'id',
                Table2_object2_description : 'description'
                Table3_Objects : [
                      ...
                ]
            },
            etc....
      ]
   },
   Table1_object2_ID: 'id' : {
      etc....
]

在我的App中，我为每个表使用3个模型，理想情况下，我想将数据保存在3个商店中，但这将是另一个问题;-)

In my App, I use 3 Models for each Table, and ideally I want to save my data in 3 Stores, but that will be an other problem ;-)

第一个商店(基于Table1中的模型)执行JsonP请求以获取嵌套JSON.

The first Store (based on the Model from Table1) do a JsonP request to get the Nested JSON.

实际上，我在PHP文件中的SQL请求很简单:

Actually my SQL request in the PHP file is simple :

SELECT * 
FROM Table1 
INNER JOIN Table2 ON Table1.ID = Table2.table1_ID 
INNER JOIN Table3 ON Table2.ID = Table3.table2_ID;

我试图根据SQL结果在PHP中创建一个数组，但无法获得预期的结果. 我还尝试使用GROUP BY和GROUP_CONCAT更改我的SQL，但是在这里相同，无法获取我想要的JSON.

I tried to make an array in PHP from my SQL results but cannot get the expect result. I also try to change my SQL with GROUP BY and GROUP_CONCAT but same here, cannot get the JSON I want.

一些帮助将不胜感激.

推荐答案

带有一些示例数据的可运行代码: http://codepad .org/2Xsbdu23

Runnable code with some sample data: http://codepad.org/2Xsbdu23

我使用了3个不同的SELECT来避免不必要的重复. 当然，您必须自定义$result数组以完全符合所需的JSON格式，但是我认为这并不难.

I used 3 distinct SELECTs to avoid the unnecessary repetitions. Of course you have to customize the $result array to your exact desired JSON format, but I think it is not that hard.

// assume $t1/2/3 will be arrays of objects
$t1 = 
SELECT     Table1.*
FROM       Table1 
WHERE Table1.ID = 111

$t2 = 
SELECT     Table2.*
FROM       Table2
WHERE      Table2.table1_ID = 111

$t3 = 
SELECT     Table3.*
FROM       Table2
INNER JOIN Table3 ON Table2.ID = Table3.table2_ID
WHERE      Table2.table1_ID = 111

function array_group_by( $array, $id ){
  $groups = array();
  foreach( $array as $row ) $groups[ $row -> $id ][] = $row;
  return $groups;
}

// group rows from table2/table3 by their parent IDs
$p2 = array_group_by( $t2, 'table1_ID' );
$p3 = array_group_by( $t3, 'table2_ID' );

// let's combine results:
$result = array();
foreach( $t1 as $row1 ){
  $row1 -> Table2_Objects = isset( $p2[ $row1 -> ID ]) ? $p2[ $row1 -> ID ] : array();
  foreach( $row1 -> Table2_Objects as $row2 )
    $row2 -> Table3_Objects = isset( $p3[ $row2 -> ID ]) ? $p3[ $row2 -> ID ] : array();
  $result[] = $row1;
}

echo json_encode( $result );

这篇关于来自3个一对多表的嵌套JSON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！