Building a dynamic query

What about something like this:

1) Determine what tables are going to be involved (visual GUI/drag & drop interface for the users?)

2) Have a table that defines the relationship between the other tables in your database - once the table selection is made by the user, you can query this table to determine what columns you are going to join on.  Obviously this won't allow you complete customization but it would work for the scenarios you describe.

3) Use your database system properties to retrieve the field names from your included tables

4) Allow your users to add "filters" based on the fields retrieved in (3)

e.g. You could use an interface to allow users to add a filter, where you display a list of fields from your tables and allow them to apply a simple filter to them - keep it simple (=, !=, >, <)

for bonus points, you could limit the options based on the field type (checkboxes for bit fields, etc)

sanitize the user input - find a SQL-safe reg exp script to handle any unwanted characters (I'm looking at you apostrophe).

5) Don't try an support aggregates if you don't need to - users can load the results into excel and manipulate them to their hearts content.

6) Build your SQL using the selections made by the user.  Import all records from your joined tables.  Use the table from (2) to determine which fields they join on.  and build your where clause using the filters the user created in steap (4).  Again, be sure anything not explicitly set by the system is sanitized prior to making its way into the SQL.

7) Execute() your SQL inside a CF query.  Expect pretty poor performance, since most SQL databases won't optimize a query plan from a dynamic SQL evaluation.

You could build this as a multi-step wizard in CF, but it would be pretty flashy as a jQuery/web service based application.

My suggestion is to write down exactly what you want to be able to do, and what you don't want to be able to do before coding anything.  I've done things like this before, and you have to be very methodical.

The biggest question is how much are you hiding the SQL from the end users?

IF you have a SMALL, well DEFINED, and SQL knowledgeable user base, and you can GUARANTEE that ONLY this user base will have access to the tool, your interface could be as simple as a single text box for these users to type in their SQL.

That is a lot of conditions that almost never exist, but it has once for me.

Otherwise, your main concern is how friendly and fool proof your user interface needs to be.  Does it have to walk the users through each and every step of selecting various criteria or can it expect to be used by users that have at least some basic understanding of the data scheme and how to query it to get the desired output.

After that, it is pretty easy, you have some level of data that represents something from a full SQL statement to a set relevant filters and criteria.  You then just utilize basic string building functions to create the full SQL statement and pass it to the database through a <cfquery...> and|or <cfstoredproc...> tag.

The most IMPORTANT thing to make priority number one, is that if you are poviding ANY means for users to provide input into SQL, you had better use EVERY trick in the book to protect the data in your database from accidental or malicious SQL code that would damage or destroy your data.

• Scrub the input to an inch of its life
• Use a database user that has one the barest minimum permissions to the data needed for their required functionality.
• Tune the ColdFusion database driver to only allow the necessary SQL commands. (Not very secure, but why not)
• Back-up, the data often and thoroughly.

Just for starters

One important item to consider is security.  If you are not already familiar with sql injection, you should do a bit of research on it, and cfqueryparam tag.  Can't stress that enough.  Beyond the cfqueryparam tag there is more to do though, regarding sorting, grouping etc.  If you pass in parameters to power that you have to protect that section as well. http://www.coldfusionmuse.com/index.cfm/2008/7/21/SQL-injection-using-order-by  basically any cf variable directly put into a query needs to be protected or abstracted, or compared against known value options.

This should be done regardless if the report you are building is public facing or not.

As for the approach, there are lots of options.  Some of which you are already considering.  Sometimes it is easier to grab a bunch of data in a small db, then to lots of query of queries on it for reporting, though this really depends a lot on how the data is related, number of records, etc.  For example you can do couple queries, then join them via code.  Or loop over them and run sub queries.  Again this is all based on your db and reporting needs.

If you think that one dynamic query is getting too complicated, you can have a couple.  Check the params coming in, if a certain combo of them exist, then try this simple (er) dynamic query, else this other different kind of dynamic query, etc.