Zero-code data retrieval method and system for automatically constructing query conditions based on relational tree

Abstract

The invention discloses a zero-code data retrieval method and system for automatically constructing query conditions based on a relational tree, and relates to the technical field of computer software. The method comprises the following steps: automatically constructing a table relation tree according to configuration information of each table in a database; responding to a query condition set by a user for the child table, and automatically deducing a query condition of the parent table based on the table relation tree; automatically generating an SQL query statement according to the derivation condition and the user configuration condition; and executing query and merging multi-table data and then returning a result. Complex data query in a zero-code environment is realized, the technical problem of dynamic derivation of query conditions in a multi-level table relation is particularly solved, and the method can be widely applied to scenes such as a code-free platform and an enterprise data platform.

Description

Technical Field

[0001] This invention relates to the field of computer software technology, specifically to a zero-code data retrieval method and system based on automatic construction of query conditions using a relational tree. It is particularly suitable for scenarios such as no-code / low-code platforms and enterprise data platforms that require support for non-technical personnel to perform complex data queries. Background Technology

[0002] With the rapid growth of enterprise data volume, efficient and accurate data retrieval has become a critical issue. Traditional database queries heavily rely on SQL, and for non-technical personnel, writing complex SQL statements (especially those involving multi-table joins and dynamic condition construction) is difficult and error-prone, leading to business personnel's high dependence on IT personnel and low efficiency.

[0003] While some visual query tools exist in the prior art, they still cannot effectively solve the problem of dynamically deriving query conditions in multi-level table relationships. For example, the invention patent application with application number CN201710326709.4, although involving relation tree construction, focuses on virtual machine fault location and does not solve the problem of automatically deriving parent-child table query conditions in a zero-code environment; the invention patent application with application number CN202410393127.8 introduces the concept of relation trees for attribute community queries, but it cannot automatically derive parent table query conditions based on child table conditions, and cannot solve the technical difficulty of dynamically deriving parent-child table query conditions.

[0004] Therefore, there is an urgent need in this field for a data retrieval solution that can automatically construct query conditions and support zero-code operations. Summary of the Invention

[0005] The present invention aims to solve the technical problem in the prior art that it is difficult for non-technical personnel to construct complex query conditions, especially in the case of multi-level table relationships where it is impossible to dynamically deduce parent and child table query conditions.

[0006] To address the aforementioned problems, the first aspect of this invention provides a zero-code data retrieval method based on a relational tree for automatically constructing query conditions, comprising the following steps: Initialization and global relational tree construction: Based on the configuration information of multiple data tables in the database, an automatic table relational tree is constructed, where each data table is a node in the tree, and connections are established between nodes based on foreign key relationships; Query condition derivation: In response to query conditions configured by the user on the front-end interface for at least one target data table, query conditions for parent tables with foreign key relationships to the target data table are automatically deduced based on the table relational tree; Query statement generation: Based on the deduced query conditions and the user-configured query conditions, a Structured Query Language (SQL) query statement is automatically generated; Query execution and data merging: The SQL query statement is executed to obtain data from the target data table and its associated parent tables, and the obtained data is merged based on foreign key relationships, returning the merged query result.

[0007] Furthermore, the construction of the table relationship tree includes: traversing all data tables with foreign key relationships in the database through recursive queries to generate a complete table relationship tree, and explicitly representing the field information and foreign key relationships of each data table in the table relationship tree.

[0008] Furthermore, the query condition derivation includes user interaction and condition configuration, and intelligent condition derivation and link analysis; wherein, user interaction and condition configuration refers to: the user selects the target table and sets query conditions through the front end; intelligent condition derivation and link analysis refers to: the system automatically derives the sub-table conditions to the parent table based on the table relationship tree.

[0009] Furthermore, the query condition derivation also includes: if the user has configured query conditions for the sub-table but not for the parent table, the query conditions of the sub-table are automatically deduced to the parent table to construct a complete cross-table query condition chain.

[0010] Furthermore, the user interaction and condition configuration include: Step 1. Front-end interface interaction: Based on the relationship tree generated in step S101, the system displays a visual graphical interface to the user; Step 2. Target Table and Condition Configuration: Users select one or more "target tables" and configure filtering conditions through a graphical interface; Among them, the target table after the condition configuration is updated.

[0011] The first aspect of this invention provides a zero-code data retrieval system based on automatic construction of query conditions using a relation tree, comprising: a relation tree construction module, a condition derivation module, a query generation module, and a query execution module connected by communication, wherein each module works collaboratively; The relation tree building module is responsible for establishing connections with various databases and obtaining metadata information; automatically analyzing foreign key relationships between tables and building a complete table relation tree; and initializing the state of all table nodes.

