MySQL 自定义报告策略
我们有一个 PHP/MySQL 应用程序，用户可以在其中创建自己的“数据库”，这可能会变得相当复杂。在 MySQL 上，它的结构为一组属性表，其中包含 record_id、field_id 和 field_value 等列。我很清楚这种结构的优点和缺点，所以请考虑这不会改变。
我们已经拥有一个自定义的高级搜索系统，用户可以通过组合任何字段的条件、在字段之间和字段内使用布尔运算符来查询这些数据库。
现在我们必须实现一个报告功能，让他们能够制作自己的报告。这可能包括一对多关系的多组数据。
例如，考虑一个“人员”数据库，其中每个人可能有多个联系人和多个地址。例如，通过我们的自定义搜索，可以轻松列出所有在美国出生的人。但我还想在结果集中显示每个人的所有联系人和地址。
我可以看到两种可能的解决方案：
使用多个
JOIN语句的单个 SQL 查询来获取所有数据。在这种情况下，每个人可能有多行，具体取决于他们有多少联系人和地址，并且我必须在显示（或组织）结果的 PHP 循环上处理该问题。考虑到报告可能有无限数量的一对多信息块（在我的示例中，我只有两个，地址和联系人），恐怕这会变得太复杂。对于每个人，运行“n”个附加查询，每个查询对应一对多数据的每个块。在我的示例中，这将是一个针对联系人的查询，另一个针对地址的查询。这种方法可能会导致运行大量查询来构建整个报告。
我知道这两种方法都有其缺点，但是在这种情况下是否有“推荐”的方法呢？
We have an PHP/MySQL application where the users can create their own "databases", which can become quite complex. On MySQL, it's structured as a set of property tables, with columns like record_id, field_id and field_value. I'm well aware of the pros and cons of such structure, so please consider this is not changing.
We already have a custom advanced search system where users can query those databases by combining criteria from any field, using boolean operators between and within fields.
Now we have to implement a reporting feature where they'll be able to craft their own reports. This might include multiple sets of data on a one to many relationship.
For example, consider a "People" database where each person may have multiple contacts and multiple addresses. With our custom search, it's easy to list all people born in the US, for example. But I also want to display all contacts and addresses for each person on the resultset.
I can see two possible solutions:
A single SQL query with multiple
JOINstatements to fetch all data. In this case I may have multiple rows for each person, depending on how many contacts and addresses they have, and I'd have to deal with that on the PHP loop that displays (or organizes) the results. I'm afraid this can get too complex, considering a report may have an unlimited number of one to many blocks of information (on my example, I have only two, addresses and contacts).For each person, run 'n' additional queries, one for each block of one to many data. On my example, that would be one query for the contacts, and another one for the addresses. That approach can lead to a huge number of queries to be run to build the whole report.
I know both approaches have their downsides, but is there a "recommended" way to go on situations like that?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
这当然取决于您认为检索所有数据所需的查询量。从性能的角度来看，建议执行大型联接，因为它的处理效率比多个小型查询要高得多。
您会发现使用第二种方法生成大型报告比使用第一种方法花费的时间更长。
This certainly depends on the amount of queries you think you will have to make to retrieve all the data. From a performance standpoint it is recommended to perform the large join as it can be processed far more efficient than multiple small queries.
You will find that large reports will take longer using your second approach than using your first approach.