For effective data analysis, it is important that the database querying language has the ability to simultaneously interact with multiple databases. SQL is one of the most commonly used languages due to its speed and flexibility that allow the creation of advanced data analytics tools and dashboards. SQL is also a simple language that can perform complex data analysis with ease and accuracy.

SQL queries are used to fetch the data from relational databases. Queries can be written on-the-go based on the requirement, such as select queries and add certain conditions to it. The queries are small in size if the database is simple. The problem arises when the data set is complex and consists of a large number of tables.

In such a scenario, the queries will be complex or will include multiple queries joined together, which have to be stored in a form that makes them available to fetch the data. Also, if we want to incorporate different requests from the frontend to display the data with different filters, such as sorting, pagination, etc., generating query on-the-go becomes difficult. If we opt for storing query strings, we won’t be able to handle the dynamic requests. Storing multiple variants is also redundant.

In order to handle such use cases, we require dynamic queries. Dynamic SQL is an application that uses dynamic code to increase flexibility in querying a database. Hence, the solution is to create a basic structure of the query for each category and modify it based on the different options from the frontend.

Generating dynamic queries

The general structure of the query is as follows:

SELECT
Field_1,
Field_2,
Field_3,
Field_4,
…
FROM <project_id>.<dataset_name>.<table_name1> 
Join <project_id>.<dataset_name>.<table_name2> on <table_name1>.field_name1= <table_name2>.field_name1 
...
{{filter_condition}}
GROUP by 1,2, …
SORT BY 
{{pagination_line}}

In above case, there are three parts:

Query body that forms the main segment of the query,
Condition part that enables selective filtering of data, and
Pagination line to apply pagination to the query output

Of these, the body part remains constant. The {{filter_condition}} and {{pagination_line}} changes dynamically based on the filters applied or page number chosen from the UI.

We can keep these phrases as placeholders in the query and then replace them with the dynamically generated strings. In this article, we learn to generate these replacement strings that cover most of the use-cases. Let’s consider the employee database as an example for the same.

{{filter_condition}}Filters are the conditions on the values of the columns. These columns can be of different data types like string type, integer, boolean, etc. Suppose we have the selected filters data in the form of key value pair:

{
filterKey1: filterValue1,
filterKey2:filterValue2,
..
}

We can generate the filter expression as follows:

applyfilters = (query, filters) => {
   let conditionString = ``
 
   if (notEmptyFilters(filters)) {
   let conditionString = `WHERE `
       for (key in filters) {
         conditionString += `${key} = "${filters[key]}" AND `
       }
       conditionString = conditionString.substr(0, conditionString.length - 4) // to remove the last AND
 
   }
   // replacing {{filter_condition}} of the query with condition string
   query = query.replace("{{filter_condition}}", conditionString)
 
   return query
}

First check if the filters present or not, if there are no filters then we can directly replace the {{filter_condition}} with the blank.
Then iterate on the filters object, for each key add a string with the condition Key = “filter[key]” which is the value of the filter.

For example, consider the following conditions to be applied on the employee table:

Filter ={
designation:”Framework Engineer”,
location:”New York”,
manager_name:”Tom Stivers”
}
The generated filter condition will be 
“WHERE designation = ”Framework Engineer”  AND location = ”New York” AND manager_name = ”Tom Stivers” ”

But here the Key = “filter[key]” expression only covers the string type values. What if we have integer type values like emp_id:4012? In such cases, we first check the type of value and then modify the expression as follows:
Key = typeof filter[key] == “string” ? “filter[key]” : filter[key]
This handles the two cases string and integer. If the value is of type boolean, this will fail again.

If the values are varied and don’t belong to the two categories, we can have a filter key and type mapping pre-defined and can refer to that data when creating the expression

Const filterValueTypeMapping = {
"emp_id": {
        dataType: "number"
    },
    "gender": {
        dataType: "string"
    },
    "designation": {
        dataType: "string"
    },
    "is_active": {
        dataType: "boolean"
    },
“Salary”:{
        dataType: "number"
    },
}