[0012] The condition derivation module provides a user interface to receive query condition configurations; implements intelligent condition derivation algorithms to automatically construct cross-table query condition chains; and manages table node status changes.

[0013] The query generation module assembles a complete SQL query syntax tree; performs query performance optimization, including subtree pruning; and generates the final executable SQL statement.

[0014] The query execution module executes the generated SQL query statements; processes the query results, performs hierarchical data reorganization; and returns structured query results.

[0015] Furthermore, the relationship tree construction module includes: Database connection submodule: Supports connections to various database types, including relational databases and non-relational databases; Metadata Acquisition Submodule: Queries database system tables, configured to retrieve a list of data tables, field definitions, and data types; extracts primary key constraints and foreign key constraints. The recursive construction submodule implements the relation tree construction algorithm, configured to recursively traverse from the root node; it also detects and handles circular references. Status Management Submodule: Maintains node status information.

[0016] Furthermore, the condition derivation module includes: User interaction submodule: Provides a graphical user interface, configured to display the hierarchical structure of data tables based on a relational tree; updates the status display of table nodes in real time; Condition Receiving Submodule: Processes user-input query conditions, configured to parse the target table and filter conditions selected by the user; updates the status of the table nodes configured with the conditions; The intelligent derivation submodule implements the core derivation algorithm, including: upward derivation algorithm: recursively deriving from the child table to the parent table; EXISTS / IN subquery generation: constructing join query conditions; and status marking: marking tables on the join path. Condition merging submodule: Configured to handle condition conflicts and merging. When user conditions exist in the parent table, the AND logic is used to merge and deduce the conditions.

[0017] A third aspect of the present invention provides an electronic device including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the program to implement any of the methods described above.

[0018] A fourth aspect of the present invention provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements any of the methods described above.

[0019] Compared with the prior art, the beneficial effects of the present invention are as follows: Zero-code operation: Users can complete complex queries through a graphical interface without writing SQL.

[0020] Automatic condition derivation: Solves the problem of dynamically constructing query conditions in multi-level table relationships.

[0021] Efficient data integration: Enables rapid association and merging of data from multiple tables based on a relational tree.

[0022] Wide applicability: Supports relational and non-relational databases (such as Redis JSON), suitable for various scenarios such as no-code platforms and data middleware platforms. Attached Figure Description

[0023] The accompanying drawings, which are incorporated in and form part of this specification, illustrate embodiments consistent with the invention and, together with the description, serve to explain the principles of the invention.

[0024] To more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, for those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0025] Figure 1 This is a flowchart of the zero-code data retrieval method provided in Embodiment 1 of the present invention.

[0026] Figure 2 This is a schematic diagram of the construction of the table relationship tree in Embodiment 1 of the present invention.

[0027] Figure 3 This is a schematic diagram illustrating the automatic derivation of query conditions in Embodiment 1 of the present invention.

[0028] Figure 4 This is a schematic diagram of the zero-code data retrieval system in Embodiment 2 of the present invention.

[0029] Figure 5 This is a flowchart of the zero-code data retrieval method in Embodiment 3 of the present invention. Detailed Implementation

[0030] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only a part of the embodiments of the present invention, and not all of them. Based on the embodiments of the present invention, all other embodiments obtained by those of ordinary skill in the art without creative effort are within the scope of protection of the present invention.

[0031] It should be noted that all directional indications (such as up, down, left, right, front, back, etc.) in the embodiments of the present invention are only used to explain the relative positional relationship and movement of each component in a certain specific posture (as shown in the figure). If the specific posture changes, the directional indication will also change accordingly.

[0032] Furthermore, the use of terms such as "first" and "second" in this invention is for descriptive purposes only and should not be construed as indicating or implying their relative importance or implicitly specifying the number of technical features indicated. Therefore, features defined with "first" and "second" may explicitly or implicitly include at least one of those features. Additionally, the technical solutions of the various embodiments can be combined with each other, but only on the basis of being achievable by those skilled in the art. When the combination of technical solutions is contradictory or impossible to implement, such a combination of technical solutions should be considered non-existent and not within the scope of protection claimed by this invention.

[0033] Example 1 See Figure 1 As shown, the zero-code data retrieval method based on a relation tree for automatically constructing query conditions provided by this invention includes the following steps: S101. Initialization and Global Relationship Tree Construction: The system reads database metadata and recursively constructs the table relationship tree. This includes: the system automatically constructs the table relationship tree based on the configuration information of multiple data tables in the database, where each data table is a node in the tree, and the nodes are connected based on foreign key relationships; the system traverses all data tables with foreign key relationships in the database through recursive queries to generate a complete table relationship tree, and explicitly represents the field information and foreign key relationships of each data table in the table relationship tree.

