Dynamic Query Generation : suggestion for better approaches
- by Gaurav Parmar
I am currently designing a functionality in my Web Application where the verified user of the application can execute queries which he wishes to from the predefined set of queries with where clause varying as per user's choice.
For example,Table ABC contains the following Template query called SecretReport
"Select def as FOO, ghi as BAR from MNO where "
SecretReport can have parameters XYZ, ILP. Again XYZ can have values as 1,2 and ILP can have 3,4
so if the user chooses ILP=3, he will get the result of the following query on his screen
"Select def as FOO, ghi as BAR from MNO where ILP=3"
Again the user is allowed permutations of XYZ / ILP
My initial thought is that User will be shown a list of Report names and each report will have parameters and corresponding values. But this approach although technically simple does not appear intuitive.
I would like to extend this functionality to a more generic level. Such that the user can choose a table and query based on his requirements. Of course we do not want the end user to take complete control of DB. But only tables and fields that are relevant to him. At present we are defining what is relevant in the code. But I want the Admin to take over this functionality such that he can decide what is relevant and expose the same to the user. On user's side it should be intuitive what is available to him and what queries he can form.
Please share your thoughts what is the most user friendly way to provide this feature to the end user.
