Methods, systems, media, and program products for generating and querying cross-form related fields

Abstract

This invention belongs to the field of low-code development platform technology and discloses a method, system, medium, and program product for generating and querying cross-form related fields. The method includes: storing inter-form relationships as independent, reusable relationship definitions; automatically generating read-only related fields on the source form in response to the creation of the relationship definitions, which do not occupy physical storage columns, and configuring the target form identifier and related conditions; performing paginated queries in response to source form query requests to obtain a set of records in the main table; when the selected column contains the related field, extracting the related key values ​​of the main table records according to the related conditions and establishing a mapping to the main table identifier; batch querying the target forms based on the key value set, grouping the results according to the target business fields, and backfilling them to the related fields of the corresponding main table records according to the mapping, forming a nested data structure for return. This invention improves the efficiency of cross-table data scheduling in low-code platforms under complex storage modes.

Description

Technical Field

[0001] This invention relates to the field of low-code development platform technology, and in particular to methods, systems, media, and program products for generating and querying cross-form related fields. Background Technology

[0002] The Low-Code Platform allows business users to define forms (i.e., data models) visually. Each form corresponds to a database table, and the fields in the form correspond to the columns in the table (i.e., one form, one table model). Users can drag and drop to configure fields, layouts, and lists in the form designer without writing any code, and the platform automatically handles data storage, retrieval, and display accordingly.

[0003] In practical enterprise applications, there are often numerous business relationships between different forms. For example, in the medical field, the patient basic information form is interconnected with more than ten other forms, such as medication records, diagnosis records, and test records, through a unique patient identifier field; one patient record corresponds to multiple medication records and multiple diagnosis records, forming a typical one-to-many (1-to-N) relationship. When browsing the patient list or viewing patient details, business personnel expect to be able to simultaneously see the data in the associated sub-tables and filter and sort it by sub-table fields.

[0004] To support relationships between forms (entities), existing low-code platforms and related data access technologies typically employ the following approaches: (1) Platform-built-in reference fields. Represented by Salesforce's Lookup relationship fields, Master-Detail relationship fields, and Mendix's entity association, this approach predefines an association field on the source entity and establishes the association through the system record ID of the associated record. The association key for this approach is fixed to the system record ID. (2) Platform-built-in join fields. A LEFT JOIN operation is performed on the source and target tables at the database SQL level, retrieving a field value from the target table and displaying it in the source table list. This method only supports first-level associations and can only retrieve a single field value from the target table. (3) Platform-built-in child table fields (such as EditableGrid). A parent-child relationship is established through the parent record's primary key, parentDataId, to display editable child table data. The association key for this approach is fixed to the parent record's primary key. (4) Association retrieval strategies of general object-relational mapping (ORM) frameworks. Frameworks such as Hibernate and MyBatis provide mechanisms like batch fetching, subselect fetching, and nested queries to load collections of associated objects of entities. This type of solution is geared towards pre-defined, statically defined object models with foreign key / primary key associations. The association relationships are fixedly described by the developer at compile time through mapping annotations or mapping files. (5) Native SQL queries. Write native SQL statements in the business process to complete arbitrary cross-table queries. This method is flexible, but it is detached from the form system, and the query results cannot be directly referenced and rendered by the list components of the form.

[0005] Regarding the requirement that business personnel can visually configure and display one-to-many related sub-table data in the form list / details of the low-code platform, and support association, filtering and sorting by business fields, the existing technologies mentioned above have the following defects: (1) The association key is limited by the system record identifier and cannot be based on any business field. The association keys of existing referenced fields, sub-table fields and ORM frameworks are fixed as system record identifier (Record ID), parent record primary key or foreign key. However, the actual association keys of enterprises are often business fields (such as patient unique identifier, medical visit serial number), and the data is usually imported from external systems and does not naturally hold the record identifier inside the platform; the existing technologies cannot support using any business field or even multiple business fields as association keys. (2) The configuration of the association relationship and the creation of the field are separated, resulting in high configuration costs and easy errors. In the existing platform, if business personnel want to display related sub-table data, they need to understand the underlying relationship logic first, and then manually create related fields one by one in the form and fill in a large number of parameters such as the relationship conditions and the target table; when the relationship changes or is deleted, they need to manually modify or delete the corresponding fields, which is very easy to cause configuration omissions or inconsistencies between fields and relationships. The relationship of the ORM framework is fixed by the programmer writing the mapping code during the development period, and is not open to the business personnel's runtime visual configuration. (3) It is impossible to express one-to-many relationship display in a reusable way. The JOIN field can only take the value of a single field in the target table, and cannot display multiple sub-table records in a list form; and the relationship configuration is a one-time configuration at the form level, and the same relationship cannot be reused by multiple forms and multiple layouts. (4) The pagination correctness of one-to-many relationship queries is difficult to guarantee. If LEFT JOIN is used directly to handle one-to-many relationships, a master record will expand into multiple rows in the result set due to matching multiple child records, resulting in incorrect pagination results in the master table. For example, if each page is expected to return 20 master records, but each master record is associated with an average of 3 child records, only about 7 master records can actually be returned per page. The existing platform's built-in association mechanism lacks a systematic solution to this expansion problem. (5) Relationships are prone to forming loops, leading to system anomalies. When the configuration personnel configure self-associations (the source form and the target form are the same), the query may fall into infinite recursion, causing the system to crash. The existing association mechanism for visual configuration by business personnel lacks verification protection for this. (6) Associations fail when the storage mode is switched. The same form in some low-code platforms may switch between two storage modes: a shared wide table (fields are mapped to the general column valueN) and an independent narrow table (fields are mapped to business semantic columns). The existing fixed association mechanism will fail due to column name changes after the storage mode is switched.

[0006] Therefore, how to provide a method, system, medium, and program product for automatic generation and querying of relationship-driven cross-form related fields in a low-code platform is an urgent problem to be solved. Summary of the Invention

[0007] This invention provides a method, system, medium, and program product for generating and querying cross-form related fields to solve the problems mentioned above in the prior art.

[0008] According to a first aspect of the present invention, a method for generating and querying cross-form related fields is provided.

[0009] In one embodiment, the method for generating and querying cross-form related fields includes: S1. In response to configuration operations, the relationship between the source form and the target form is stored as a relationship definition independent of the form; the relationship definition includes the source form identifier, the target form identifier, the relationship type, the association conditions, and the target filter conditions; S2. In response to the creation of the relationship definition, generate the associated fields on the source form based on the relationship definition. The associated fields do not occupy the physical storage columns of the data table corresponding to the source form and are read-only fields. The field configuration of the associated fields records the target form identifier and the association conditions. S3. In response to a data query request from the source form, perform a paginated query on the source form to obtain the main table record set for the current page. When the selected column requested in the query request includes a related field, extract the related key value from each main table record in the main table record set according to the source form business field of the related condition, and establish a mapping between the related key value and the main table record identifier. Perform a set query on the target form based on the set of related key values ​​to obtain the set query results. Group the set query results according to the target form business field of the related condition, and fill each result record back into the related field of the corresponding main table record according to the mapping, forming a data structure of a list of nested related sub-table records in the main table, and then return it.

[0010] According to a second aspect of the present invention, a system for generating and querying cross-form related fields is provided.

[0011] In one embodiment, a cross-form related field generation and query system includes: The relationship configuration layer is used to respond to configuration operations and store the association between the source form and the target form as a relationship definition independent of the form; the relationship definition includes the source form identifier, the target form identifier, the relationship type, the association conditions, and the target filter conditions; The field management layer is used to respond to the creation of relationship definitions and generate associated fields on the source form based on the relationship definitions. The associated fields do not occupy physical storage columns in the corresponding data table of the source form and are read-only fields. The field configuration of the associated fields records the target form identifier and the association conditions. The query execution layer responds to data query requests from the source form by performing paginated queries on the source form to obtain the main table record set for the current page. When the selected columns requested in the query request include related fields, it extracts the related key values ​​from each main table record in the main table record set according to the source form business fields of the related conditions, and establishes a mapping between the related key values ​​and the main table record identifiers. Based on the set of related key values, it performs a set query on the target form to obtain the set query results. The set query results are grouped according to the target form business fields of the related conditions, and each result record is backfilled to the related fields of the corresponding main table record according to the mapping, forming a data structure of a list of nested related sub-table records in the main table, which is then returned.

