Detailed Explanation of Query Parameters

The Cloud Development data model provides a powerful query feature, supporting complex query conditions, field selection, sorting, pagination, and join queries. This article will detail the various parameters and usage methods of data model query operations.

Overview of Query Parameters

The query operations of the data model primarily include the following parameters:

Parameter	Type	Description	Required
envType	pre | prod	Specifies the data environment for the query. If not passed, defaults to production data. pre: Experience data, prod: Production data	No
select	SelectParams	Specifies the fields to return	No
filter	FilterParams	Query filter conditions	No
orderBy	OrderByParams[]	Sorting conditions	No
pageSize	number	Pagination size, maximum 200	No
pageNumber	number	Page number	No
getCount	boolean	Whether to return the total count	No

Specifying the Data Environment envType

The envType parameter specifies the data environment for the query, defaulting to production data. pre: Experience data, prod: Production data.

const {
    data
} = await models.post.list({
    envType: 'pre',
    filter: {
        where: {},
    },
    select: {
        $master: true,
    },
});

Field Selection select

The select parameter specifies the fields to include in the query results, supporting precise control over both primary model fields and associated model fields.

✅ It is recommended to select only the required fields and relationships to reduce response data size and improve query speed.

Return all fields of the primary model

Using $master: true returns all fields of the primary model:

const {
    data
} = await models.post.get({
    select: {
        $master: true, // Query all fields of the primary model.
    },
    filter: {
        where: {
            _id: {
                $eq: _id, // It is recommended to pass the _id as data identifier for operations.
            },
        },
    },
});

console.log(data);
// {
//     "owner": "Anonymous(95fblM7nvPi01yQmYxBvBg)",
//     "createdAt": 1717488585078,
//     "createBy": "Anonymous(95fblM7nvPi01yQmYxBvBg)",
//     "updateBy": "Anonymous(95fblM7nvPi01yQmYxBvBg)",
//     "_openid": "95fblM7nvPi01yQmYxBvBg",
//     "_id": "e2764d2d665ecbc9024b058f1d6b33a4",
//     "title": "Hello, world👋",
//     "body": "Article Content...",
//     "slug": "hello-world",
//     "updatedAt": 1717490751944
// }

Return specified fields

By specifying the field name as true to select specific fields:

const {
    data
} = await models.post.list({
    select: {
        _id: true,
        title: true,
        updatedAt: true,
    },
    filter: {
        where: {},
    },
    getCount: true,
});

console.log(data);
// {
//     "records": [
//         {
//             "_id": "e2764d2d665ecbc9024b058f1d6b33a4",
//             "title": "Hello, world👋",
//             "updatedAt": 1717492882289
//         }
//         // ... more records
//     ],
//     "total": 51
// }

Return fields of associated models

Can query fields of associated models simultaneously:

const {
    data
} = await models.post.list({
    select: {
        _id: true,
        title: true,
        updatedAt: true,
        // Query associated comment data
        comments: {
            _id: true,
            createdAt: true,
            comment: true,
        }, // You can also directly pass true to return all fields in the comments.
    },
    filter: {
        where: {},
    },
    getCount: true,
});

console.log(data);
// {
//     "records": [
//         {
//             "_id": "9FSAHWM9VV",
//             "title": "Bonjour le Monde👋",
//             "updatedAt": 1718096503886,
//             "comments": [
//                 {
//                     "_id": "9FSAJF3GLG",
//                     "createdAt": 1718096509916,
//                     "comment": "This is a comment"
//                 }
//             ]
//         }
//         // ... more records
//     ],
//     "total": 2
// }

Query Filtering filter

The filter parameter is used to specify query conditions and supports two methods: basic query and associated query.

Basic Queries where

Basic queries are used to filter the fields of the main model:

const {
    data
} = await models.post.list({
    filter: {
        where: {
            // Title contains "world"
            title: {
                $search: "world",
            },
            // Body not empty
            body: {
                $nempty: true,
            },
        },
    },
    select: {
        $master: true,
    },
});

Association Query relateWhere

Associated queries are used to filter based on the conditions of associated models:

const {
    data
} = await models.post.list({
    filter: {
        // Query articles with comments
        relateWhere: {
            comments: {
                where: {
                    comment: {
                        $nempty: true, // Comment content is not empty
                    },
                },
            },
        },
        where: where: {}, // Main model query condition
    },
    select: {
        $master: true,
        comments: {
            comment: true,
        },
    },
});

Composite Query Example

When AND/OR relationships are required, you can use the $and and $or operators in the where parameter.

If you use the $and and $or operators, you need to place them in an array:

const {
    data
} = await models.post.list({
    filter: {
        where: {
            $and: [{
                    // title contains 'technology' or 'tutorial'
                    $or: [{
                            title: {
                                $search: "Technology"
                            }
                        },
                        {
                            title: {
                                $search: "Tutorial"
                            }
                        }
                    ]
                },
                {
                    // Creation time is within the last 30 days
                    createdAt: {
                        $gte: Date.now() - 30 * 24 * 60 * 60 * 1000
                    }
                },
                {
                    // Status is published
                    status: {
                        $eq: "published"
                    }
                }
            ]
        },
    },
    select: {
        _id: true,
        title: true,
        createdAt: true,
        status: true,
    },
});

Query Operator

operator	description	example
$eq	equal	{status: {$eq: "active"}}
$ne	not equal	{status: {$ne: "deleted"}}
$gt	greater than	{score: {$gt: 80}}
$gte	greater than or equal to	{age: {$gte: 18}}
$lt	less than	{price: {$lt: 100}}
$lte	less than or equal to	{discount: {$lte: 0.5}}
$in	in array	{category: {$in: ["tech", "news"]}}
$nin	not in array	{status: {$nin: ["deleted", "banned"]}}
$and	logical AND	{$and: [{age: {$gte: 18}}, {status: {$eq: "active"}}]}
$or	logical OR	{$or: [{priority: {$eq: "high"}}, {urgent: {$eq: true}}]}
$search	fuzzy search, case-sensitive	{status: {$search: "active"}}
$search_ci	fuzzy search, case-insensitive	{status: {$search_ci: "active"}}
$nsearch	Search for records not containing the specified string value, case-sensitive	{status: {$nsearch: "active"}}
$nsearch_ci	Search for records not containing the specified string value, case-insensitive	{status: {$nsearch_ci: "active"}}
$empty	is null	{status: {$empty: true}}
$nempty	is not null	{status: {$nempty: true}}

Sorting orderBy

The orderBy parameter is used to specify the sorting order of query results and supports multi-field sorting (up to 3 fields).

Single Field Sorting

const {
    data
} = await models.post.list({
    filter: {
        where: {},
    },
    orderBy: [{
        createdAt: createdAt: "desc", // Sort by creation time in descending order
    }, ],
    select: {
        $master: true,
    },
});

Multi-field Sorting

const {
    data
} = await models.post.list({
    filter: {
        where: {},
    },
    orderBy: [{
            featured: featured: "desc", // First sort by recommendation status in descending order
        },
        {
            createdAt: createdAt: "desc", // Then sort by creation time in descending order
        },
        {
            title: title: "asc", // Finally sort by title in ascending order
        },
    ],
    select: {
        $master: true,
    },
});

Sorting direction:

• "asc": Ascending order
• "desc": Descending order

Pagination Parameters

pageSize and pageNumber

Used to implement pagaged queries:

const {
    data
} = await models.post.list({
    filter: {
        where: {},
    },
    pageSize: pageSize: 10, // 10 records per page
    pageNumber: pageNumber: 2, // Page 2 (starting from 1)
    getCount: getCount: true, // Obtain the total count to calculate the total number of pages
    select: {
        $master: true,
    },
});

console.log(data);
// {
//     "records": [...], // 10 records on page 2
//     "total": 156      // Total number of records
// }

// Calculate the total number of pages
const totalPages = Math.ceil(data.total / 10);

getCount

Controls whether to return the total number of records matching the criteria:

const {
    data
} = await models.post.list({
    filter: {
        where: {
            status: {
                $eq: "published"
            }
        },
    },
    getCount: getCount: true, // Returns the total field when set to true
    select: {
        _id: true,
        title: true,
    },
});

console.log(data.total); // Total number of records matching the criteria

Complete Query Example

Below is a complete query example containing all parameters:

const {
    data
} = await models.post.list({
    // Field Selection
    select: {
        _id: true,
        title: true,
        excerpt: true,
        createdAt: true,
        updatedAt: true,
        author: {
            _id: true,
            name: true,
            avatar: true,
        },
        comments: {
            _id: true,
            content: true,
            createdAt: true,
        },
    },

    // Query conditions
    filter: {
        where: {
            $and: [{
                    status: {
                        $eq: "published"
                    }
                },
                {
                    $or: [{
                            title: {
                                $search: "Technology"
                            }
                        },
                        {
                            tags: {
                                $in: ["Frontend", "Backend"]
                            }
                        }
                    ]
                },
                {
                    createdAt: {
                        $gte: Date.now() - 7 * 24 * 60 * 60 * 1000 // last 7 days
                    }
                }
            ]
        },
        relateWhere: {
            comments: {
                where: {
                    status: {
                        $eq: "approved"
                    } // Only query approved comments
                }
            }
        }
    },

    // Sorting
    orderBy: [{
            featured: "desc"
        },
        {
            createdAt: "desc"
        }
    ],

    // Pagination
    pageSize: 20,
    pageNumber: 1,
    getCount: true,
});