[0034] S102, Query condition derivation, including user interaction and condition configuration, and intelligent condition derivation and link analysis. User interaction and condition configuration refer to the user selecting a target table and setting query conditions through the front end. Intelligent condition derivation and link analysis refer to the system automatically deducing sub-table conditions to parent table conditions based on the table relationship tree. Specifically, if the user configures query conditions for a sub-table but not for the parent table, the system automatically deduces the sub-table's query conditions to the parent table to construct a complete cross-table query condition chain.

[0035] S103. Query Statement Generation: Generates optimized SQL query statements. This includes automatically generating Structured Query Language (SQL) query statements based on derived query conditions and user-configured query conditions.

[0036] S104. Execute the query, merge the data and return the result.

[0037] The following will combine Figure 2-3 The method and process of the above multi-level query will be explained in detail. Figure 3 In the table: Table A indicates no conditions; Table B indicates conditions, such as age > 30; Table E indicates conditions, such as quantity > 100; Table F indicates no conditions and no query link exists; Table C indicates conditions, such as status = activated; Table D indicates no conditions; Table G indicates conditions, such as date > 2021-01-01. The origin marks the defined data tables; green background indicates the default table status; yellow background indicates that the syntax configuration has a query link, but the user has not configured query conditions; blue background indicates that the user has set a query status (the system-built syntax will be merged with the user-configured syntax, if any).

[0038] The specific execution process of step S101 includes the following steps: Step 1.1, Connection and Metadata Acquisition: The system first connects to the target database (such as relational databases like MySQL and PostgreSQL, or non-relational databases like Redis that support JSON data structures).

[0039] After a successful connection, the system queries the database's system tables or information schemas (such as INFORMATION_SCHEMA) to obtain complete metadata information, including a list of all data tables, field definitions for each table, primary keys, and foreign key constraints.

[0040] Step 1.2, Node and Edge Identification: The system abstracts the table relationships in the database into a graph structure. Each data table is treated as an independent node in the graph; connections between nodes are established by analyzing foreign key constraints. Specifically, by analyzing foreign key constraints (e.g., the user_id field in table A references the id field in table B), edges between nodes are identified, thereby determining parent-child relationships (e.g., table B is the parent table, and table A is the child table).

[0041] Step 1.3, Recursively construct the tree structure: The system starts from one or more root nodes (usually tables without foreign key dependencies on parent tables, such as the company table) and executes a recursive algorithm: Recursive steps: For the current table, find all child tables that directly reference its primary key. Then, for each found child table, repeat this process, i.e., "traverse the current node -> find all its child nodes -> recursively traverse each child node".

[0042] Termination conditions include: when a table has no child tables, the recursion terminates on that branch; when a circular reference is detected, the recursion exits safely.

[0043] Step 1.4, Tree Structure Generation and State Initialization: The recursive process generates a complete table relation tree, such as... Figure 2 As shown, this represents the complete table relationship tree generated after state initialization, which is the table relationship in the database; the green background indicates the default table state: the table exists in the relationship tree but has not yet participated in the current query.

[0044] For example: The complete table relationship tree generated after state initialization is: Company Form (Green) └── Department Table (Green) └── Employee List (Green) └── Project List (green).

[0045] The company table serves as the root node, and its child nodes can be department tables. The child nodes of the department tables can be employee tables, and the child nodes of the employee tables can be project tables.

[0046] Step S101 is the foundation of the entire method; the generated table relationship tree serves as the "map" for all subsequent automatic deduction operations. Through this explicit relationship map generated in step S101, the system can understand how tables are related and perform cross-table conditional deductions, providing a topological basis for automatic conditional deduction.

[0047] Step S102 includes: Step 2.1, User Interaction and Condition Configuration: Users select the target table and set query conditions through the front end. The specific execution process includes: Step 1. Front-end Interface Interaction: Based on the relational tree generated in step S101, the system displays a visual graphical interface to the user. This interface typically displays a list of available data tables (possibly in the form of a suffix tree or folders, with the underlying logic based on the relational tree generated in S101); to support tree-structured or folder-style navigation interfaces, it also provides intuitive field selectors and conditional input controls.

[0048] Step 2. Target Table and Condition Configuration: The user first selects one or more "target tables" of interest (e.g., they ultimately want to see data from the "Project Table"). Then, they configure filtering conditions through the graphical interface, including setting query conditions for these target tables by selecting fields, drop-down menus, input boxes, and other controls (e.g., setting the condition for the Project Table: Project Status = "In Progress").

[0049] After the target table is configured with the conditions, its status is updated: the status of the table node with the set conditions changes from green to blue. Blue indicates that the table contains the query conditions directly configured by the user. For example... Figure 3 As shown in the figure, the process from (1) to (2) will change the status of the conditional nodes B, C, E and G from green to blue.

