Templates or ideas for a web query builder (not for reporting)?

Question

Perhaps this is a shot in the dark here, but I'm trying to find out if anyone has any thoughts on this issue that were presented to us.

The situation is that we have a database that contains all kinds of data on a large list of projects. There are dozens of tables that provide supporting information about the project, both from 1 to 1, and with some specific types of project information (for example, ProjectInfoTypeA) can be stored in a table called ProjectInfoTypeA, and we will do an internal join between them and the project table , as well as from 1 to many, for example ProjectScopeKeywords, where N attributes can be assigned to the project, or in this case “keywords” for several different attribute / search tables.

In the end, we need the user to create things in our web application such as: Show me that all projects completed in the last 5 years, which took at least 4 years, cost at least $ 1 million and have all three of these keywords (x, y, z) associated with it.

We also want users to be able to save their requests, so that they and other users can select them from the list of saved requests.

As soon as we get the list of projects from their filter, we need to work with it in different ways: but not as a report. If it was a report, I would just give them some report designer, but we need to work with their filtered list in a web application.

We are currently thinking of two different ideas: 1) is that we are just trying to write our own user interface to create a query, and then create some giant SQL statement.

2) we save the data about each of our filters in the database, and then, when they are the “Search” spot, we will significantly reduce the list of projects by iteratively canceling projects that did not match each request, based on the data that they store in the database data.

I suppose that no one had to deal with something like this, but if any of you were, I would be interested to hear any suggestions / templates that are worth exploring.

Answer 1

I would recommend choosing option 1. I used the query builder approach for a number of projects with varying degrees of complexity depending on the complexity of the requirements.

If you are able to use the turnkey solution, you can find several on the Internet: http://www.google.com/search?q=sql+query+builder

For a custom solution, at a minimum, you probably want to provide smoothed views for the user request; this will simplify design complexity, reduce the learning curve for the user, and provide some abstraction from future circuit changes.

After defining your underlying data sources, you need to provide the means by which the user can select specific columns, define filter criteria, determine the aggregation of values, and define sub-queries (depending on your query request). Selecting a column and defining a filter should not be too complicated, but to determine nontriviality to create aggregation and create subqueries. You should be able to use ready-made solutions as examples of how to present this function to the user.

Answer 2

as well as from 1 to many, for example, ProjectScopeKeywords, where N attributes can be assigned to the project or in this case “keywords” for several different attribute / search tables.

Can you be more frank about what this means?