Detailed Explanation of Query Parameters

The cloud development data model provides powerful query capabilities, supporting complex query conditions, field selection, sorting, pagination, and relational queries. This article details various parameters and usage methods for data model query operations.

Overview of Query Parameters

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

Parameter	Type	Description	Required
`select`	`SelectParams`	Specifies the fields to return	No
`filter`	`FilterParams`	Query filter conditions	No
`orderBy`	`OrderByParams[]`	Sorting conditions	No
`pageSize`	`number`	Page size, maximum 200	No
`pageNumber`	`number`	Page number	No
`getCount`	`boolean`	Specifies whether to return the total count	No

Field Selection `select`

The select` parameter is used to specify the fields to be included 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 relationships to reduce the 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

Select specific fields by setting the field name to true:

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

Can query fields of associated models simultaneously:

const {
    data
} = await models.post.list({
    select: {
        _id: true,
        title: true,
        updatedAt: true,
        // Join query for 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 Filter `filter`

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

Basic Query `where`

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

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

Relational Query `relateWhere`

Relational 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: {}, // Query conditions for the main model
    },
    select: {
        $master: true,
        comments: {
            comment: true,
        },
    },
});

Composite Query Example

When you need to use AND/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	Equals	`{status: {$eq: "active"}}`
$ne	Not equal to	`{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 an array	`{category: {$in: ["tech", "news"]}}`
$nin	Not in an 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	Find records that do not contain the specified string value, case-sensitive	`{status: {$nsearch: "active"}}`
$nsearch_ci	Find 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 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: "desc", // By creation time in descending order
    }, ],
    select: {
        $master: true,
    },
});

Multi-field Sorting

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

Sorting direction:

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

Pagination Parameters

`pageSize` and `pageNumber`

Used to implement pagination query:

const {
    data
} = await models.post.list({
    filter: {
        where: {},
    },
    pageSize: 10, // 10 records per page
    pageNumber: 2, // Page 2 (starting from 1)
    getCount: true, // Get total count for calculating the total number of 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 that meet the criteria:

const {
    data
} = await models.post.list({
    filter: {
        where: {
            status: {
                $eq: "published"
            }
        },
    },
    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 criteria

Complete Query Sample

The following is a complete query sample 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"
                    } // Query only reviewed comments
                }
            }
        }
    },

    // Sort
    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 Details

Filter by Association

Using relateWhere allows filtering the primary model based on the 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 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,
        }
    }
});

Join Query Performance Optimization

Precise field selection: Select only the necessary associated fields.
Use association filtering appropriately: Avoid overly complex association conditions.
Pagination: 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,
        // Retrieve only the author's basic information
        author: {
            _id: true,
            name: true,
        },
        // Retrieve 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: querying 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 queries
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
        }
    }
});

// ⚠️ Caution: 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: true, // Get 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 succeeded:", data);
} catch (error) {
    console.error("Query failed:", error);
    // Handle error cases
}

By properly using these query parameters, you can achieve efficient and flexible data query operations to meet various complex business requirements.

Detailed Explanation of Query Parameters

Overview of Query Parameters​

Field Selection select​

Return All Fields of the Primary Model​

Return Specified Fields​

Return Associated Model Fields​

Query Filter filter​

Basic Query where​

Relational Query relateWhere​

Composite Query Example​

Query Operators​

Sorting orderBy​

Single Field Sorting​

Multi-field Sorting​

Pagination Parameters​

pageSize and pageNumber​

getCount​

Complete Query Sample​

Join Query Details​

Filter by Association​

Join Query Performance Optimization​

Best Practices​

1. Field Selection Optimization​

2. Query Conditions Optimization​

3. Pagination Handling​

4. Error Handling​