[0050] This step is the user input stage. The user's stated "what they want" (target table) and "what the filtering conditions are" (query conditions) form the starting point for the entire query task. The user-inputted query conditions are the seeds for subsequent automatic derivation. The system will use these explicitly configured conditions as a starting point to expand its algorithm on the relationship tree map.

[0051] Step 2.2, Intelligent Condition Derivation and Link Analysis: Based on the table relationship tree, the system automatically derives the conditions from the sub-table to the parent table. The specific execution process is as follows: Step 1. Condition Location and Tree Traversal: The system receives the query conditions set by the user in step S102. For example, the user sets the condition "Age > 30" for the "Employee Table". The system traverses the relation tree constructed in step S101 and locates the corresponding node (such as the "Employee Table" node), and executes the intelligent derivation algorithm.

[0052] Step 2. Upward Derivation Algorithm: The system initiates an algorithm that traverses from child nodes to parent nodes, that is, the system recursively derives the algorithm from the node containing the condition towards the root node. For example, for each parent node of the "Employee Table" (e.g., the "Department Table"), the system will execute the following logical derivation: Logical relationship construction: First, the system will construct the following logic: "To find employees who are over 30, you need to find the departments that have these employees." Query condition transformation: To achieve the above logic, the system will automatically generate a query condition for the parent table (department table). This condition is usually an EXISTS subquery or an IN subquery.

[0053] Generating derivation conditions: Ultimately, the system automatically generates derivation conditions for the "Department Table". For example, they might be: SQL EXISTS ( SELECT 1 FROM employee table) WHERE employee_table.department_id = department_table.id AND employee table.age>30 -- This is the user-defined condition) Step 3. Recursive Derivation: This process is recursive; the system will not stop at the first-level parent table. For example, after generating derivation conditions for the "Department Table," the system will continue upwards, treating the "Department Table" as a new "child table," and derive from its parent table (such as the Company Table) to generate conditions such as EXISTS (SELECT 1 FROM Department Table WHERE ...), thus forming a complete chain of query conditions.

[0054] The target table after recursive derivation undergoes a state update. The yellow nodes indicate the automatically identified bridge tables, which have no user-defined conditions but are crucial for establishing query relationships. For example... Figure 3 As shown in (2), Tables A and D themselves have no conditions set, but since they are both on the conditional node link, the system changes their node status from green to yellow, indicating that they are on the query association link.

[0055] Step 2.3, Condition Merging: If the user has manually configured conditions for the parent table, the system will merge the automatically derived conditions with the user-configured conditions using AND logic to ensure the accuracy and completeness of the query results. For example... Figure 3 As shown in (3), the node status of tables A and D on the query association link will change from yellow to blue.

[0056] This invention, through step S102 where the system automatically derives the conditions from the child table to the parent table based on the table relationship tree, completely solves the technical challenge of "how the parent table automatically constructs query conditions when the child table has conditions." Users do not need to understand the underlying table relationships; they only need to focus on their most direct data view, as the system automatically completes all upstream filtering logic. Specifically, the process involves a combined algorithm of "EXISTS subquery" and "recursive upward traversal of the relationship tree," which "translates" the conditions set by the user at a node in the tree into a query language that all ancestor nodes can understand, achieving full automation.

[0057] Step S103: Query statement generation and optimization step: Generates optimized SQL query statements, and the specific execution process includes: Step 3.1, Syntax Tree Assembly: The system integrates all query elements, including: SELECT fields: Fields selected by the user from the target table, supporting field selection and alias definition.

[0058] The FROM clause determines the tables to be joined based on the optimized query subtree, including only table nodes in blue and yellow states.

[0059] The WHERE clause combines the explicit conditions configured by the user on all tables (blue nodes) with the implicit conditions automatically derived by the system (EXISTS subqueries in yellow nodes).

[0060] Step 3.2, SQL Assembly and Performance Optimization: The system assembles all query elements into a complete and syntactically correct SQL statement. During this process, preliminary optimizations may also be performed, including: (1) Subtree pruning optimization, including: 1. Removing green nodes: Discarding tables that have neither user-configured conditions in the query condition chain nor are they in the query chain (i.e., "orphaned" table nodes from which conditions cannot be derived from child nodes). This reduces unnecessary JOINs and improves query performance. For example... Figure 3 As shown in (3), table F is removed. 2. Generate a concise query subtree: containing only the necessary blue and yellow nodes, the remaining blue and yellow nodes together form a concise and efficient "query syntax subtree", as shown in (3). Figure 3 As shown in (3). This subtree fully contains the minimum set of tables required from the main table to all filtering conditions.

[0061] Example of before-and-after optimization comparison: Figure 3 Before optimization: The complete relation tree containing all green nodes (shown) Figure 3 (1) diagram structure); after optimization: only blue and yellow nodes that actually participate in the query are included, such as Figure 3 shown in (3).