[0012] According to a third aspect of the present invention, a computer device is provided.

[0013] In some embodiments, the computer device includes a memory and a processor, the memory storing a computer program, and the processor executing the computer program to implement the steps of the method described above.

[0014] According to a fourth aspect of the present invention, a computer program product is provided.

[0015] In one embodiment, the computer program product stores a computer program that, when executed by a processor, implements the steps of the method.

[0016] According to a fifth aspect of the present invention, a computer-readable storage medium is provided.

[0017] In one embodiment, a computer program is stored on the computer-readable storage medium, which, when executed by a processor, implements the steps of the above method.

[0018] The technical solutions provided by the embodiments of the present invention may include the following beneficial effects: (1) More flexible association, covering real business scenarios. With the help of any business field association key, the association key can be any business field and supports the joint association of multiple business fields. This breaks through the limitations of existing reference-type fields, sub-table fields, and ORM frameworks that can only be associated with system record identifiers or foreign keys. It can cover the scenarios in real business of enterprises that are associated with business identifiers (such as the unique identifier of patients imported from external systems).

[0019] (2) Low configuration cost, low error rate, and reusability. Leveraging the automatic auto-automation mechanism of relationship-driven fields, business personnel only need to maintain one set of relationship definition metadata. The system automatically creates, updates, and deletes the corresponding read-only LOOKUP fields on the source form, eliminating the need for manual manipulation of form fields and fundamentally avoiding inconsistencies between field configuration and relationships. Furthermore, the relationship definition is independent of the form and can be reused by multiple forms and layouts. Compared to ORM frameworks, which require programmers to write mapping code during development, this invention provides visual configuration for business personnel during runtime.

[0020] (3) One-to-many pagination is correct and the number of queries is constant. With the help of pagination-preserving order on-demand batch back lookup, the pagination results of the main table are always correct, and there is no row expansion and pagination error problem caused by LEFT JOIN one-to-many; the sub-table is retrieved through a batch collection query, avoiding N+1 queries; the number of back lookups for a sub-table with a single associated field is constant at one, and the total number of back lookups for the entire page of sub-tables is only related to the number of the requested associated fields and is not related to the amount of data in the main table and sub-tables, ensuring query performance and predictability under large data volumes. Taking a three-level chain association (one main form associated with one intermediate form, and the intermediate form associated with seven sub-forms) as an example, the total number of back lookups for the sub-tables loading a whole page of data is always 1+7=8 times, and is not related to the number of records at each level.

[0021] (4) Easy to use and combinable capabilities. With the help of the dot chain syntax of referencing, filtering, sorting and multi-level nesting of related sub-table fields, related sub-table fields can be selected, filtered and sorted just like the fields in this form, and support multi-level chain association. They can be directly rendered by the list component and detail component of the form without having to write native SQL outside the form system.

[0022] (5) High robustness, portability, and zero intrusion on existing systems. By leveraging the self-association verification during relation configuration, infinite recursion during recursive loading is avoided; by using the adaptive column name resolution of the storage mode of any business field association key, the association capability remains effective after the form storage mode is switched, and it supports mixed scenarios where the source form and the target form are located in wide / narrow tables respectively; the association capability allows for replication between different deployment environments along with the seed data; and since the associated sub-table data of the LOOKUP field is only loaded when requested, the form query behavior of forms without configured or requested LOOKUP fields remains completely unchanged, without affecting the existing functions and performance of the platform.

[0023] It should be understood that the above general description and the following detailed description are exemplary and explanatory only, and are not intended to limit the invention. Attached Figure Description

[0024] 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.

[0025] Figure 1 This is a schematic diagram illustrating a method for generating and querying cross-form related fields according to an exemplary embodiment; Figure 2 This is an overall architecture diagram of a cross-form related field generation and query system, illustrated according to an exemplary embodiment. Figure 3 This is a flowchart illustrating the creation / update / deletion of the relationship definition and the auto-automation of the LOOKUP field according to an exemplary embodiment; Figure 4 This is a flowchart illustrating a one-to-many related data master table pagination, on-demand judgment, batch query of sub-tables, and grouped backfilling according to an exemplary embodiment. Figure 5 This is a schematic diagram illustrating dot-chain syntax for referencing sub-table fields and multi-level nested associations, according to an exemplary embodiment. Figure 6 This is a schematic diagram of the structure of a computer device according to an exemplary embodiment. Detailed Implementation

[0026] The following description and accompanying drawings fully illustrate specific embodiments described herein to enable those skilled in the art to practice them. Some portions and features of certain embodiments may be included in or replace portions and features of other embodiments. The scope of the embodiments herein includes the entire scope of the claims and all available equivalents thereof. The various embodiments described herein are presented in a progressive manner, with each embodiment focusing on its differences from other embodiments; similar or identical parts between embodiments can be referred to interchangeably.

[0027] The modules in the apparatus or system of this application can be implemented entirely or partially through software, hardware, or a combination thereof. These modules can be embedded in or independent of the processor in a computer device in hardware form, or stored in the memory of a computer device in software form, so that the processor can call and execute the operations corresponding to each module.

[0028] Where there is no conflict, the embodiments and features in the embodiments of the present invention can be combined with each other.

[0029] Figure 1 An embodiment of the cross-form related field generation and query method of the present invention is shown.

[0030] In this optional embodiment, the method for generating and querying cross-form related fields includes: Step S1: In response to the configuration operation, store the association between the source form and the target form as a relationship definition independent of the forms; the relationship definition includes the source form identifier, the target form identifier, the relationship type, the association conditions, and the target filter conditions, such as... Figure 3 As shown; It should be noted that the relationships between forms are abstracted into a metadata object (hereinafter referred to as the relationship definition) that is independent of specific forms and can be reused by multiple forms. When configuring the relationship definition, the system automatically materializes a read-only association field (LOOKUP field, also known as the association field, which is a different name for the same object) on the source form. This field only stores the configuration and does not occupy any physical storage columns. When querying source form data at runtime, the system loads the related one-to-many sub-table data as needed, based on the relationship definition referenced by the LOOKUP field. This is done by paginating the main table, then batch querying the sub-tables by business field values, and finally grouping and filling by association key. To ensure the universality of the technical solution, it is described using common terms such as low-code platform, form, and field. In specific implementation, the low-code platform can be, but is not limited to, the CETA platform.

[0031] In this optional embodiment, the association between the source form and the target form is stored as a relationship definition independent of the form. This includes: performing a validity check on the relationship definition, wherein the validity check includes that the relationship identifier, the source form, the target form, and at least one association condition are not empty; performing a self-association check on the relationship definition, wherein when the source form and the target form are completely identical, it is determined to be self-association and creation is rejected; persisting the validated relationship definition to the relationship definition table and invalidating the relationship definition cache of the corresponding business component; creating a LOOKUP field on the source form, writing the target form identifier, the target form business component identifier, the association condition, and the target filter condition from the relationship definition into the field configuration of the LOOKUP field, and setting the referenced business component and referenced form of the LOOKUP field to the business component identifier and form identifier of the target form, respectively. The identifier of the LOOKUP field is generated by converting the relationship identifier according to a preset rule (e.g., concatenating the relationship identifier with a fixed prefix and replacing hyphens with underscores), the name of the LOOKUP field is taken from the relationship name, the type of the LOOKUP field is marked as LOOKUP, and the physical storage column of the LOOKUP field is set to empty.

[0032] Furthermore, the relationship definition includes target filtering conditions for the target form, which are merged into the query conditions in a set query; and / or, when the association key field of the association condition is a reference-type field, the value portion is extracted from the value-tag structure set of that field for association matching. The relationship definition, as business component-level metadata, is incorporated into the seed data export and import process of the business component, and triggers the automatic generation of association fields on the source form after import; and / or, the relationship definition is cached at the business component level, and the corresponding cache is invalidated when the relationship definition is created, updated, or deleted.

