Customizable queries

[WolfShade]

Hello, all,

The project that I'm currently working on has a requirement that is making me cross-eyed.  Customizable queries.  (Rolling eyes)

The customer wants the ability to choose which columns will appear in a report.  But the data is normalized across several tables.

If the data were all in one table, it'd be a no-brainer.

What is an effective and efficient way to create queries on-the-fly across more than one table?  How do I determine which tables (based upon selected columns) will be part of the query?

V/r,

^_^

[BKBK]

I would start in the usual software development way: by writing down the client's requirements. This should specify all the columns, description of the queries and so on. How often will the client need various categories of data. Aim for a full, detailed description.

Next, an analysis of the requirements. Is a solution feasible? Efficiency requirements? Scalability?

Implementation: I would use REST or Web Service API. The client then becomes literally an API client. He or she will make a request to the API, passing to it the column names, query types, and any other relevant parameters. The API will have components that, respectively, put a query together, run the query against the database and return the result back to the client.

Ideally, each component should be responsible for just one task. The tasks may be defined, for example, according to database table, type of query, number and type of columns, database server, and so on.

In any case, I would ensure that all calls come through a controller component. Its task is to assemble the query and to pass it on to the appropriate component for processing.

[WolfShade]

Hi, BKBK‌,

Requirements: There are 17 columns of data pulled from (I think) six or seven tables.  The customer wants to be able to (from a form) check boxes indicating what columns they want for their customized report.

Last Name, First Name, TCID, EPID, DODID, Organization, Duty Location, Duty State, Duty Country, and eight other columns that are all DOD-related information that I (from home) don't currently have access to.  Tables are for personnel records, normalized with a table each for the person, organization, location, and three other factors.  (I did NOT design this - this is legacy from about 15 years ago.)

REST and WebService API's are not allowed - security risk.  This has to be a straight forward query.

Currently, it's looking as though I will have to write an almost endless list of conditional statements for both columns and tables.

Another idea that I thought of would be to use the current full report query, and do a QoQ against it.

Neither idea is very appealing.  I was just hoping that there might be an easier way to do it.

V/r,

^_^

[BKBK · Accepted solution]

WolfShade wrote:

REST and WebService API's are not allowed - security risk.  This has to be a straight forward query.

No problem. You could still use the exact same principle, but without REST or a Web Service. Just let separate CFCs perform the various tasks.

Currently, it's looking as though I will have to write an almost endless list of conditional statements for both columns and tables.

Not necessarily. You will make your code more scalable and more maintainable by passing parameters to the CFCs, telling them of the query, set of columns and table. (You may need to pass a set of tables in the case of join queries). Then you will be dealing with at most 4 to 5 parameters instead of an endless list.

Requirements: There are 17 columns of data pulled from (I think) six or seven tables.  The customer wants to be able to (from a form) check boxes indicating what columns they want for their customized report.

Last Name, First Name, TCID, EPID, DODID, Organization, Duty Location, Duty State, Duty Country, and eight other columns that are all DOD-related information that I (from home) don't currently have access to.  Tables are for personnel records, normalized with a table each for the person, organization, location, and three other factors.

No problem. Extend the client's choice to include specifications for putting a query together on the fly. Remember to take join queries into account.