[0062] SQL generation: The system automatically generates precisely structured SQL statements. SQL Generation: The system automatically generates precisely structured SQL statements that only involve tables within the "query syntax subtree" (such as...). Figure 3 (3) shows: For example: SELECT CompanyTable.CompanyName, DepartmentTable.DepartmentName, EmployeeTable.EmployeeName, ProjectTable.ProjectName FROM CompanyTable INNER JOIN Department_table ON Company_table.id = Department_table.Company_id INNER JOIN employee_table ON department_table.id = employee_table.department_id INNER JOIN Project_table ON Employee_table.id = Project_table.Responsible_id WHERE ProjectTable.ProjectStatus='In Progress' AND EXISTS ( SELECT 1 FROM employee table) WHERE employee_table.department_id = department_table.id AND employee table. Age > 30).

[0063] Step S103 of the present invention is a bridge from logical model to physical execution. It ultimately concretizes the "relationship tree", "user intent" and "derivative logic" generated in the previous steps into standard SQL code that the database engine can understand and execute.

[0064] S104. Query Execution and Data Merging Steps: Execute the query, merge the data, and return the result. This includes executing the SQL query statement, obtaining data from the target data table and its associated parent tables, merging the obtained data based on foreign key relationships, and returning the merged query result. The specific execution process includes: Step 4.1, Query Execution: The system sends the SQL statement generated in step S103 to the database engine for execution through the database connection pool; the database engine executes complex multi-table join queries; the system monitors query performance and execution status.

[0065] Step 4.2, Data Acquisition and Structured Merging: After executing this complex multi-table join query, the database engine returns a flattened result set. The system receives this flattened result set and reorganizes it into a hierarchical structure. The system does not simply return a flattened result set. It reorganizes the data into nested JSON structures or other hierarchical data structures based on the initially constructed table relationship tree. For example, the returned data might include: company name and department information, department information including department name and employee information, and employee information including employee name, age, and project, etc.

[0066] For example: The original flattened result set is: (Company Name: "Company A", Department Name: "R&D Department", Employee Name: "Zhang San", Project Name: "Project X").

[0067] After structuring, it becomes: Company Name: "Company A" department: Department Name: Research and Development Department staff: Employee Name: Zhang San project: Project Name: "Project X"

[0068] Step 4.3, Result Return: Return the final structured data to the front-end interface and display it to the user in the form of tables, charts, or tree components.

[0069] This invention completes a closed loop from "user intent input" to "final result output" through step S104. In particular, the structured merging of data directly demonstrates the advantages of querying based on a relational tree, providing users with an intuitive data view that conforms to business logic.

[0070] The method described in this invention, through the close integration of constructing a "relationship tree map" and an "automatic condition derivation algorithm," enables the system to understand the complex relationships between data and to perform the most difficult task of constructing cross-table conditions on behalf of the user. Ultimately, this achieves efficient and accurate retrieval of multi-level, complexly related data with zero coding required. This not only lowers the technical threshold but also fundamentally improves the automation and intelligence of data querying.

[0071] The technical advantages and innovations of this invention are as follows: 1. Zero-code operation: The graphical interface completely replaces SQL writing, allowing non-technical personnel to perform complex data queries.

[0072] 2. Intelligent Condition Derivation: Based on a recursive derivation algorithm using a relational tree, the system constructs queries that are not static but dynamically generated and optimized according to user-configured conditions. The scope and structure of the query adapt to changes based on user clicks (the position and number of blue nodes). It automatically resolves the dynamic construction problem of query conditions between parent and child tables.

[0073] 3. Performance optimization: By using pruning algorithms to remove irrelevant table nodes, JOIN operations are reduced; high performance is maintained in complex relational networks.

[0074] 4. Data Hierarchy: Maintains the natural hierarchical relationship of data; provides an intuitive display, returning nested data structures that conform to business logic. The automatic representation of yellow status nodes allows the system to perform complex relationship derivation processes on behalf of the user, solving the technical problems of complex query conditions and difficult relationship derivation.

[0075] 5. Visualized Logical Reasoning: The green-yellow-blue color coding system visualizes the system's internal logical reasoning process. It allows users (especially administrators or developers) to intuitively understand how the system "thinks" and why a query may or may not include certain tables.

[0076] The embodiments of the present invention are particularly suitable for a variety of applications, including: Enterprise data platform: Business personnel can conduct data analysis independently; No-code / low-code platform: Enhanced data query capabilities; Intelligent customer service system: Automatically constructs complex query conditions; Multi-level data management: Scenarios requiring deep relational queries.

[0077] The complete technical solution of this embodiment achieves the goal of efficient and accurate retrieval of multi-level and complex data with zero coding, significantly reducing the technical threshold and improving the automation and intelligence of data query.

