select

Detailed Explanation of Query Parameters

Cloud development data models provide powerful query capabilities, supporting complex query conditions, field selection, sorting, pagination, and relational queries. This article offers a detailed explanation of various parameters and usage methods for data model query operations.

This article applies to Web SDK, Node.js SDK. For other SDKs, please refer to corresponding SDK documentation link.

Query Parameters Overview

The query operations of data models mainly include the following parameters:

Parameter	Type	Description	Required
envType	pre | prod	Specifies the data environment for queries; defaults to production data if not provided. pre: trial data, prod: production data	No
select	SelectParams	Specifies the fields to return	No
filter	FilterParams	Filter conditions	No
orderBy	OrderByParams[]	Sorting conditions	No
pageSize	number	Page size, maximum 200, default is 10	No
pageNumber	number	Page number	No
getCount	boolean	Whether to return the total count	No

Specify the Data Environment envType

The envType parameter specifies the data environment for queries; it defaults to production data. pre: trial 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, enabling precise control over both primary model fields and associated model fields.

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

Return all fields of the primary model

Using $master: true will return 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 setting the field name to 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 associated model fields

Fields of associated models can be queried 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

filter parameter is used to specify query conditions, supporting both basic queries and associated queries.

Basic Query where

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

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

Associated Query relateWhere

Associated queries are used to filter based on 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: {}, // Query conditions for the main model
    },
    select: {
        $master: true,
        comments: {
            comment: true,
        },
    },
});

Compound Query Example

When you need to use AND or OR relationships, 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: [{
                    // The title contains "technology" or "tutorial"
                    $or: [{
                            title: {
                                $search: "technology"
                            }
                        },
                        {
                            title: {
                                $search: "tutorial"
                            }
                        }
                    ]
                },
                {
                    // Creation time 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 Operators

Operator	Description	Example
$eq	equal	{status: {$eq: "active"}}
$neq	not equal	{status: {$neq: "deleted"}}
$ne	Same as $neq, compatible syntax	{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	Finds records that do not contain the specified string value, case-sensitive	{status: {$nsearch: "active"}}
$nsearch_ci	Finds records that do not contain the specified string value, case-insensitive	{status: {$nsearch_ci: "active"}}
$empty	Data is null	{status: {$empty: true}}
$nempty	Data is not null	{status: {$nempty: true}}

Sorting orderBy

The orderBy parameter is used to specify the sorting method for query results, supporting 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", // Sort by featured status in descending order first
        },
        {
            createdAt: createdAt: "desc", // Then sort by creation time in descending order
        },
        {
            title: title: "asc", // Finally sort by title in ascending order
        },
    ],
    select: {
        $master: true,
    },
});

Sort Order:

• "asc": Sort in ascending order
• "desc": Sort in descending order

Pagination Parameters

pageSize and pageNumber

Used for implementing pagination 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 total count to calculate total pages
    select: {
        $master: true,
    },
});

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

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

getCount

Controls whether to return the total number of records meeting the conditions:

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

console.log(data.total); // Total number of records meeting the conditions

Complete Query Example

Here is a complete query example with 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 reviewed 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));

Association Query Detailed Explanation

Filter by Association

Using relateWhere allows filtering the primary model based on conditions of associated models:

// 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 is reviewed
                        {
                            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,
        }
    }
});

Association Query Performance Optimization

1. Precise field selection: Select only the necessary associated fields.
2. Appropriate use of association filtering: Avoid overly complex association conditions.
3. Pagination processing: Use pagination for large volumes of associated data.

// Optimization example: Retrieve only necessary associated data
const {
    data
} = await models.post.list({
    filter: {
        where: {
            status: {
                $eq: "published"
            }
        }
    },
    select: {
        _id: true,
        title: true,
        excerpt: true,
        // Obtain only the author's basic information
        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: Only select the needed fields.
const {
    data
} = await models.post.list({
    select: {
        _id: true,
        title: true,
        excerpt: true,
        createdAt: true,
    },
    filter: {
        where: {}
    }
});

2. Query Condition Optimization

// ✅ Recommended: Query using indexed fields.
const {
    data
} = await models.post.list({
    filter: {
        where: {
            _id: {
                $eq: "specific-id"
            }, // Primary key query, best performance
            status: {
                $eq: "published"
            }, // Index 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 with other conditions
        }
    }
});

3. Pagination Handling

// ✅ Recommended: Reasonable page 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 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.