Sub-table Scenario Guide

Overview

Sub-table scenarios are designed to handle one-to-many data entry requirements. For example, in a purchase order, you need to enter multiple product details, each containing product name, price, quantity, and other fields. To efficiently manage data, orders and product details are typically stored in separate tables and linked through foreign key associations.

Applicable Scenarios

• Orders and product details
• Customers and contacts
• Projects and task lists
• Other one-to-many data relationships

Data Association Design

In sub-table scenarios, parent and child tables are associated through foreign keys:

Association Principles

• Child table stores foreign key: Create a foreign key field in the child table (such as order_id) that points to the _id of the parent table record
• Parent table is unaware: The parent table doesn't need to maintain child table relationships, keeping the data structure simple
• Unidirectional association: Child table → parent table unidirectional reference, avoiding the complexity of bidirectional maintenance

Association Configuration Example

The child table creates an orderId field that associates with the _id field of the parent order table to implement parent-child table association.

Deletion Rules

When creating a foreign key, the deletion rule can be set to "cascade". When data in the referenced table (order parent table) is deleted, records referencing that data in the current table (product details table) are also deleted.

Data Processing Flow

When submitting a page, the data processing order is as follows:

1. Process parent table first: Add or update parent table data to get the _id of the parent table record
2. Then process child table: Use the parent table _id as a foreign key to batch process create, update, and delete operations on the child table

Note

If related relationship fields are used, field format conversion needs to be handled separately in the cloud function (converting simple values to {_id: value} format).

Since multiple database interactions and data comparison logic are involved, child table data processing will use cloud functions for implementation. The frontend only needs to call the cloud function to complete data updates.

Sub-table Cloud Function Implementation

Processing Steps

Sub-table processing is divided into the following steps:

1. Data Splitting

The cloud function receives sub-table data and compares current data with original data to obtain data that needs to be created, updated, or deleted:

• Create data: Records without _id in current data
• Update data: Records with _id in current data where field values differ from original data
• Delete data: Records that exist in original data but not in current data

2. Batch Operations

• Batch creation: Collect all create data and use createMany to create at once
• Parallel update: Collect all update data and use Promise.all to execute multiple update operations in parallel
• Disassociate: Set the child table data foreign key field to null. If you need to delete data when canceling associations, this can also be implemented at this stage

3. Related Field Processing

Since related relationship fields have a data format of {_id: value} when creating, separate processing and conversion is required.

Cloud Function Parameter Description

The cloud function input parameter format is as follows:

Parameter	Type	Description
mainId	string	Parent table data object _id
subList	SubTableItem[]	Child table list containing all child table information to be processed
isProd	boolean	Environment identifier, true for production, false for pre-release

SubTableItem format is as follows:

Parameter	Type	Description
subName	string	Child table data model name
parentId	string	Foreign key field in child table that associates with parent table
subData	Object[]	Child table current data array
subOldData	Object[]	Child table original data array (for comparison)
relateField	string	Related relationship field key, multiple separated by commas

Complete Code Example

View complete cloud function code

const cloudbase = require('@cloudbase/node-sdk');

const app = cloudbase.init({
    env: cloudbase.SYMBOL_CURRENT_ENV,
});
const models = app.models;

/**
 * Process parent table and multiple child table data relationships
 * @param {Object} params - Function parameter object
 * @param {string} params.mainId - Parent table data object _id
 * @param {SubTableItem[]} params.subList - Child table list containing all child table information to be processed
 * @param {boolean} [params.isProd=false] - Environment identifier, true for production, false for pre-release
 * @returns {Promise<any>} Return processing result object
 */
exports.main = async function ({ mainId, subList, isProd = false }, context) {
    try {
        const envType = isProd ? 'prod' : 'pre';

        // Process create, update, and delete operations for each child table
        for (const subItem of subList) {
            const { subName } = subItem;

            console.log(`Starting to update child table ${subName} data`);
            await executeSubTableOperations(subItem, mainId, envType);
            console.log(`Finished updating child table ${subName}`);
        }

        return {
            success: true,
            message: 'Data processing successful',
        };
    } catch (error) {
        console.error('Data processing failed:', error);
        throw new Error(`Data processing failed: ${error.message}`);
    }
};

/**
 * Convert related fields to object format
 * @param {string} relateField - Related field name, multiple separated by commas
 * @param {Object} obj - Original data object
 * @returns {Object} Converted data object
 */
function transRelateField(relateField, obj) {
    if (!relateField) return obj;
    
    const fields = relateField.split(',');
    return fields.reduce((acc, field) => {
        if (acc[field]) {
            acc[field] = {
                _id: acc[field],
            };
        }
        return acc;
    }, { ...obj });
}