[0078] Example 2 This invention also provides a zero-code data retrieval system based on a relation tree for automatically constructing query conditions, specifically including: The communication connection relationship tree construction module, condition inference module, query generation module, and query execution module work together.

[0079] The relation tree building module is responsible for establishing connections with various databases and obtaining metadata information; automatically analyzing foreign key relationships between tables and building a complete table relation tree; and initializing the status of all table nodes to green (the default state).

[0080] The condition derivation module provides a user interface to receive query condition configurations; implements an intelligent condition derivation algorithm to automatically construct cross-table query condition chains; and manages table node status changes (green → blue → yellow).

[0081] The query generation module assembles a complete SQL query syntax tree; performs query performance optimization, including subtree pruning; and generates the final executable SQL statement.

[0082] The query execution module executes the generated SQL query statements; processes the query results, performs hierarchical data reorganization; and returns structured query results.

[0083] The relationship tree construction module includes: Database connection submodule: Supports connections to various database types, including relational databases such as MySQL, PostgreSQL, and Oracle, and non-relational databases such as Redis and MongoDB with JSON structure.

[0084] Metadata Acquisition Submodule: Queries database system tables (such as INFORMATION_SCHEMA), configured to retrieve table lists, field definitions, and data types; extracts primary key constraints and foreign key constraints.

[0085] The recursive construction submodule implements the relational tree construction algorithm, configured to recursively traverse from the root node (the table with no foreign key dependencies); it detects and handles circular references to ensure the safety of the construction process.

[0086] The state management submodule maintains node state information, where green indicates that the table exists in the relation tree but is not involved in the current query.

[0087] The condition derivation module includes: The user interaction submodule provides a graphical user interface configured to display the hierarchical structure of data tables based on a relational tree; it also provides interactive elements such as field selectors and conditional input controls; and it updates the status of table nodes in real time.

[0088] Condition Receiving Submodule: Processes user-input query conditions, configured to parse the target table and filter conditions selected by the user; updates the status of the table node with the configured conditions to blue.

[0089] Intelligent Derivation Submodule: Implements the core derivation algorithm, including: Upward Derivation Algorithm: Recursively derives from child table to parent table; EXISTS / IN Subquery Generation: Constructs join query conditions; Status Marking: Marks tables on the join path as yellow.

[0090] Condition merging submodule: Configured to handle condition conflicts and merging. When user conditions exist in the parent table, the AND logic is used to merge and deduce the conditions, ensuring the completeness and accuracy of the query conditions.

[0091] The query generation module includes: Syntax tree assembly submodule: Integrates query elements: SELECT field, based on the target table field selected by the user; FROM clause, determines the set of tables to be joined; WHERE clause, merges explicit conditions and implicit inferred conditions.

[0092] The optimization processing submodule implements performance optimization algorithms, including subtree pruning (removing green nodes, which are irrelevant to the table), query simplification (generating concise query subtrees containing only blue and yellow nodes), and JOIN optimization (reducing unnecessary table join operations).

[0093] SQL generation submodule: Outputs standard SQL statements, including generating SQL queries that conform to database specifications, ensuring syntax correctness and execution efficiency.

[0094] The query execution module includes: The query execution submodule manages database operations, including: executing SQL statements through a database connection pool; monitoring query performance and execution status; and handling query timeouts and exceptions.

[0095] The data merging submodule reconstructs query results, including: converting flat database results into a hierarchical structure; reconstructing parent-child relationships based on a relation tree; and generating nested JSON data structures.

[0096] The results return submodule outputs the final results, including: support for multiple data display formats; provision of visualization components such as tables, trees, and charts; and ensuring the completeness and accuracy of the returned data.

[0097] The specific implementation methods of the functions of the above modules are the same as those of the method in Embodiment 1 above, and will not be repeated here.

[0098] Example 3: In the enterprise data platform, business personnel can use the system of this invention to complete multi-level data association queries of sales orders, customers, products, etc. without writing SQL, which greatly improves data analysis efficiency.

[0099] In traditional database queries, when it's necessary to retrieve records that simultaneously meet both order criteria and related product criteria, business users must write complex SQL statements, such as using JOIN joins or subqueries. For example, to query "orders with a quantity greater than 100 and the product name is 'mouse'", one would need to write SQL similar to the following: This query method has a high technical threshold for non-technical personnel and is prone to errors.

[0100] The specific implementation process of applying the zero-code data retrieval method and system based on relational tree automatic query condition construction in Embodiments 1 and 2 to an enterprise data platform to solve the above-mentioned technical problems includes: The system initialization phase includes: Step S301: Relationship Tree Construction The system reads the database metadata and automatically identifies the foreign key relationship between the orders table and the goods table: The orders table contains fields such as id, goods_id (foreign key), quantity, and order_date.