[0033] Specifically, a relationship definition table is defined to store relationship definition metadata at the business component level. The table's fields must include at least: business component identifier, project identifier, relationship name, relationship identifier (unique within the business component), source form business component identifier, source form identifier, target form business component identifier, target form identifier, relationship type, association condition, target filter condition, and deletion flag. The source form business component identifier and the target form business component identifier can be different, thus supporting cross-business component associations; the relationship type can be one-to-many, many-to-one, or one-to-one; the association condition is stored as structured data (such as JSON), describing the correspondence between source form fields and target form fields, supporting one or more correspondences; the target filter condition is stored as structured data, used to apply additional filtering to the target form when loading sub-table data (e.g., only associating records with a valid status); the relationship identifier and the business component identifier constitute a unique constraint.

[0034] Furthermore, when creating or updating relationship definitions, the system performs self-association checks. If the source form and target form of a relationship definition are completely identical (i.e., the source form's business component identifier and the target form's business component identifier are the same, and the source form identifier and the target form identifier are the same), it is determined to be self-association, creation / update is rejected, and an error is displayed. This avoids getting stuck in infinite recursion due to self-association during multi-level nested recursive loading. As business component-level metadata, relationship definitions are included in the business component's seed data export and import process: when migrating business components across environments, relationship definitions are exported / imported along with the business components, and the automatic creation of the LOOKUP field is triggered after import, thus ensuring that association capabilities are replicable across different deployment environments.

[0035] In this optional embodiment, updating the relationship definition includes the following steps: Determine if the source form has changed; if the source form has changed, delete the LOOKUP field from the original source form and create a new LOOKUP field on the new source form; if the source form has not changed, update the field configuration (association conditions, target form, target filter conditions, etc.) of the original LOOKUP field to ensure that the field configuration is consistent with the updated relationship definition; if the LOOKUP field does not exist, create the LOOKUP field.

[0036] In one embodiment, when deleting a relationship definition, the corresponding LOOKUP field on the source form is first deleted, then a soft delete flag (set delete flag) is applied to the relationship definition, and the cache is invalidated.

[0037] It should be noted that, through the above-mentioned automated mechanism, the configuration personnel only need to maintain the metadata of the relationship definition. The creation, updating and deletion of the LOOKUP field are completed automatically by the system, without the need for manual operation of the form fields, which fundamentally avoids the problem of inconsistency between fields and relationships. Moreover, since the relationship definition is independent of the form and can be reused, the same relationship can be referenced by multiple forms.

[0038] Step S2: In response to the creation of the relationship definition, generate the associated field on the source form based on the relationship definition. The associated field does not occupy the physical storage column of the data table corresponding to the source form and is a read-only field. The field configuration of the associated field records the target form identifier and the association condition. In one embodiment, when the association condition includes two or more associated corresponding items, the set query constructs the main filter condition with the target form business field of the first associated corresponding item and the corresponding associated key value set, and adds a set filter condition for each of the remaining associated corresponding items. The set query is constrained by the logical AND relationship of each filter condition, thereby realizing the association based on the joint association of multiple business fields.

[0039] Specifically, the association conditions defined in the relation definition consist of a set of associated counterparts. Each associated counterpart includes a source form field identifier, a target form field identifier, and an operator (such as equality). The source / target field identifier points to any business field in the form, not a fixed system record identifier. When the association condition contains multiple associated counterparts, it indicates a multi-field joint association between the source form field and the target form field. At runtime, all associated counterparts must be satisfied simultaneously for the association to be considered a match. When resolving the association key field identifier to the underlying database column name at runtime, the column name is dynamically selected based on the storage mode of the source / target form. In narrow table mode, the business semantic column name (normalizedStorageField) of the field is used, and in wide table mode, the general storage column name (storageField, such as valueN) of the field is used. This ensures that the association mechanism remains effective after the form storage mode is switched and supports mixed scenarios where the source and target forms are located in different storage modes. When the association key field is a reference field (whose field value serialization result is a collection of value-tag structures), the "value" part is extracted for association matching; when the association key field is a regular field, its field value is directly used for association matching.

[0040] Furthermore, the specific execution steps of the association key mechanism at runtime are as follows: determine the associated corresponding items, read the association conditions defined in the relation definition referenced by the LOOKUP field, and obtain an ordered list P=[p1,p2,…,p...] of the associated corresponding items. m ], where each associated item p i =(srcField i ,tgtField i ,op i ), srcField i For the source form business field identifier, tgtField i Identify the target form's business fields and operations. i The operator is m; m is the number of associated items, m=1 indicates a single-field association, and m≥2 indicates a multi-field joint association.

[0041] Parse the source column names, for each srcField i Based on the current storage mode of the source form s Parsed as the underlying database column name srcCol i The parsing rules are denoted by the function col(field, mode): col(field,mode) = field.normalizedStorageField, when mode = narrow table mode; col(field,mode) = field.storageField (in the form of valueN), when mode = wide table mode.

[0042] Parse the target column names, for each tgtField i Based on the current storage mode of the target form t Similarly, according to col(tgtField) i ,mode t ) is parsed into the target column name tgtCol i ; due to mode s With mode t Each is determined independently, therefore the source form and the target form are allowed to be stored in different storage modes.

[0043] Value retrieval and normalization: Define the value retrieval function `val(record, field)`. For a record `record` in the source form, retrieve the value of the business field `field`. If `field` is a reference field (its value is a value-label structure set {(v1, l1), (v2, l2), ...}), then all its "value" components are taken to form the set {v1, v2, ...}. If `field` is a regular field, its scalar value is taken and treated as a single-element set {v}. If the value is empty (null or an empty string), it is recorded as an empty set. This indicates that the record is not matched in the corresponding associated item.

[0044] The composition of a multi-field composite key: For a source record `record`, its composite key `key(record)` under the relation definition is formed by combining the values ​​of each corresponding item in sequence, denoted as: key(record)=(val(record,srcField1),val(record,srcField2),…,val(record,srcField m )); That is, key(record) is a sequence of values ​​arranged in the order of the associated corresponding items (this sequence is further concatenated into a string in step S33 to serve as the mapping key). When any component is an empty set... If the record does not generate a valid composite key, it will not be included in the lookup. Two records are considered a match if and only if they are associated with each other by the operator op on all m corresponding items. i Each item is established (op in this embodiment) i All are equal in value, meaning that the components of the bond are equal.

[0045] The output of the above-mentioned association key mechanism execution steps serves as the input for step S33 (collecting association key values) and step S34 (batch lookup of sub-tables) in the following text.

[0046] Step S3: In response to the data query request from the source form, perform a paginated query on the source form to obtain the main table record set for the current page; when the selected column requested by the query request includes a related field, extract the related key value from each main table record in the main table record set according to the source form business field of the related condition, and establish a mapping between the related key value and the main table record identifier; perform a set query on the target form based on the set of related key values ​​to obtain the set query results; group the set query results according to the target form business field of the related condition, and fill each result record back into the related field of the corresponding main table record according to the mapping, forming a data structure of a list of nested related sub-table records in the main table, and then return it, such as... Figure 4 As shown.

[0047] In this optional embodiment, step S3 includes: Step S31: Perform an independent pagination query on the source form according to the filtering, sorting and pagination parameters of the query request to obtain the main table record set of the current page. This process does not perform a join operation with the target form. It should be noted that since this step does not involve a join with the target table, the pagination results of the main table are naturally correct and there is no row expansion issue.

[0048] Step S32: Determine whether the selected columns of the query request contain related fields; if not, return the current page's main table record set as the query result without triggering a sub-table query; if so, execute the subsequent steps. Specifically, the query checks whether the requested selection column contains a LOOKUP field identifier or its dot-chain subfields; subsequent steps S33 and S34 are only executed for the requested LOOKUP field, and no subtable query is triggered for LOOKUP fields that are not requested.

