时间戳

首页 > 解决方案 > 如何编写 MySQL 查询以使用 JOINS 获取嵌套的 JSON 数组？

mysql - 如何编写 MySQL 查询以使用 JOINS 获取嵌套的 JSON 数组？

问题描述

我正在做一个类似项目的计划，我必须从 mysqli 数据库中以特定的 JSON 格式加载所有信息。（否则他们无法更改）

所以格式应该是这样的：

{  
   "data":[  
      {  
         "id_profile":"66",
         "nom":"BARRAUD ",
         "groupe":"1",
         "tasks":[  
            {  
               "id_counter":"8",
               "id_profile":"66",
               "from_interval":"1528092000",
               "to_interval":"1528138800",
               "counter":"16",
               "hrname":"EC2"
            },
            {  
               "id_counter":"458",
               "id_profile":"66",
               "from_interval":"1532412000",
               "to_interval":"1532458800",
               "counter":"16",
               "hrname":"EC1"
            },
            {  
               "id_counter":"466",
               "id_profile":"66",
               "from_interval":"1532757600",
               "to_interval":"1532804400",
               "counter":"14",
               "hrname":"EC1"
            }
         ]
      }
]}

这是他们使用的原始代码：

<?php

if (isset($_GET['counter'])) {
    require_once '../functions.php';

    $sDateHour = $_GET['sDateHour'];
    $eDateHour = $_GET['eDateHour'];

    $now = time() - 24 * 60 * 60;
    $fn = new Func();
    $fn->select('id_profile, nom, groupe', 'profile', '', " date_f_contract>='$now' AND groupe>0 ORDER BY nom");

    $sel = new Func();
    $getHrName = new Func();

    while ($row = mysqli_fetch_assoc($fn->select)) {
        $pr_id = $row['id_profile'];

        $sel->select('*', 'counter', '', " from_interval>='$sDateHour' AND to_interval<='$eDateHour' AND id_profile='$pr_id'");
        $nr = mysqli_num_rows($sel->select);

        if ($nr > 0) {
            //here we have to do the total counter
            $totalCounter = 0;
            $from_interval = 0;
            $to_interval = 0;
            $name = "";

            $tmp_id_profile = 0;
            $tmp_f_int = 0;
            $tmp_to_int = 0;

            while ($r = mysqli_fetch_assoc($sel->select)) {
                $frint = $r['from_interval'];
                $toint = $r['to_interval'];

                $counter_ct = $r['counter'];

                $totalCounter += $counter_ct;

                $getHrName->select('libelle AS lbl, COUNT(libelle) AS totalHr', "horraire", "", " date_heure_deb>='$frint' AND date_heure_fin<='$toint' AND id_profile='$pr_id' GROUP BY libelle ORDER BY totalHr DESC LIMIT 1");
                $rr = mysqli_fetch_assoc($getHrName->select);
                $r['hrname'] = $rr['lbl'];
                $name = $rr['lbl'];

                $r['counter'] = $rr['totalHr'];

                $row['tasks'][] = $r;
            }
        }
        $res[] = $row;
    }

    $temp['data'] = $res;
    $fn->deconnect();
    $sel->deconnect();

    echo json_encode($temp, JSON_PRETTY_PRINT);
}

但这对于加载数据来说太长了，我正在考虑使用联接的机会？在这种情况下可以使用任何 JOINS 吗？那会给我更快的结果吗？

我一直在尝试将查询更改为：

SELECT pr.id_profile, pr.nom, pr.groupe, ct.* FROM profile AS pr 
INNER JOIN counter AS ct ON pr.id_profile = ct.id_profile WHERE
(ct.from_interval>='$sDateHour' AND ct.to_interval<='$eDateHour') AND
pr.date_f_contract>='$now' AND groupe>0 ORDER BY nom

标签： mysqlmysql-error-1064

解决方案

一般来说，是的。连接总是比单独的查询好，而且更容易阅读。下次请以更好的格式发布代码。到目前为止，真的很难读懂那件事。如果我没看错，这应该可以工作，即使你真的应该从 mysqli 切换到 PDO：

SELECT
            `profile`.id_profile,
            `profile`.nom,
            `profile`.groupe,

            counter.from_interval,
            counter.to_interval,
            counter.counter,

            libelle AS lbl,
            COUNT(libelle) AS totalHr
FROM        `profile`
INNER JOIN  counter
    ON      counter.id_profile = `profile`.id_profile
INNER JOIN  horraire
    ON      horraire.id_profile = `profile`.id_profile
            AND horraire.date_heure_deb >= counter.from_interval
            AND horraire.date_heure_fin <= counter.to_interval
WHERE       `profile`.date_f_contract >= CURDATE() AND `profile`.groupe > 0
            AND from_interval >= '$sDateHour' AND to_interval <= '$eDateHour'
GROUP BY    libelle
ORDER BY    totalHr DESC;

然后您可以在 PHP 中完成其余的工作，或者（如果您使用的是 MySQL +5.7 或 MariaDB 10.3）您可以使用数据库本身的 JSON 函数。

推荐阅读