Condition becomes:

Let filterType = filterValueTypeMapping[key].dataType
if(filterType == “string”)
Key = “filter[key]”
Else if(filterType == “number” || filterType == “boolean”)
Key = filter[key]

We have considered only single valued conditions so far. There are scenarios where we need to deal with a range of values, like salary ranges or array of values in case of multiselect. Below is the generalised method used to handle all these cases to generate the filter string for the given key – value pair.

generateFilterString = (filterKey, filterValue) => {
   let filterString = ''
   let filterType = filterValueTypeMapping[filterKey].dataType
 
   //if array of values, generate filter string
   if (Array.isArray(filterValue) && filterValue.length > 0) {
       if (filterType == "string") {
           filterValue.forEach(val => {
               filterString += `"${val}", `
           })
           filterString = filterString.substr(0, filterString.length - 2)
       }
       else {
           filterString = filterValue.join()
       }
       return `${key} IN (${filterString}) AND `
   }
   else if (typeof filterValue == 'object' && filterType == "range") {
    return `${filterKey} BETWEEN ${filterValue.from} AND ${filterValue.to} AND `
   }
   else {
       // if single value then return based on type
       filterValue = typeof filterValue == "string" ? `"${filterValue}"` : filterValue
       return `${filterKey} = ${filterValue} AND `
   }
 
}

In this method, we get the filterType from the filterMapping. We check if the value is an array type, then based on the data type of the filter we generate the comma separated list of values.

If the type of filterValue is object and filterType is range, then generate the string and the range expression following SQL syntax `KEY in BETWEEN value1 AND value2`, or else generate the expression for the key value pair.

Now the modified applyFilters method calling the generateFilterString method is described below:

applyfilters = (query, filters) => {
   let conditionString = ``
    if (notEmptyFilters(filters)) {
       let conditionString = `WHERE `
       for (key in filters) {
             conditionString += generateFilterString(key, filters[key])
      
       }
       conditionString = conditionString.substr(0, conditionString.length - 4) // to remove the last AND
    }
   // replacing {{filter_condition}} of the query with condition string
   query = query.replace("{{filter_condition}}", conditionString)
    return query
}

{{pagination_line}}
Pagination consists of two parts – one is LIMIT and another is OFFSET.
LIMIT is used to restrict the query output rows to a specified value whereas OFFSET is used to set a offset from the beginning of the query output, ie. the number of rows to be skipped from the top of the output. This is used to fetch the data for a particular page for the given page number.

For example, to fetch the data for the third page with page size 10, we generate the following expression skipping first 20 rows of the first 2 pages:
` LIMIT 10 OFFSET 20 `

Here, we need to calculate the offset number properly, based on the page number, to get the right output.
Offset can be calculated using page number and page size as
Offset = pageSize * (pageNumber-1)where page number is the current page for which the data is to be fetched

Below logic can be used for adding pagination to the query:

paginateQuery = (query, pageSize, pageNumber) => {
   let paginateString = `LIMIT ${pageSize} `
   if (pageNumber != 1) {
       let offset = pageSize * (pageNumber - 1)
       paginateString += `OFFSET ${offset} `
   }
   // replacing {{paginate}} of the query with paginate string
   query = query.replace('{{paginate}}', paginateString)
   return query
}

There is no offset for the first page.

Using these methods, we can logically build the dynamic query based on the selected parameters from the frontend. Dynamic SQL generation enables us to build SQL statements dynamically at runtime. This allows creation of more general purpose and flexible applications as well as addressing complex database problems.

The ability to directly interact with databases enables dynamic SQL to easily communicate complex instructions to databases and analyze data within seconds. This facilitates the creation of intuitive dashboards that can provide high-impact data visualization and reports.

Obtain custom analytics in minutes. Get in touch with us.