/**
 * Execute create, update, and delete operations on child table data
 * @param {SubTableItem} subItem - Child table information
 * @param {string} mainId - Parent table data object _id
 * @param {string} envType - Environment type
 * @returns {Promise<void>}
 */
async function executeSubTableOperations(subItem, mainId, envType) {
    const { subName, parentId, subData, subOldData, relateField } = subItem;
    const model = models[subName];

    // Parameter validation
    if (!model) {
        throw new Error(`Data model ${subName} does not exist`);
    }
    if (!Array.isArray(subData)) {
        throw new Error(`Child table data ${subName} must be in array format`);
    }

    // Collect data to be created and updated
    const addDataList = [];
    const updateList = [];
    const updatePromises = [];

    // 1. Iterate through current data, process create and update
    for (const item of subData) {
        if (!item._id) {
            // Create data
            addDataList.push(item);
        } else {
            // Check if update is needed (compare with old data)
            const oldItem = subOldData?.find((old) => old._id === item._id);
            if (oldItem) {
                const updateFields = {};
                // Compare all fields, record changed parts
                Object.keys(item).forEach((key) => {
                    if (key !== '_id' && item[key] !== oldItem[key]) {
                        updateFields[key] = item[key];
                    }
                });

                // If fields have changed, execute update
                if (Object.keys(updateFields).length > 0) {
                    const updateData = transRelateField(relateField, {
                        [parentId]: mainId,
                        ...updateFields,
                    });

                    const updatePromise = model
                        .update({
                            envType,
                            data: updateData,
                            filter: {
                                where: {
                                    _id: {
                                        $eq: item._id,
                                    },
                                },
                            },
                        })
                        .then((res) => ({
                            type: 'edit',
                            data: res,
                            _id: item._id,
                        }));

                    updateList.push(updateData);
                    updatePromises.push(updatePromise);
                }
            }
        }
    }

    // 2. Batch create data
    let addPromises = null;
    let dataWithParent = null;
    if (addDataList.length > 0) {
        // Add parent table association to create data
        dataWithParent = addDataList.map((item) =>
            transRelateField(relateField, { ...item, [parentId]: mainId })
        );
        addPromises = model.createMany({
            envType,
            data: dataWithParent,
        });
    }

    // 3. Process delete operations (disassociate)
    const newIds = subData.filter((item) => item._id).map((item) => item._id);
    const oldIds = subOldData?.map((item) => item._id) || [];
    const deleteIds = oldIds.filter((id) => !newIds.includes(id));
    let deletePromises = null;

    if (deleteIds.length > 0) {
        // Set foreign key to null to disassociate
        deletePromises = model.updateMany({
            envType,
            data: { [parentId]: null },
            filter: {
                where: {
                    _id: {
                        $in: deleteIds,
                    },
                },
            },
        });
    }

    // Log output
    console.log(`${subName} child table operation details:`);
    console.log('- Create data:', dataWithParent);
    console.log('- Update data:', updateList);
    console.log('- Disassociate:', deleteIds);

    // Execute all database operations
    const allPromises = [];
    if (addPromises) allPromises.push(addPromises);
    if (deletePromises) allPromises.push(deletePromises);
    allPromises.push(...updatePromises);

    if (allPromises.length > 0) {
        await Promise.all(allPromises);
    }
}

/**
 * @typedef {Object} SubTableItem - Child table information object
 * @property {string} subName - Child table data model name
 * @property {string} parentId - ID in child table that associates with parent table
 * @property {Object[]} subData - Child table current data array
 * @property {Object[]} subOldData - Child table original data array (for comparison)
 * @property {string} relateField - Related relationship field key, multiple separated by commas
 */

Core Logic Explanation

The executeSubTableOperations method is the core of child table data processing. The main logic is as follows:

1. Data comparison: Iterate through subData, determine whether to create or update based on whether _id exists
2. Field comparison: For update operations, compare new and old data field by field, only update changed fields
3. Batch operations: Use createMany for batch creation, use Promise.all for parallel updates
4. Delete processing: Find records to delete by comparing _id lists of new and old data, set their foreign key to null
5. Association conversion: Automatically convert specified fields to association object format {_id: value} through the transRelateField function

Performance Optimization

• Use createMany for batch creation to reduce database interaction frequency
• Use Promise.all to execute update operations in parallel for improved performance
• Only update changed fields to reduce unnecessary database writes

Example Scenario Development

Here we use a "Procurement Management" system as an example to demonstrate how to use sub-table functionality.

The data relationship is as follows:

Create Data Models

Go to MySQL Database/Data Models to create the following two data models:

Purchase Order (purchase_order)

Field Name	Type	Description
name	Text	Order name
amount	Number	Total amount
status	Enum	Order status

