Dynamic SQL Query Generation via Modular WHERE Conditions
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing methods for generating SQL queries in relational databases face performance issues and exponential complexity when handling multiple search filters, either requiring numerous queries to maintain or using a single inefficient query.
Innovation Solution
An autonomic algorithm dynamically generates database queries using sub-queries, starting with a base query and adding 'WHERE' conditions through separate modules for each search filter, only when necessary, thereby avoiding exponential complexity and maintaining high performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If a general-purpose SQL query including all tables is used, then all search filters can be applied, but query performance deteriorates due to unnecessary table joins
Solution Approach 1:
The patent segments the query generation process into modular components: a base query template and separate filter modules. Each filter module contains the specific table joins and WHERE conditions needed for that filter type. This segmentation allows the system to assemble only the necessary components for each query, avoiding unnecessary table joins and improving performance while maintaining versatility.
2Productivity
If a different SQL query is created for every filter combination, then optimal performance is achieved for each case, but the number of queries to maintain grows exponentially
Solution Approach 1:
The patent implements dynamic query generation where a single base query template is combined with filter modules at runtime based on the actual search criteria. This dynamic assembly replaces the static maintenance of exponential numbers of queries, achieving optimal performance for each case while keeping the maintainable query count constant at a manageable level.
Solution Approach 2:
The system uses reflection and metadata to automatically determine which filter modules are needed for each query request. The query generator self-configures by examining the search criteria and assembling the appropriate combination of base query and filter modules without manual intervention, eliminating the need to maintain numerous individual queries.
3Device complexity
If only a subset of queries is implemented based on frequent combinations, then maintenance complexity is reduced, but adaptability to handle all filter combinations is limited
Solution Approach 1:
The base query template serves as a universal foundation that can work with any combination of filter modules. Each filter module is designed to be independently configurable and composable, allowing the system to handle any filter combination that was anticipated in the module definitions. This universal architecture provides both low maintenance complexity and high adaptability.
Data Source
AI summary
A process for generating SQL queries to retrieve requested information from a database, whilst applying requested search filters, involving defining a “base” query, which is the simplest SQL query that can be executed to retrieve the information requested by the user (i.e. without applying any search filter), and then use a separate module for each search filter to detect the existence of the table that is required to apply that filter, and possibly add a “WHERE” condition that includes the required table using a sub-query. For each module, if the table required to filter a particular attribute exists in the current query, the “WHERE” condition is added on that table, without adding any sub-query. If on the other hand the required table does not exist in the current query, a sub-query that navigates associations from the tables involved in the current query to the required on is added, and the “WHERE” condition is added to the required table within the sub-query.