[0101] The goods table contains fields such as id, name, price, and category.

[0102] The system constructs a table relationship tree through recursive queries: orders table (green) └── Goods List (Green) The relationship tree clearly shows that the orders table is linked to the id primary key of the goods table through the goods_id foreign key; The two tables form a parent-child relationship: the goods table is the parent table, and the orders table is the child table.

[0103] The user interaction and condition configuration phase includes: Step S302: User operation process, specifically as follows: Open the operation page: Users enter the graphical interface of the zero-code data retrieval system; Select the main query table: The user selects the "orders table" as the main query target from the table list; Configure order conditions: The user sets the query condition for the orders table as "quantity>100"; Related Product Table: The system automatically displays the goods table associated with the orders table, and the user selects and configures product conditions; Configure product criteria: The user sets the query criteria for the goods table as: "name = 'mouse'".

[0104] Step S303: Status flag update, that is, after configuration is completed, the system updates the table node status, including: orders table → Blue (User has set order quantity conditions); Goods table → Blue (User has set product name criteria).

[0105] The intelligent condition derivation stage includes: Step S304: Automatic derivation algorithm, i.e., the system automatically performs conditional derivation based on the constructed relation tree, including: 1. Relationship Analysis: Identify the foreign key relationships between the orders table and the goods table; 2. Derivation Logic Construction: The system constructs the business logic: "To query orders with the product name 'mouse,' you need to first find the product with the name 'mouse,' and then find the orders associated with these products." 3. Query condition transformation: Derive the query conditions from the goods table to the orders table.

[0106] Step S305: Generate derivation conditions, that is, the system automatically generates derivation conditions similar to IN subqueries in SQL, such as: orders.goods_id IN ( SELECT id FROM goods WHERE name ='Mouse' ).

[0107] The query generation and execution phase includes: Step S306: Complete query condition assembly, which means the system logically merges the user-configured conditions and the derived conditions, including: User configuration condition: orders.quantity > 100; System-derived condition: orders.goods_id IN (SELECT id FROM goods WHERE name ='mouse'); Merged condition: orders.quantity>100 AND orders.goods_id IN (SELECT id FROM goods WHERE name ='mouse').

[0108] Step S307: SQL statement generation. The system automatically generates optimized SQL query statements, such as: SELECT orders.* FROM orders WHERE orders.quantity>100 AND orders.goods_id IN ( SELECT id FROM goods WHERE name ='Mouse' ).

[0109] Step S308: Query execution and result return, including: 1. Execute query: The system executes the generated SQL statement through the database connection pool; 2. Result retrieval: The database returns all order records that meet the criteria; 3. Data encapsulation: The system organizes the query results into a structured data format; 4. Interface Display: The query results are displayed in a table format on the front-end interface, including order ID, product ID, quantity, order date, etc.

[0110] In the application of this embodiment, the SQL query statement generated by this solution can accurately return: all records with an order quantity greater than 100; and the product name associated with these orders is "mouse"; which fully meets the user's business query intent and obtains accurate query results.

[0111] In this embodiment, the solution achieves zero-code operation: users do not need to write any SQL statements; it enables intuitive condition configuration: tables are selected and conditions are set through a graphical interface; it enables automatic join processing: the system automatically handles the relationships between tables, and users do not need to understand the concept of foreign keys; and it enables real-time feedback: the system immediately returns the query results to support business decisions.

[0112] This embodiment fully demonstrates the practical application effect of a zero-code data retrieval solution based on automatically constructing query conditions using a relational tree, through a query scenario involving the association of the `orders` and `goods` tables. It fully proves the effectiveness and practicality of this invention in solving real-world business query needs, providing an efficient and easy-to-use data retrieval solution for various enterprises.

[0113] Example 4 The present invention also provides an electronic device, including: a processor, a transmitting device, an input device, an output device, and a memory. The processor may be implemented using a general-purpose CPU (Central Processing Unit), a microprocessor, an application-specific integrated circuit, or one or more integrated circuits, and is used to execute relevant programs to implement the technical solutions provided in the embodiments of this application. The memory may be implemented using a read-only memory (ROM), a static storage device, a dynamic storage device, or a random access memory (RAM), and is used to store computer program code. The computer program code includes computer instructions. When the processor executes the computer instructions, the electronic device executes a method as described in any of the above possible implementation methods.

[0114] Example 5 The present invention also provides a computer-readable storage medium storing a computer program, the computer program including program instructions, which, when executed by a processor of an electronic device, cause the processor to perform a method as described in any of the above possible implementations.

[0115] In the description of this specification, the references to terms such as "an embodiment," "example," "specific example," etc., indicate that a specific feature, structure, material, or characteristic described in connection with that embodiment or example is included in at least one embodiment or example of the present invention. In this specification, the illustrative expressions of the above terms do not necessarily refer to the same embodiment or example. Furthermore, the specific features, structures, materials, or characteristics described may be combined in any suitable manner in one or more embodiments or examples.