[0049] Step S33: Traverse all records in the main table on the current page, extract the related key values ​​according to the association conditions, establish the mapping between the related key values ​​and the main table record identifier list, and collect the deduplicated set of related key values. Specifically, the logical judgment and processing flow of this step is as follows: Initialize an empty mapping table M (associating key-value pairs with a list of record identifiers in the main table; the data structure is a hash table, where the key is a string concatenated from the associative key and the value is a list of record identifiers in the main table), and initialize an empty set of associative key-value pairs K (for batch lookup in subsequent step S34). Iterate through each record (let its main table record identifier be id) in the current page's main table record set.

[0050] For the current record, calculate its composite association key key(record) using the steps described above for association keys based on arbitrary business fields and multiple fields. Check: If any component of key(record) is an empty set... If the record is not found, skip it (it will not be included in the backtracking process) and proceed back to the previous step to process the next record. Concatenate and normalize the key(record) to obtain the mapping key k. The normalization rule is to concatenate the values ​​of each associated item in a fixed delimiter order: k=CONCAT(v1,SEP,v2,SEP,…,SEP,v m ); CONCAT(·) represents a string concatenation function, which concatenates the values ​​within the parentheses with the delimiter in sequence to form a single string. SEP is a preset delimiter that will not appear in the business field values ​​(e.g., ||). For example, if a record has two associated items with values ​​P001 and V12, then k = P001||V12. When an associated item has a multi-value set (in the case of a reference field), a mapping key is generated for each value in the set, meaning a source record may be registered under multiple mapping keys.

[0051] Update the mapping table by appending the record ID (id) from the main table to the list corresponding to M[k] (if key k does not exist in M, create an empty list first); simultaneously, add k to the associated key-value set K. This is logically equivalent to: Append the id to the end of the list M[k], denoted as M[k].append(id); Add k to the set K, denoted as K = K∪{k}.

[0052] Where append(·) means adding an element to the end of the list (M[k] is an ordered list that can contain duplicates); ∪ means the union operation of sets (K is a set without duplicate elements, used to remove duplicates and collect all related key values ​​to be retrieved).

[0053] After all records have been traversed, if K is an empty set (i.e., no record on the current page holds a valid association key), then this LOOKUP field will not trigger a subsequent sub-table lookup and will be returned directly; otherwise, the mapping table M and the association key-value set K will be output to step S34.

[0054] The purpose of the above mapping table M is to be able to locate the list of all main table record identifiers holding the key in O(1) time by reversing any associated key value k in the group backfilling stage of step S35, thereby supporting the situation of one sub-table record backfilling to multiple main table records (many-to-many aggregation).

[0055] Step S34: Using the collected set of related key values ​​as the filtering condition, perform a batch set query on the target form to obtain all candidate sub-table records; if the relation definition contains the target filtering condition, merge it into the query condition and execute it together. Specifically, the sub-table batch lookup uses the collected set of related key values ​​as conditions to construct a set (IN) query on the target form: the target field of the corresponding item in the main association is used as the filter column, and the set of related key values ​​is used as the filter value; for multi-field joint associations, a set filter condition is added to each of the remaining related items, and multiple conditions are constrained together by an "AND" relationship; if the target filter condition is configured in the relation definition, it is merged into the query conditions. This step retrieves all sub-table data related to all records in the main table of the current page through a single batch query, avoiding the N+1 problem caused by querying each record one by one.

[0056] Furthermore, the query implementation method and filtering rules for multi-field joint association are as follows: Decompose the set of values ​​for each associated item. Suppose the association condition contains m associated items p1…p m In step S33, during the collection phase, in addition to generating the joint mapping key, each associated corresponding item p is also processed. i Collect source value set V separately i ={All main table records on the current page are in the srcField i The non-empty values ​​on the} (used for deduplication).

[0057] Construct a set of filtering conditions for each associated counterpart. For the i-th associated counterpart, use the target field tgtField. i The parsed target column name tgtCol i For filtering columns, with a set of values ​​V i Construct a set (IN) of filter conditions F for the filter values. i Its semantics are tgtCol i ∈V i (That is, the values ​​in this column belong to set V) i ).

[0058] All filter conditions are combined using the logical AND operator. This connects the filter conditions of m sets using the logical AND operator, resulting in the filter conditions for the target form query being F1ANDF2AND…ANDF… m If the relation definition is configured with the target filtering condition F_extra, then further logical AND operations are performed to obtain F1AND…ANDF. m ANDF_extra.

[0059] The combination of database-side candidate recall and application-side exact matching, as described above, involves constructing set filtering conditions based on the value sets of each associated item and then combining them with a logical "AND". This is a two-stage exact matching mechanism. The first stage involves the database recalling candidate sub-table records that match each associated item in a single query, ensuring that sub-table records that truly satisfy the join condition (i.e., the join key is exactly equal to the join key of a main table record) are included in the candidate set without omission. The second stage, during the grouping and backfilling in step S35, involves an exact comparison of each candidate sub-table record with its join key k' against the mapping table M. Backfilling only occurs if k' matches M; otherwise, it is not performed. This mechanism allows multi-field joins to be completed with a single batch database query, avoiding the N+1 overhead of constructing join conditions for each main table record individually, and ensuring the accuracy of the join matching results through application-side join key comparison. Single-field joins (m=1) are a special case of the above rules: in this case, there is only one set filtering condition F1, and the candidate set is the exact matching result.

[0060] Step S35: For the returned candidate sub-table records, generate mapping keys according to the same association key value rules, match the corresponding main table record identifiers through mapping, group the sub-table records and fill them back into the association fields of the corresponding main table records to form a nested list structure and return it.

[0061] In this optional embodiment, extracting the associated key value and establishing the mapping from the associated key value to the main table record identifier list includes: reading the ordered list of associated corresponding items in the associated field configuration; for each main table record, extracting the values ​​of the corresponding source form business fields one by one according to the order of the associated corresponding items; if the field is a reference field, extracting the value component from its value-tag structure field value as the matching value; if the field value is empty, determining that the record does not generate a valid value in the associated corresponding item and does not participate in the subsequent sub-table lookup; concatenating all non-empty values ​​into a string-based joint mapping key according to the preset separator order, and establishing the mapping relationship from the associated key value to the main table record identifier using the joint mapping key as the key and the main table record identifier as the value.

[0062] In this optional embodiment, when the association condition includes two or more associated corresponding items, the set query adopts a two-stage exact matching mechanism. In step S34, the database recalls the candidate sub-table records that are matched on each associated corresponding item at once. In step S35, when filling in the group, each candidate sub-table record is exactly matched according to its composite key and mapping, and only the successfully matched sub-table records are filled in.

[0063] In this optional embodiment, the form has two storage modes: a shared wide table and an independent narrow table. When extracting the associated key value from the main table record in step S33 and constructing the filter column for the set query of the target form in step S34, the business field is dynamically parsed into the column name of the underlying data table according to the current storage mode of the source form and the target form, respectively. In the narrow table mode, the business semantic column name corresponding to the business field is used, and in the wide table mode, the general storage column name corresponding to the business field is used, so that the source form and the target form can support different storage modes.

[0064] In this optional embodiment, the query request supports referencing fields of the associated sub-table using dot-chain syntax with the associated field identifier and the target field identifier; when dot-chain syntax appears in the requested selection column, the collection query selects only the requested target field and automatically supplements the target form business fields involved in the association conditions; when dot-chain syntax appears in the requested filter conditions, the corresponding field filter conditions are appended to the collection query of the target form. When the target form itself is also configured with read-only associated fields, the process of performing a collection query on the target form recursively executes the query execution steps on the associated fields of the target form, thereby supporting multi-level chained data loading between the source form, the target form, and the secondary target form.

