Topic
  • 2 replies
  • Latest Post - ‏2013-03-13T20:21:34Z by canutri
clogs
clogs
186 Posts

Pinned topic SQL Dynamic Filtering

‏2013-03-12T14:55:16Z |
Dear forum,

We currently have a requirement which will allow our users (RUI) to use dynamic filters to filter on data (rows and columns) which are returned by a SQL query based on parameters they specify (e.g. "I want you to filter on a production date which is greater than or equal to a specified date").
The user would then provide the column on which to filter ("production date"), the condition ("greater than or equal to") and the data to filter on ("a specified date").
Of course, we can write each and every query which can possibly be the result of filters our users specify, but that is an endless task and not very flexible.

Basically, we want to (dynamically) say: select data through a service from a table using the query parameters as constructed by our user through a RUI front-end. Has anyone come across something like this and/or has a suggestion on how to tackle this?

Thanks in advance,
Willem Kunkels
Updated on 2013-03-13T20:21:34Z at 2013-03-13T20:21:34Z by canutri
  • markevans
    markevans
    2844 Posts

    Re: SQL Dynamic Filtering

    ‏2013-03-12T20:10:27Z  
    Willem,

    The only thing I know to do is to build a string with the select that you create and then prepare/execute at runtime.

    You build the string based on the selected values (column names), operators, and values for the where clause.

    If the SELECT list stays the same and it is only the where clause that has to be built dynamically, then it is even easier.

    This will keep you from having to code all the permutations. You just take what they specify and create the concatenated string.
  • canutri
    canutri
    357 Posts

    Re: SQL Dynamic Filtering

    ‏2013-03-13T20:21:34Z  
    • markevans
    • ‏2013-03-12T20:10:27Z
    Willem,

    The only thing I know to do is to build a string with the select that you create and then prepare/execute at runtime.

    You build the string based on the selected values (column names), operators, and values for the where clause.

    If the SELECT list stays the same and it is only the where clause that has to be built dynamically, then it is even easier.

    This will keep you from having to code all the permutations. You just take what they specify and create the concatenated string.
    Could this functionality be handled as a widget on the column to provide the filtering parameters and a behavior that does the actual filtering of the data grid? The filtering is done by the RUI and the query always returns the full dataset; albeit, not as efficient from a db standpoint.

    Daron