来自3个一对多表的嵌套JSON

本文介绍了来自3个一对多表的嵌套JSON的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在构建一个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.

一些帮助将不胜感激.

推荐答案

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

Runnable code with some sample data: codepad/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 );