[0065] Specifically, grouping and backfilling involves grouping the sub-table query results according to the values ​​of the related fields in the target form; for each sub-table record, the values ​​of its related fields are extracted (if it is a reference field, the "value" part is extracted), and based on the mapping from the related key values ​​to the main table record identifier list established in step S33, the sub-table record is backfilled into the LOOKUP field of one or more corresponding main table records, forming a data structure of nested sub-table record lists within the main table records. The detailed processing flow of this step is as follows: Initialize an empty backfill mapping table R (a list of records from the main table to the child table, with a hash table data structure). Iterate through each child table record sub returned in step S34's batch backfill query. For the current child table record sub, calculate its concatenated mapping key k' according to the same normalization rule as described above: based on the target form fields tgtField1…tgtField in the relation definition's association conditions. m Each item in the sub field is retrieved using the val function, which extracts the "value" component from the referenced field, and then concatenated in order using the same delimiter SEP. k'=CONCAT(val(sub,tgtField1),SEP,…,SEP,val(sub,tgtField m )); That is, after retrieving the values ​​of each target associated field of the sub-table record, the CONCAT concatenation function and the SEP delimiter are used to generate the mapping key k', so as to ensure that the key of the sub-table record can be directly compared with the key of the main table record.

[0066] Query the mapping table M established in step S33 using k' as the key: retrieve M[k'], which is the list of identifiers of all main table records with the join key equal to k', IDs=M[k']. Determine: If the key k' does not exist in M ​​(IDs is empty), it means that the sub-table record has no matching entry in the main table on the current page; discard the sub-table record and return to the previous steps.

