php - 复杂报表需要结构最优的连接查询

Question

我正在为我的 SaaS 构建一个自定义报告模块，需要一些关于我正在构建的查询(MySQL 和 PHP)的建议。我正在动态构建一个表，返回的每条记录都将是该表上的一行。它动态地连接了许多不同的表，除了我需要连接的 3 个表外，它都很好用。

所有其他表都是 1-1 LEFT JOIN。例如，如果我正在查询员工表并尝试查找 department_id，它只会加入 hunky dory。它会像这样加入:

First | Last  | Dept
John  | Smith | Toy Department

让事情变得复杂的是那些额外的 3 个表有一对多连接。可能还需要指出的是，当我进行此连接时，它可能会提取 2-50 行数据中的任何位置。例如，如果我正在查询员工表并需要加入时钟输入/输出表，它最终看起来像这样:

First | Last  | Time
John  | Smith | 8:00 am
John  | Smith | 12:00 pm
John  | Smith | 1:00 pm
John  | Smith | 5:00 pm

如果我进行右连接，名字/姓氏将为空，但我仍然有多个行。

我想将它们排成一行的原因是因为在提取报告时我正在设置的表，当它遇到需要使用 3 个 1-many 表之一的表时，它需要留在同一个数据单元格中(这是我无法控制的，它只是必须发生):

<table>
    <tr>
        <td>John</td>
        <td>Smith</td>
        <td>8:00 am<br />12:00 pm<br />..... </td>
    </tr>
</table>

我真的真的真的很想避免必须点击那个单元格然后运行另一个查询来提取所有数据。原因是我可以运行包含 25 列以上的数千行的报告，并且它会运行 25x1000 行的相同查询。

当数据返回时，我所希望的是这样的:

First | Last  | Time_1  | Time_2   | Time_3  | Time_4
John  | Smith | 8:00 am | 12:00 pm | 1:00 pm | 5:00 pm

我的一些想法是在 PHP 中运行 while 循环并为 MySQL 动态构建连接，但我遇到了一个问题，即我需要提供连接表的次数才能获得那个栏目吧。我在技术上可以做到:

for($i = 1; $i < 50; ++$i) {
    $sql_fields .= "time_clock_{$i}.time AS Time_{$i},";
    $joins .= "LEFT JOIN time_clock AS time_clock_{$i} ON time_clock_{$i}.employee_id = emp.id";
}
$sql_fields = rtrim($sql_fields,',');

mysql_query("SELECT emp.field_blah, emp.field_blah2, {$sql_fields} FROM employees AS emp {$joins} WHERE condition = blah");

我只是担心这真的会导致 MySQL 出现一些性能问题以及我在其中进行的所有疯狂连接。有人有更好的主意吗？在这一点上我会招待任何东西。

Answer 1

没有。

构建两个查询。一个用所有1:1的元素来拉动所有的人；然后另一个拉你所有的时间。然后在 PHP 中循环遍历时间，将它们填充到人的时间数组中。