php - 使用 PHP/MySQL 构建更多维的 JSON 对象

Question

所以，首先 - 我的目标是构建一个 SQL 查询来输出以下 JSON 对象。正如您所看到的，它会显示一些基本的竞赛详细信息，然后显示与该竞赛相关的每个用户。此外，它还为每个用户显示他们已执行的事件。

{
        organisationId: 1,
        competitionId: "52eabcf0f3672",
        title: "Sales Hood Q1 Challenge",
        end_date: "2014-03-01 00:00:00",
        description: "This is it guys, challenge time!",
        prizeImage: "placeholder.jpg",
        prizeDescription: "Dinner for 2!",
        users: [{
            id: 2,
            name: "Jane Wilson",
            isAdmin: true,
            direction: "down",
            profilePic: "fighter-1.jpg",
            tagline: "My shit is consistently on fire",
            totalPoints: 40,
            isOnStreak: false,
            activities: [{ 
                id: 6431,
                time: (57).minutes().ago(),
                points: 20
            }, {
                id: 6431,
                time: new Date(),
                points: 20
            }]
        }, {
            id: 3,
            name: "Caroline Wilson",
            isAdmin: false,
            direction: "up",
            profilePic: "fighter-3.jpg",
            tagline: "I am the best",
            totalPoints: 60,
            isOnStreak: false,
            activities: [{ 
                id: 6431,
                time: (1).days().ago,
                points: 20
            }, {
                id: 6431,
                time: (2).days().ago,
                points: 20
            }, {
                id: 6431,
                time: new Date(),
                points: 20
            }]
        }, {
            id: 1,
            name: "Matthew Lloyd",
            isAdmin: false,
            direction: "down",
            profilePic: "placeholder.jpg",
            tagline: "Aref to the rescue!",
            totalPoints: 140,
            isOnStreak: false,
            activities: [{ 
                id: 6431,
                time: new Date(),
                points: 20
            }, {
                id: 6431,
                time: new Date(),
                points: 20
            }, {
                id: 6432,
                time: new Date(),
                points: 100
            }]
        }]
    };

我有以下 SQL 模式(可以在这个 fiddle http://sqlfiddle.com/#!2/82d6e/1 中找到)，并且我似乎无法构建一个查询来提供我想要的内容。

为了澄清，数据库中有以下表格

1. 比赛
2. 用户
3. competitionmembers -> 这是受邀参加比赛的用户
4. activity_types -> 与该竞赛相关的事件
5. activity_entries -> 用户实际“执行”的事件

就 SQL/PHP 查询而言，这就是我目前所处的位置 - 但似乎无法做到正确。

 $get = mysql_query("SELECT c.organisationId, c.competitionId, c.name, c.end_date, c.about, c.prizeImage, c.prize, u.name AS userName, u.id AS userId, u.profilePic, u.tagline
FROM competitions c
INNER JOIN users1 u ON c.organisationId = u.organisationId 
INNER JOIN competitionmembers m ON m.userid = u.id
WHERE c.competitionId = '52a99783c5d6f'") or die("Couldn't select competition details");


$arr = array();

while ($row = mysql_fetch_array($get)) {

    $arr = array(
        array(
            "competitionId" => $row["competitionId"],
            "title" => $row["name"],
        ),
        array(
            "id" => $row["userId"],
            "name" => $row["userName"],
        )
    );
}

echo json_encode($arr);

?>

希望得到一些帮助，如果你能更接近那个 json 对象，那就太好了!

Answer 1

您应该进行更多查询:

• 查询1 = 首先获取比赛数据
• 查询2 = 获取与竞争相关的所有用户
• 查询3 = 获取用户相关事件(收集用户 key )

我确信这会更快、更容易管理。如果您尝试仅使用一个查询，则会有很多重复数据，并且查询会变得非常慢。

获得数据后，将其保存在数组中，并使用 in_array、array_key_exists 等函数将所有数据组合在一起。

伪代码:

$competition = query1;

$users = query(get users in competition);

$competition["users"] = $users;

$user_ids = array();

foreach($users as $user)
  $user_ids[] = $user['id'];

$activities = query("select * from activities where id_user in (" . implode(",", $user_ids)) . ")";

for ($i = 0; $i < count($competition["users"]); $i++) {

  $competition["users"][$i]["activities"] = array(); 

  foreach($activities as $act) {

    if ($competition["users"][$i]['id'] == $act["id_user"]) {

       // Add activity..
       $competition["users"][$i]["activities"][] = $act;

    }
  }
}

echo json_encode($competition);