[0116] The above description is merely a specific embodiment of the present invention, enabling those skilled in the art to understand or implement the invention. Various modifications to these embodiments will be readily apparent to those skilled in the art, and the general principles defined herein may be implemented in other embodiments without departing from the spirit or scope of the invention. Therefore, the present invention is not to be limited to the embodiments shown herein, but is to be accorded the widest scope consistent with the principles and novel features claimed herein.

Claims

1. A zero-code data retrieval method based on automatically constructing query conditions using a relational tree, characterized in that, Includes the following steps: Initialization and Global Relationship Tree Construction: Based on the configuration information of multiple tables in the database, a table relationship tree is automatically constructed, where each table is a node in the tree, and nodes are connected based on foreign key relationships; Query Condition Derivation: Responding to the query conditions configured by the user on the front-end interface for at least one target table, the query conditions of the parent tables with foreign key relationships with the target table are automatically derived based on the table relationship tree; Query Statement Generation: Based on the derived query conditions and the user-configured query conditions, a Structured Query Language (SQL) query statement is automatically generated; Query Execution and Data Merging: The SQL query statement is executed to obtain data from the target table and its associated parent tables, and the obtained data is merged based on foreign key relationships, returning the merged query result.

2. The method according to claim 1, characterized in that, The construction of the table relationship tree includes: traversing all data tables with foreign key relationships in the database through recursive queries to generate a complete table relationship tree, and explicitly representing the field information and foreign key relationships of each data table in the table relationship tree.

3. The method according to claim 1, characterized in that, The query condition derivation includes user interaction and condition configuration, and intelligent condition derivation and link analysis. User interaction and condition configuration refers to the user selecting the target table and setting query conditions through the front end. Intelligent condition derivation and link analysis refers to the system automatically deriving the sub-table conditions to the parent table based on the table relationship tree.

4. The method according to claim 3, characterized in that, The query condition derivation also includes: if the user has configured query conditions for the sub-table but not for the parent table, the query conditions for the sub-table are automatically deduced to the parent table to construct a complete cross-table query condition chain.

5. The method according to claim 4, characterized in that, The user interaction and condition configuration include: Step 1. Front-end interface interaction: Based on the relationship tree generated in step S101, the system displays a visual graphical interface to the user; Step 2. Target Table and Condition Configuration: Users select one or more "target tables" and configure filtering conditions through a graphical interface; Among them, the target table after the condition configuration is updated.

6. A zero-code data retrieval system based on a relation tree for automatically constructing query conditions, characterized in that, include: The communication connection relationship tree construction module, condition inference module, query generation module, and query execution module work together. The relation tree construction module is responsible for establishing connections with various databases and obtaining metadata information; automatically analyzing foreign key relationships between tables and constructing a complete table relation tree; and initializing the state of all table nodes. The condition derivation module provides a user interface and receives query condition configurations. Implement intelligent condition derivation algorithms to automatically construct cross-table query condition chains; manage table node status changes; The query generation module assembles a complete SQL query syntax tree; Perform query performance optimizations, including subtree pruning; generate the final executable SQL statement; The query execution module executes the generated SQL query statements; processes the query results; and performs hierarchical data reorganization. Returns structured query results.

7. The system according to claim 6, characterized in that, The relationship tree construction module includes: Database connection submodule: Supports connections to various database types, including relational databases and non-relational databases; Metadata Acquisition Submodule: Queries database system tables, configured to retrieve a list of data tables, field definitions, and data types; extracts primary key constraints and foreign key constraints. The recursive construction submodule implements the relation tree construction algorithm, configured to recursively traverse from the root node; it also detects and handles circular references. Status Management Submodule: Maintains node status information.

8. The system according to claim 6, characterized in that, The condition derivation module includes: User interaction submodule: Provides a graphical user interface, configured to display the hierarchical structure of data tables based on a relational tree; updates the status display of table nodes in real time; Condition Receiving Submodule: Processes user-input query conditions, configured to parse the target table and filter conditions selected by the user; updates the status of the table nodes configured with the conditions; The intelligent derivation submodule implements the core derivation algorithm, including: upward derivation algorithm: recursively deriving from the child table to the parent table; EXISTS / IN subquery generation: constructing join query conditions; and status marking: marking tables on the join path. Condition merging submodule: Configured to handle condition conflicts and merging. When user conditions exist in the parent table, the AND logic is used to merge and deduce the conditions.

9. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the program, it implements the method as described in any one of claims 1 to 5.

10. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the program is executed by the processor, it implements the method as described in any one of claims 1 to 5.

Images