Note

The parent table doesn't need to maintain child table relationship fields.

Purchase Product Details (order_items)

Field Name	Type	Description
name	Text	Product name
price	Number	Product price
quantity	Number	Purchase quantity
order_id	Text	Belongs to order (foreign key)

Tip

The order_id field is a foreign key pointing to the _id of the parent table purchase_order.

This foreign key relationship needs to be created in the corresponding table at MySQL Database/Database Tables after creating the data models.

Create Application

Create a blank application and quickly generate "Table and Form Pages" through templates, selecting Purchase Order (purchase_order) as the data model.

Configure Edit Page

Step 1: Get Child Table Data

Enter the edit page. Since the form container is bound to the Purchase Order data source, child table data cannot be obtained directly. You need to manually call the "Built-in Data Table Query" method to get child table data.

Add a "Built-in Data Table Query" method on the current page, named getOrderDetail:

1. Configure data table: Select Purchase Product Details (order_items)
2. Trigger method: Select Manual trigger execution
3. Query conditions: Set Belongs to order equal to $w.page.dataset.params._id (parent table _id passed from URL)

4. In the edit page's Form Container Query success event, add execution of the getOrderDetail method. This way, after the parent table form data is loaded, the child table data will be automatically loaded.

Step 2: Configure Product List Component

Add an "Array Nested Form" component to the form as the product details list:

1. Change Nested form template to Object array (table)
2. Add child table fields:
   • Product name (name) - Text field
   • Purchase quantity (quantity) - Number input
   • Product price (price) - Number input
3. Set the array nested form value to get from getOrderDetail:

$w.getOrderDetail.data?.records?.length ? $w.getOrderDetail.data?.records : [{}]

Step 3: Configure Submit Event

Modify the Form Container's Submit event to call the cloud function to process child table data when parent table data is successfully updated:

The cloud function call code is as follows:

() => {
    $w.cloud.callFunction({
        name: "subTable-management", // Cloud function name
        data: {
            mainId: $w.page.dataset.params._id || event.detail.id,
            subList: [{
                subName: 'order_items',
                parentId: 'orderId',
                subData: $w.formArr2.value,
                subOldData: $w.getOrderDetail.data.records,
                relateField: 'gongyingshang'
            }]
        },
    })
}

Parameter description:

• mainId: Parent table _id. If in edit mode, use $w.page.dataset.params._id; if in create mode, get event.detail.id from the return value of the previous "Call data source method" step
• subList: Child table configuration array
  • subName: Child table data model name
  • parentId: Field name in child table that associates with parent table
  • subData: Child table current data array (formArr2 is the child table component ID, get through $w.formArr2.value)
  • subOldData: Child table original data array (for comparison)
  • relateField: Related relationship field key (optional, multiple separated by commas)

Advanced Usage

Multiple Child Tables Processing

If a parent table is associated with multiple child tables, you can add multiple child table configurations in subList:

await $w.cloud.callFunction({
    name: 'subTable-management',
    data: {
        mainId: mainId,
        subList: [
            {
                subName: 'order_items',
                parentId: 'order_id',
                subData: $w.form1.value?.order_items || [],
                subOldData: $w.form1.remoteValue?.order_items || [],
            },
            {
                subName: 'order_attachments', // Second child table
                parentId: 'order_id',
                subData: $w.form1.value?.attachments || [],
                subOldData: $w.form1.remoteValue?.attachments || [],
            }
        ],
        isProd: false
    },
});

Related Field Conversion

If the child table has other related fields (such as supplier, category, etc.), you need to use the relateField parameter:

{
    subName: 'order_items',
    parentId: 'order_id',
    subData: $w.form1.value?.order_items || [],
    subOldData: $w.form1.remoteValue?.order_items || [],
    relateField: 'supplier,category' // Fields to convert, multiple separated by commas
}

The cloud function will automatically convert these fields from simple values (such as "supplier_id_value") to association object format {_id: "supplier_id_value"}.

Example Template

For a complete example template, click here to download. After downloading, go to CloudBase Platform/WeDa Low-Code/Custom Template Management to import the template and start using it.

FAQ

1. Why doesn't the parent table need to maintain child table relationships?

Using a foreign key unidirectional association design, the child table points to the parent table through a foreign key, and the parent table doesn't need to maintain child table information. This approach allows you to:

• Simplify parent table data structure
• Avoid data redundancy
• Reduce data maintenance complexity

2. Is the delete operation physical deletion or logical deletion?

This solution uses a "disassociation" approach, setting the child table's foreign key field to null instead of physically deleting records. If physical deletion is needed, the delete processing section of the cloud function can be modified to use model.modelName.delete().