基于网络的特定领域查询生成器的模式或想法（不适用于报告）？
也许这是一个黑暗中的尝试，但我试图找出是否有人对我们遇到的这个问题有想法。
情况是我们有一个数据库，其中包含大量项目的各种数据。有几十个表都以 1 对 1 的方式提供有关项目的支持信息，其中有关项目的某些特定类型的信息（例如 ProjectInfoTypeA）可能存储在名为 ProjectInfoTypeA 的表中，我们将执行内部联接在该表和项目表之间，以及一对多，例如 ProjectScopeKeywords，其中可以为项目分配 N 个属性，或者在本例中为多个不同属性/查找表分配“关键字”。
最后，我们需要让我们的网络应用程序中的用户构建如下内容： 显示过去 5 年完成的所有项目，这些项目至少需要 4 年时间才能完成，成本至少为 1MM，并且所有 3 个关键字 ( x,y,z ) 都与其关联。
我们还希望用户能够保存他们的查询，以便他们和其他用户可以从保存的查询列表中选择它们。
一旦我们从过滤器中获得了项目列表，我们就需要以各种不同的方式使用它：但不是作为报告。如果这是一份报告，我只会给他们一些某种类型的报告生成器，但我们需要在网络应用程序中使用他们的过滤列表。
目前我们正在考虑两种不同的想法： 1）我们只是尝试编写自己的 UI 来构建查询，然后创建一些巨大的 SQL 语句。
2）我们将有关每个过滤器的数据存储在数据库中，然后当他们巧妙地“搜索”时，我们会根据数据迭代地删除与每个查询不匹配的项目，从而实质上修剪项目列表它们存储在数据库中。
我猜没有人必须处理这样的事情，但如果你们中有人遇到过，我有兴趣听到任何值得研究的建议/模式。
Maybe this is a shot in the dark here but I'm trying to find out if anyone has thoughts on this problem we have been presented with.
The situation is that we have a database that contains all kinds of data about a large list of projects. There are dozens of tables that all provide supporting info about a project, both in 1 to 1 manner, where some specific type of info about projects (say ProjectInfoTypeA) might be stored in a table called ProjectInfoTypeA, and we'd do a inner join between that and the projects table, as well as 1 to many, like maybe ProjectScopeKeywords, where a project can be assigned N attributes or in this case "keywords" for a number of different attribute/lookup tables.
In the end we need to have the user in our web app build up things like:
Show me all projects completed in the last 5 years that took at least 4 years to do, cost at least $1MM, and have all 3 of these keywords ( x,y,z ) associated with it.
We also want users to be able to save their queries so they, and other users, can select them from a list of saved queries.
Once we get the list of projects from their filter, we need to then work with it in all different ways: but not as a report. If this were a report I'd just give them some report builder of some kind, but we need to work with their filtered list in the web app.
Currently we are thinking of 2 different ideas:
1) being that we just try to write our own UI for building up the query, and then create some giant SQL statement.
2) we store the data about each of their filters in the database, and then when they slick "Search" we would essentially prune down the list of projects by iteratively stripping off the projects that didn't match each query, based on the data they stored in the database.
I'm guessing no one out there has had to deal with something like this, but if any of you had, I'd be interested to hear any suggestions/patterns that would be worth looking into.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我建议选择选项 1。我已经在许多项目中使用了查询构建器方法，根据需求的复杂程度，其复杂程度也有所不同。
如果您能够使用现成的解决方案，您可以在网上找到几个：http://www.google.com/search?q=sql+query+builder
对于自定义构建的解决方案，您可能至少希望提供扁平视图以供用户查询;这将简化设计人员的复杂性，减少用户的学习曲线，并为未来的模式更改提供一些抽象。
定义基础数据源后，您需要提供用户可以选择特定列、定义过滤条件、指定值聚合和定义子查询（基于示例查询要求）的方法。列选择和过滤器定义应该不会太困难，但值聚合和子查询创建的定义并不简单。您应该能够使用现成的解决方案作为如何向用户展示此功能的示例。
I would recommend choosing option 1. I have used a query-builder approach on a number of projects, with varying degrees of sophistication depending on the complexity of the requirements.
If you are in a position to use a ready-made solution, you can find several on the web: http://www.google.com/search?q=sql+query+builder
For a custom built solution, at a minimum, you would probably want to provide flattened views for the user to query from; this will simplify the designer complexity, reduce the learning curve for the user, and provide some abstraction against future schema changes.
After defining your base data sources, you need to provide means by which the user can select specific columns, define filter criteria, specify value aggregation, and define sub-queries (based on your example query requirement). The column selection and filter definition should not be too difficult, but the value aggregation and sub-query creation would be non-trivial to define. You should be able to use the ready-made solutions as examples of how to present this functionality to the user.