[0067] For each main table record identifier id in IDs, append the sub table record sub to the list corresponding to the backfill mapping table R[id]: For each id∈M[k']: append the sub table record sub to the end of the list R[id], denoted as R[id].append(sub).

[0068] Where id∈M[k'] represents the identifier of each main table record in the list M[k']; append(·) represents the operation of appending an element to the end of the list. This allows a sub-table record to be aggregated under multiple main table records at the same time (when multiple main table records hold the same association key).

[0069] After all records in the sub-table have been traversed, each record (identifier id) in the main table on the current page is traversed again: R[id] is retrieved (an empty list if it does not exist), and it is used as the field value of the LOOKUP field. This is then written to the corresponding field of the record, i.e., the value of the LOOKUP field in the record is set to R[id] (an empty list if the id does not match). This forms the final data structure of the list of sub-table records nested within the main table records corresponding to the LOOKUP field, and is then returned.

[0070] The overall time complexity of step S35 is O(n+s), where n is the number of records in the main table of the current page and s is the number of records in the sub-table obtained by back lookup: step S33 table building is O(n), traversing the sub-table and using hash mapping to aggregate is O(1) to O(s), and back writing is O(n). The whole process does not contain nested loops between the main table and the sub-table, thus avoiding the O(n×s) overhead of matching each record.

[0071] Through the above mechanism of pagination, on-demand triggering, batch lookup, and grouped backfilling: First, the pagination results of the main table are always correct and are not affected by one-to-many relationships; Second, for any number of main table records and any number of child table records in the current page, the number of child table lookups for a single LOOKUP field is constant (once), and the total number of child table lookups for the entire page is equal to the number of requested LOOKUP fields, regardless of the number of main table records or child table records, thus maintaining stable query performance under large data volumes.

[0072] Furthermore, it supports referencing specific fields of related sub-tables using a dotted-chain syntax of LOOKUP field identifier.target field identifier. In the select columns of the query request: when only the LOOKUP field identifier itself appears, a list of all records from the related sub-tables is loaded; when LOOKUP field identifier.target field identifier appears, only the specified field of the sub-table records is loaded; when the system performs batch sub-table back-queries on the target form, the requested target field, and automatically supplemented with the target fields involved in the association conditions (for grouping back-filling), are used as the select columns for the sub-table query.

[0073] It supports referencing related sub-table fields in filter conditions using dot-chain syntax (e.g., LOOKUP field identifier.target field identifier contains a certain value). The system adds the corresponding field's filter condition to the sub-table query of the target form, thereby retrieving only the sub-table records that meet the conditions and backfilling them.

[0074] When a dotted chain expression contains column identifiers of the form Lf (L for LOOKUP field identifier and f for target form field identifier) ​​in the filtering or sorting conditions of a query request, the system identifies the first segment L as a LOOKUP field, locates the relation definition and target form referenced by L, and identifies the remaining segment f as the field to be filtered / sorted on the target form.

[0075] When filtering conditions are pushed down to sub-tables for lookup, for filtering conditions targeting Lf (including operators such as equal to, contain, greater than, and range), the system converts these filtering conditions into filtering conditions on the target form field f during the batch lookup of sub-tables in step S34. These conditions are then combined with the existing association key set filtering conditions from step S34 using a logical AND operation and pushed down for execution. Thus, the sub-table lookup only retrieves sub-table records that are both associated with the current page's main table record and satisfy the f filtering condition; sub-table records that do not satisfy the f filtering condition are not included in subsequent grouping and backfilling. The effect is that the list of sub-table records after backfilling a main table record only contains sub-table records that satisfy the filtering condition. If all sub-table records associated with a main table record do not satisfy the filtering condition, the LOOKUP field of that main table record is backfilled as an empty list. Compared to first retrieving all associated sub-table records and then filtering them one by one at the application layer, pushing the filtering conditions down to the database for execution utilizes indexes on the target form fields and reduces the amount of data transferred from the database to the application layer.

[0076] The sorting can be achieved in the following two ways: 1. Sorting within sub-tables (specifying sorting direction for Lf): In step S34, during the sub-table lookup of the target form, the target field f is sorted (ORDER BY f) so that the lookup results are returned in order according to f; in step S35, during group backfilling, the sub-table records are appended to the sub-table record list of each main table record in the order of their return (i.e., the order of f is maintained within the list), so that the sub-table record list nested in each main table record is sorted according to f.

[0077] 2. Sort the main table by the aggregate value of the sub-table (optional): When it is necessary to sort the main table records by the aggregate value of Lf (such as the number of records in the associated sub-table, the maximum / minimum value of field f, etc.), in the main table pagination query stage of step S31, the system introduces a related aggregate subquery that calculates the aggregate value by grouping by the association key for the target table, and uses the result column of the aggregate subquery as the sort key of the main table query to participate in sorting and pagination; since this method still paginates the main table records themselves (the aggregate value is only used as the sorting basis column of the main table records and is not row-level joined with the sub-table records), there is no row expansion of one-to-many join, and the pagination result is still correct.

[0078] In collaboration with multi-level nesting, when f in Lf is itself a LOOKUP field of the next level (i.e., a multi-level dot chain L1.L2.f), the above filtering / sorting is pushed down level by level to the target form of the corresponding level according to the recursive lookup in step S34.

[0079] It supports multi-level nesting of dot-chain syntax. When the target form itself is also configured with a LOOKUP field, the batch lookup process of the target form's sub-tables will recursively process its own LOOKUP field, thus supporting multi-level chained data loading of source form, target form, and secondary target form (A to B to C).

[0080] When a query request references a LOOKUP field, but the corresponding relationship definition has been deleted (the corresponding field cannot be resolved, and the field identifier has the LOOKUP field prefix), the system throws a clear error message indicating that the relationship may have been deleted.

[0081] It should be further explained that when creating or updating a relationship definition, the system performs a self-association check on the relationship: when the source form and the target form of the relationship definition are exactly the same (i.e., the business component identifier of the source form is the same as the business component identifier of the target form, and the identifier of the source form is the same as the identifier of the target form), it is judged as self-association, the creation / update is rejected and an error is prompted, thereby avoiding infinite recursion due to self-association during multi-level nested recursive loading.

[0082] As metadata at the business component level, relationship definitions are incorporated into the seed data export and import process of business components. When migrating business components across environments, relationship definitions are exported / imported along with the business components, and the LOOKUP field is automatically created after import, thereby ensuring that the association capability can be replicated across different deployment environments.

[0083] In one specific embodiment, a single business field is associated with many others (patient - medication record). In a low-code platform in the healthcare field (taking CETA platform as an example), the patient management business component includes a basic patient information form (containing the business field "Patient Unique Identifier") and a medication record form (containing the business fields "Patient Unique Identifier," "Drug Name," and "Dosage"). Both patient and medication data are imported from external hospital systems and are linked together using the "Patient Unique Identifier" business field. Internal record identifiers within the platform are meaningless to external data. One basic patient information entry corresponds to multiple medication records, representing a one-to-many relationship.

[0084] During the configuration phase, the configuration personnel create a relationship definition in the relationship configuration interface of the patient management business component: named "Patient-Medication Association," with the relationship identifier "patient-medication-rel," the source form being patient basic information, the target form being medication records, the relationship type being one-to-many, and the association condition being {source field = patient unique identifier, target field = patient unique identifier, operator = equivalence}. The system performs validity checks and self-association checks on this relationship definition before persisting it. It also automatically creates a read-only field on the patient basic information form with the identifier "lkp_patient_medication_rel," the name "Patient-Medication Association," the type "LOOKUP," and an empty physical storage column. The field configuration records the target form as medication records and the association condition as the aforementioned equivalence association condition.

[0085] During the query phase (when the user browses the patient list and requests the display of medication names and dosages for medication records), the system first queries the patient basic information form according to the user's filtering, sorting, and pagination conditions, obtaining 20 patient records for the current page (corresponding to step S31). The system determines that the selected columns in this request contain the `lkp_patient_medication_rel` field, and then, based on the association conditions defined in the relation definition, extracts the unique patient identifier values ​​from these 20 patient records, establishing a mapping between the unique patient identifier values ​​and the patient record identifier list (corresponding to steps S32 and S33). The system constructs a set query on the medication record form, using the unique patient identifier as the filter column, the set of the aforementioned identifier values ​​as the filter value, and the medication name, dosage, and unique patient identifier as the selection columns, retrieving all medication records associated with all patients on the current page at once (compare with step S34). The system groups the medication records by the unique patient identifier and, based on the established mapping relationship, backfills each medication record into the `lkp_patient_medication_rel` field of the corresponding patient record, forming a nested data structure before returning it (corresponding to step S35).

[0086] The entire query process correctly paginated the main table (consistently 20 patients per page), and the sub-table was queried only once, regardless of the number of patients or medication records. In contrast, if the traditional method of directly LEFT JOINing patient basic information and medication records is used, with an average of 3 medication records associated with each patient, a page of 20 rows would actually only contain about 7 patients, resulting in a pagination error. If a method of querying medication records patient by patient is used, 1 + 20 = 21 queries are required, leading to an N+1 problem.

[0087] Furthermore, if the patient basic information form has been switched to an independent narrow table storage (its patient unique identifier field is mapped to the business semantic column yiyao_unique_patient_id), while the medication record form still uses a shared wide table storage (its patient unique identifier field is mapped to the general column value15).

[0088] In step S33, when retrieving the associated key value from the main table record, the system uses the narrow table mode of the source form and retrieves the value according to the business semantic column name yiyao_unique_patient_id. In step S34, when constructing a set query for the target form, the system uses the wide table mode of the target form and parses the target associated field into the general column value15 as the filter column. Therefore, even if the source and target forms are stored in different modes, the association remains valid. When the storage mode of a form subsequently changes, because the resolution of the associated key to the column name is performed dynamically at runtime according to the then-current storage mode, the association can continue correctly without modifying the relationship definition or the LOOKUP field.

[0089] In one specific embodiment, for a one-to-many association involving multiple business fields, the business component includes a medical record form (uniquely identified by the patient's unique identifier and medical record number) and multiple associated sub-tables (each containing the patient's unique identifier and medical record number fields). When the configuration personnel create the relationship definition, the association conditions include two corresponding items: {source field = patient's unique identifier, target field = patient's unique identifier, equivalent} and {source field = medical record number, target field = medical record number, equivalent}.

[0090] During the query, in the batch lookup phase of the sub-table (step S34), the system constructs a main set filter condition based on the first associated item and adds a set filter condition to the second associated item. The two conditions are used together by an "AND" relationship to constrain the target table query, thereby achieving a joint association of multiple business fields. During the group backfill (step S35), group mapping is performed using the joint association key.

[0091] In one specific embodiment, for chained associations (sub-tables such as patient, adverse event, symptoms / medication, etc.), the basic patient information in the business component is associated with adverse event records through the patient's unique identifier (one-to-many). The adverse event records are then associated with seven sub-tables such as symptoms, signs, medication, and tests through the adverse event identifier (each one-to-many).

[0092] The configuration personnel create the following relationships: Relationship Definition 1 (Patient to Adverse Event, automatically creating a LOOKUP field in the Patient form) and Relationship Definitions 2 to 8 (Adverse Event to each sub-table, automatically creating the corresponding LOOKUP field in the Adverse Event form).

[0093] During the query, the user queries the patient list and requests the adverse event LOOKUP field. When the system performs a batch lookup of sub-tables on the adverse event record form (step S34), it discovers that the form itself is configured with LOOKUP fields pointing to each sub-table. Therefore, it recursively performs batch lookups on each sub-table, thus returning multi-level nested data from patients to adverse events to each sub-table in a single request. The total number of sub-table lookups for loading a full page of data throughout the entire process is always 1 (adverse events) + 7 (seven sub-tables) = 8 times, regardless of the number of records at each level.

[0094] In one specific embodiment, for dot-chain field filtering, a user wants to display only patient-related data whose medication records contain amoxicillin in their drug names. The user specifies the filter condition in the query request using dot-chain syntax: {column=lkp_patient_medication_rel.drugName, operation=contains, value=amoxicillin}. The system appends this field filter condition to the sub-table lookup of the target medication record form, retrieving only medication records that meet the condition and backfilling them, thus achieving filtering of results based on the associated sub-table field.

[0095] In one specific embodiment, a comprehensive example of dot-chain selection columns, multi-level nesting, and filtering is as follows: Figure 5 As shown, source form A is a patient basic information form (the business field is the patient's unique identifier, which is materialized with a LOOKUP field lkp_adr pointing to the adverse event record form); target form B is an adverse event record form (the business fields are the patient's unique identifier and the adverse event identifier, which are materialized with LOOKUP fields pointing to several sub-forms C1 to Cn, such as lkp_symptom pointing to symptoms, lkp_drug pointing to medication, etc.); sub-form C is a form for symptoms, medication, etc. (the business field is the adverse event identifier). Relationship definition 1 associates A and B (one-to-many) through the patient's unique identifier, and relationship definitions 2 to n associate B and each Cn (one-to-many) through the adverse event identifier.

[0096] The query request requests three types of columns: first, business fields of the source form A itself (such as patientName); second, a dotted chain expression lkp_adr.eventDate, indicating that only the eventDate field of the records in subform B is retrieved; and third, a multi-level dotted chain expression lkp_adr.lkp_symptom.name, indicating that through nested steps from A to B to C, the name field of the records in subform C (symptoms) is retrieved. The query request requests a filter condition as a multi-level dotted chain expression {column=lkp_adr.lkp_drug.drugName, operation=contain, value=amoxicillin}, indicating that the drugName field is added as a filter during the third-level backtracking from A to B to C (medication), retrieving only medication records that meet the condition and backtracking them.

[0097] The system processes the request according to the above-mentioned data loading process. First, it performs a paginated query on the main table for form A (once); then it performs a batch collection query on form B referenced by lkp_adr (once); subsequently, it performs a batch collection query on the corresponding sub-form C for each of the requested LOOKUP fields (lkp_symptom, lkp_drug, etc.) on form B (once each), with the lkp_drug lookup appended with a filter condition that drugName contains amoxicillin. The final query request returns the following multi-level nested data structure (illustrated): Under a patient main table record (identified as 1001, patientName is Zhang San), the lkp_adr field is filled with a list of adverse event records; under each adverse event record (eventDate is 2025-03-15), the lkp_symptom field is filled with a list of symptom records (e.g., name is headache, name is nausea), and the lkp_drug field is filled with a list of medication records that meet the filtering conditions (e.g., drugName is amoxicillin). Throughout the process, the pagination results of the main table are always correct, and there is no row bloat caused by one-to-many joins; the total number of sub-table back-queries is equal to the number of requested LOOKUP fields, and is unrelated to the number of patients, adverse events, symptoms, or other records at each level.

[0098] In one specific implementation, regarding relationship deletion and automatic field cleanup, when an administrator deletes a patient-medication association relationship definition, the system first deletes the `lkp_patient_medication_rel` field from the patient's basic information form, then marks the relationship definition as deleted and invalidates the cache. Subsequently, if a query request still references the `LOOKUP` field of the deleted relationship, the system, when parsing the selected column, recognizes that the corresponding field no longer exists and its identifier has the `LOOKUP` field prefix, and throws a clear error message indicating that the inter-table relationship corresponding to that field may have been deleted.

[0099] It should be noted that existing mechanisms for association built into platforms such as SalesforceLookup / Master-Detail and Mendix entity association use system record identifiers as association keys, and the association fields need to be predefined and cannot be reused. This invention differs from the present invention, which uses any business field (including multiple field combinations) as the association key, and the association fields are automatically generated by relation definition metadata, and the relations can be reused.

[0100] Unlike the batch and subquery crawling of ORM frameworks such as Hibernate / MyBatis, where ORM relationships are statically defined by programmers during development through mapping code and are oriented towards foreign keys / primary keys, this solution allows business users to visually configure relationships at runtime, oriented towards any business field, and the system automatically generates read-only related fields on the form. The two solutions differ in their stage, users, related keys, and the materialization mechanism of relationships (fields).

[0101] Figure 2 An embodiment of the cross-form related field generation and query system of the present invention is shown.

[0102] In this optional embodiment, the cross-form related field generation and query system includes: The relationship configuration layer stores the association between the source form and the target form as a relationship definition independent of the form; the relationship definition includes the source form identifier, the target form identifier, the relationship type, the association conditions, and the target filter conditions; The field management layer is used to respond to the creation of relationship definitions and generate associated fields on the source form based on the relationship definitions. The associated fields do not occupy physical storage columns in the corresponding data table of the source form and are read-only fields. The field configuration of the associated fields records the target form identifier and the association conditions. The query execution layer responds to data query requests from the source form by performing paginated queries on the source form to obtain the main table record set for the current page. When the selected columns requested in the query request include related fields, it extracts the related key values ​​from each main table record in the main table record set according to the source form business fields of the related conditions, and establishes a mapping between the related key values ​​and the main table record identifiers. Based on the set of related key values, it performs a set query on the target form to obtain the set query results. The set query results are grouped according to the target form business fields of the related conditions, and each result record is backfilled to the related fields of the corresponding main table record according to the mapping, forming a data structure of a list of nested related sub-table records in the main table, which is then returned.

[0103] In this optional embodiment, the relationship configuration layer is also used to perform legality verification and self-association verification on the relationship definition, persist the verified relationship definition to the relationship definition table and invalidate the cache, create a LOOKUP field on the source form and write the configuration information of the relationship definition into the field configuration of the LOOKUP field.

[0104] In this optional embodiment, the field management layer is also used to: when updating a relationship definition, if the source form has not changed, synchronously update the field configuration of the associated fields; if the source form has changed, delete the original associated fields on the original source form and generate new associated fields on the new source form; when deleting a relationship definition, first delete the corresponding associated fields on the source form and then delete the relationship definition.

[0105] In this optional embodiment, the query execution layer is further configured to construct a main filter condition based on the target form business field and the corresponding set of related key values ​​of the first related item when the association condition includes two or more related items, and to add a set filter condition for each of the remaining related items; a two-stage exact matching mechanism is adopted, in which the database recalls candidate sub-table records at once in the first stage, and exact matching is performed during group backfilling in the second stage.

[0106] To facilitate understanding of the above technical solutions of the present invention, the following further explains the above technical solutions of the present invention from the perspective of architecture and principle. The system of the present invention is logically divided into three layers, as follows: The first layer is the relationship configuration layer, located in the platform's Business Component (PBC) management interface. Configuration personnel (business personnel) can visually create, edit, and delete relationship definitions in this layer. Relationship definitions are business component-level metadata, stored in the relationship definition table (form_entity_relation), and include source form identifier, target form identifier, relationship type, and association conditions. A single relationship definition can be reused by multiple layouts of multiple forms.

[0107] The second layer is the field management layer. When a relationship definition is created, updated, or deleted at the relationship configuration layer, this layer automatically creates, updates, or deletes the corresponding LOOKUP field on the source form. The LOOKUP field is a form-level field; its field configuration records the referenced target form and associated conditions, but its storage column is empty (does not occupy any physical data storage column), making it a read-only field.

[0108] The third layer is the query execution layer. When a user queries source form data, this layer identifies the LOOKUP fields involved in the query, and based on their reference relationships, executes the related data loading process of paginated query of the main table, batch query of the sub-table, and grouped backfilling. It also supports the selection, filtering, and sorting of sub-table fields through dot-chain syntax.

[0109] To improve performance, the relationship configuration layer caches relationship definitions by business component dimension, and invalidates the corresponding cache when a relationship definition is created, updated, or deleted, thereby ensuring that the query execution layer can obtain the relationship definition without repeatedly reading the database when loading related data.

[0110] In one embodiment, a computer device is provided, which may be a server, and its internal structure diagram may be as follows: Figure 6 As shown, the computer device includes a processor, memory, and a network interface connected via a system bus. The processor provides computing and control capabilities. The memory includes a non-volatile storage medium and internal memory. The non-volatile storage medium stores an operating system, computer programs, and a database. The internal memory provides an environment for the operation of the operating system and computer programs in the non-volatile storage medium. The database stores static and dynamic information data. The network interface communicates with external terminals via a network connection. When the computer program is executed by the processor, it implements the steps in the above method embodiments.

[0111] Those skilled in the art will understand that Figure 6 The structure shown is merely a block diagram of a portion of the structure related to the present invention and does not constitute a limitation on the computer device to which the present invention is applied. A specific computer device may include more or fewer components than those shown in the figure, or combine certain components, or have different component arrangements.

[0112] In addition, the present invention also provides a computer device, including a memory and a processor, wherein the memory stores a computer program, and the processor executes the computer program to implement the steps in the above method embodiments.

[0113] In addition, the present invention also provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the steps in the above method embodiments.

[0114] Those skilled in the art will understand that all or part of the processes in the methods of the above embodiments can be implemented by a computer program instructing related hardware. The computer program can be stored in a non-volatile computer-readable storage medium, and when executed, it can include the processes of the embodiments of the methods described above. Any references to memory, storage, databases, or other media used in the embodiments provided by this invention can include at least one of non-volatile and volatile memory. Non-volatile memory can include read-only memory (ROM), magnetic tape, floppy disk, flash memory, or optical storage, etc. Volatile memory can include random access memory (RAM) or external cache memory. By way of illustration and not limitation, RAM can be in various forms, such as static random access memory (SRAM) or dynamic random access memory (DRAM), etc.

[0115] This invention is not limited to the structures described above and shown in the accompanying drawings, and various modifications and changes can be made without departing from its scope. The scope of this invention is limited only by the appended claims.

Claims

1. A method for generating and querying cross-form related fields, characterized in that, include: S1. In response to configuration operations, store the relationship between the source form and the target form as a relationship definition independent of the form; The relationship definition includes the source form identifier, the target form identifier, the relationship type, the association conditions, and the target filter conditions; S2. In response to the creation of the relationship definition, generate the associated fields on the source form based on the relationship definition. The associated fields do not occupy the physical storage columns of the data table corresponding to the source form and are read-only fields. The field configuration of the associated fields records the target form identifier and the association conditions. S3. In response to the data query request of the source form, perform a paginated query on the source form to obtain the main table record set of the current page; when the selected column requested by the query request contains related fields, extract the related key values ​​from each main table record of the main table record set according to the source form business fields of the related conditions, and establish a mapping between the related key values ​​and the main table record identifiers. Perform a set query on the target form based on the set of associated key values ​​to obtain the set query results; group the set query results according to the target form business fields of the associated conditions, and fill each result record back into the associated field of the corresponding main table record according to the mapping, forming a data structure of a list of nested associated sub-table records in the main table and then returning it.

2. The method for generating and querying cross-form related fields according to claim 1, characterized in that, The step of storing the relationship between the source form and the target form as a relationship definition independent of the forms includes: Perform a validity check on the relationship definition, wherein the validity check includes that the relationship identifier, the source form, the target form, and at least one association condition are not empty; Perform self-association validation on the relationship definition, where if the source form and the target form are exactly the same, it is determined to be a self-association and creation is rejected; Persist the validated relationship definition to the relationship definition table and invalidate the relationship definition cache of the corresponding business component. Create a LOOKUP field on the source form. Write the target form identifier, target form business component identifier, association conditions, and target filter conditions from the relationship definition into the field configuration of the LOOKUP field. Set the referenced business component and referenced form of the LOOKUP field to the business component identifier and form identifier of the target form, respectively. The identifier of the LOOKUP field is generated by converting the relationship identifier according to a preset rule. The name of the LOOKUP field is taken from the relationship name. The type of the LOOKUP field is marked as LOOKUP. The physical storage column of the LOOKUP field is set to empty.

3. The method for generating and querying cross-form related fields according to claim 2, characterized in that, Updating the relationship definition includes the following steps: Determine if the source form has changed; If the source form changes, delete the LOOKUP field on the original source form and create a new LOOKUP field on the new source form; If the source form has not changed, update the field configuration of the original LOOKUP field to ensure that the field configuration is consistent with the updated relationship definition; if the LOOKUP field does not exist, create the LOOKUP field.

4. The method for generating and querying cross-form related fields according to claim 2, characterized in that, When deleting a relationship definition, first delete the corresponding LOOKUP field on the source form, and then perform a soft delete flag on the relationship definition.

5. The method for generating and querying cross-form related fields according to claim 1, characterized in that, When the association condition includes two or more associated corresponding items, the set query constructs the main filter condition with the target form business field of the first associated corresponding item and the corresponding associated key value set, and adds a set filter condition for each of the remaining associated corresponding items. The set query is constrained by the logical AND relationship of each filter condition, thereby realizing the association based on the combination of multiple business fields.

6. The method for generating and querying cross-form related fields according to claim 1, characterized in that, S3 includes: S31. Perform an independent pagination query on the source form according to the filtering, sorting and pagination parameters of the query request to obtain the main table record set of the current page. This process does not perform a join operation with the target form. S32. Determine whether the selected columns of the query request contain related fields; if not, return the current page's main table record set as the query result without triggering a sub-table query; if so, execute the subsequent steps. S33. Traverse all records in the main table on the current page, extract the related key values ​​according to the related conditions, establish a mapping between the related key values ​​and the main table record identifier list, and collect the deduplicated set of related key values. S34. Using the collected set of related key values ​​as the filtering condition, perform a batch set query on the target form to obtain all candidate sub-table records; if the relation definition contains the target filtering condition, merge it into the query condition and execute it together. S35. For the returned candidate sub-table records, generate mapping keys according to the same association key value rules, match the corresponding main table record identifiers through mapping, group the sub-table records and fill them into the association fields of the corresponding main table records, forming a nested list structure and then return it.

7. The method for generating and querying cross-form related fields according to claim 6, characterized in that, The step of extracting the associated key values ​​and establishing a mapping from the associated key values ​​to the main table record identifier list includes: Read the ordered list of associated items in the associated field configuration; for each main table record, extract the values ​​of the corresponding source form business fields one by one according to the order of the associated items; If the field is a reference field, the value component is extracted from the field value in its value-tag structure as the matching value; if the field value is empty, it is determined that the record does not generate a valid value for the corresponding associated item and will not participate in the subsequent sub-table lookup. All non-empty values ​​are concatenated into a string-based composite mapping key according to a preset delimiter. Using this composite mapping key as the key and the main table record identifier as the value, a mapping relationship is established between the associated key-value pair and the main table record identifier.

8. The method for generating and querying cross-form related fields according to claim 6, characterized in that, When the association condition contains two or more associated corresponding items, the set query adopts a two-stage exact matching mechanism. In step S34, the database recalls the candidate sub-table records that are matched for each associated corresponding item at once. In step S35, when filling in the group, each candidate sub-table record is exactly matched according to its composite key and mapping, and only the successfully matched sub-table records are filled in.

9. The method for generating and querying cross-form related fields according to claim 6, characterized in that, The form has two storage modes: a shared wide table and an independent narrow table. In step S33, when extracting the associated key value from the main table record, and in step S34, when constructing the filter column for the set query of the target form, the business field is dynamically parsed into the column name of the underlying data table according to the current storage mode of the source form and the target form, respectively. In the narrow table mode, the business semantic column name corresponding to the business field is used, and in the wide table mode, the general storage column name corresponding to the business field is used, so that the source form and the target form can support different storage modes.

10. The method for generating and querying cross-form related fields according to claim 6, characterized in that, The query request supports referencing fields of the associated sub-table using dot-chain syntax with the associated field identifier and the target field identifier; when dot-chain syntax appears in the requested selection column, the collection query selects only the requested target field and automatically supplements the target form business fields involved in the association conditions; when dot-chain syntax appears in the requested filter conditions, the corresponding field filter conditions are appended to the collection query of the target form. When the target form itself is also configured with read-only associated fields, the process of performing a collection query on the target form recursively executes the query execution steps on the associated fields of the target form, thereby supporting multi-level chained data loading between the source form, the target form, and the secondary target form.

11. A system for generating and querying cross-form related fields, characterized in that, include: The relationship configuration layer is used to respond to configuration operations and store the association between the source form and the target form as a relationship definition independent of the form; The relationship definition includes the source form identifier, the target form identifier, the relationship type, the association conditions, and the target filter conditions; The field management layer is used to respond to the creation of relationship definitions and generate associated fields on the source form based on the relationship definitions. The associated fields do not occupy physical storage columns in the corresponding data table of the source form and are read-only fields. The field configuration of the associated fields records the target form identifier and the association conditions. The query execution layer responds to data query requests from the source form by performing paginated queries on the source form to obtain the main table record set for the current page. When the selected columns requested in the query request include related fields, it extracts the related key values ​​from each main table record in the main table record set according to the source form business fields of the related conditions, and establishes a mapping between the related key values ​​and the main table record identifiers. Based on the set of related key values, it performs a set query on the target form to obtain the set query results. The set query results are grouped according to the target form business fields of the related conditions, and each result record is backfilled to the related fields of the corresponding main table record according to the mapping, forming a data structure of a list of nested related sub-table records in the main table, which is then returned.

12. The cross-form related field generation and query system according to claim 11, characterized in that, The relationship configuration layer is also used to perform legality and self-association validation on the relationship definition, persist the validated relationship definition to the relationship definition table and invalidate the cache, create a LOOKUP field on the source form and write the configuration information of the relationship definition into the field configuration of the LOOKUP field.

13. The cross-form related field generation and query system according to claim 11, characterized in that, The field management layer is also used to: when updating a relationship definition, if the source form has not changed, synchronously update the field configuration of the associated fields; if the source form has changed, delete the original associated fields on the original source form and generate new associated fields on the new source form; when deleting a relationship definition, first delete the corresponding associated fields on the source form and then delete the relationship definition.

14. The cross-form related field generation and query system according to claim 11, characterized in that, The query execution layer is also used to construct a main filter condition based on the target form business field and the corresponding set of related key values ​​of the first related item when the association condition includes two or more related items, and to add a set filter condition for each of the remaining related items; a two-stage exact matching mechanism is adopted, in which the database recalls candidate sub-table records at once in the first stage, and exact matching is performed in the second stage when the group is backfilled.

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

16. A computer program product, comprising a computer program, characterized in that, When the computer program is executed by a processor, it implements the steps of the method according to any one of claims 1 to 10.

Images