console.log("Query result:", data.records);
console.log("Total records:", data.total);
console.log("Total pages:", Math.ceil(data.total / 20));

Join Query Detailed Explanation

Filter by Relationship

Use relateWhere to filter the main model based on the associated model's conditions:

// Query articles with specific comments
const {
    data
} = await models.post.list({
    filter: {
        relateWhere: {
            // Articles must have comments
            comments: {
                where: {
                    $and: [{
                            content: {
                                $nempty: true
                            }
                        }, // Comment content is not empty
                        {
                            status: {
                                $eq: "approved"
                            }
                        }, // Comment approved
                        {
                            rating: {
                                $gte: 4
                            }
                        } // Comment rating >= 4
                    ]
                }
            },
            // Articles must have tags
            tags: {
                where: {
                    name: {
                        $in: ["Technology", "Tutorial", "Sharing"]
                    }
                }
            }
        },
        where: {
            status: {
                $eq: "published"
            }
        }
    },
    select: {
        $master: true,
        comments: {
            content: true,
            rating: true,
            status: true,
        },
        tags: {
            name: true,
        }
    }
});

Join Query Performance Optimization

1. Precise Field Selection: Select only the required associated fields
2. Proper Use of Relational Filtering: Avoid overly complex relational conditions
3. Pagination: Employ pagination for large volumes of associated data

// Optimization example: Only obtain necessary associated data
const {
    data
} = await models.post.list({
    filter: {
        where: {
            status: {
                $eq: "published"
            }
        }
    },
    select: {
        _id: true,
        title: true,
        excerpt: true,
        // Obtain only the basic information of the author
        author: {
            _id: true,
            name: true,
        },
        // Obtain only the latest 3 comments
        comments: {
            _id: true,
            content: true,
            createdAt: true,
        }
    },
    orderBy: [{
        createdAt: "desc"
    }],
    pageSize: 10,
    pageNumber: 1,
});

Best Practices

1. Field Selection Optimization

// ❌ Not recommended: Query all fields
const {
    data
} = await models.post.list({
    select: {
        $master: true
    },
    filter: {
        where: {}
    }
});

// ✅ Recommended: Select only the required fields
const {
    data
} = await models.post.list({
    select: {
        _id: true,
        title: true,
        excerpt: true,
        createdAt: true,
    },
    filter: {
        where: {}
    }
});

2. Query Conditions Optimization

// ✅ Recommended: Use indexed fields for querying
const {
    data
} = await models.post.list({
    filter: {
        where: {
            _id: {
                $eq: "specific-id"
            }, // Primary key query, best performance
            status: {
                $eq: "published"
            }, // indexed field
            createdAt: {
                $gte: timestamp
            } // time range query
        }
    }
});

// ⚠️ Note: Fuzzy search has poor performance; use with caution
const {
    data
} = await models.post.list({
    filter: {
        where: {
            title: {
                $search: "keyword"
            } // fuzzy search, recommended to be used in conjunction with other conditions
        }
    }
});

3. Pagination Handling

// ✅ Recommended: Reasonable pagination size
const pageSize = 20; // Recommended range: 10-50
const pageNumber = 1;

const {
    data
} = await models.post.list({
    filter: {
        where: {}
    },
    pageSize,
    pageNumber,
    getCount: getCount: true, // Obtain the total count for pagination calculation
    select: {
        _id: true,
        title: true,
        createdAt: true,
    }
});

// Calculate pagination information
const totalPages = Math.ceil(data.total / pageSize);
const hasNextPage = pageNumber < totalPages;
const hasPrevPage = pageNumber > 1;

4. Error Handling

try {
    const {
        data
    } = await models.post.list({
        filter: {
            where: {
                status: {
                    $eq: "published"
                }
            }
        },
        select: {
            _id: true,
            title: true,
        },
        pageSize: 10,
        pageNumber: 1,
        getCount: true,
    });

    console.log("Query successful:", data);
} catch (error) {
    console.error("Query failed:", error);
    // Handle error cases
}

By properly utilizing these query parameters, efficient and flexible data query operations can be achieved to meet various complex business requirements.