relation

Relationship Details

What is a Relationship

Relationship refers to the connection between data models, which associates data from different models through association fields, enabling logical connections between the data.

When creating a new relationship, the system will establish association fields in both data models to store the _id of the associated data.

⚠️ Note: In MySQL data models, relationship fields are associated through intermediate tables, therefore, they cannot be directly queried using SQL JOIN statements. It is necessary to use the association query method provided by the data model to obtain the associated data.

Common Relationship Scenarios:

• A student belongs to a class (Student → Class)
  • Student model association field: Class
  • Class model association field: List of Students
• An article has multiple comments (Article → Comments)
  • Article model association field: List of Comments
  • Comment model association field: Article
• A user has a profile (User → Profile)
  • User model association field: Profile
  • Profile model association field: User

Relationship Types

One-to-One (1:1)

A record can only be associated with one record from another model, and both sides maintain a unique correspondence.

Example: User ↔ Profile

// User model
{
    name: name: "Zhang San",
    profile: {
        _id: "profile_123"
    }
}

// Profile model association field: User
{
    _id: "profile_123",
    avatar: "avatar.jpg",
    bio: "bio: "Biography"
}

One-to-Many (1:N)

A record can be associated with multiple records from another model.

Example: Class ↔ Student

// Class model
{
    name: name: "Grade 1 (Class 1)",
    students: [
        { _id: "student_1" },
        { _id: "student_2" }
    ]
}

Many-to-One (N:1)

Multiple records can be associated with a single record from another model.

Example: Student ↔ Class

// Student model
{
    name: name: "Xiao Ming",
    class: {
        _id: "class_123"
    }
}

Many-to-Many (M:N)

Multiple records can be associated with multiple records from another model.

Example: Student ↔ Course

// Student model
{
    name: name: "Xiao Ming",
    courses: [
        { _id: "course_1" }, // Chinese
        { _id: "course_2" }  // Mathematics
    ]
}

Supported Database Types

Database Type	Supported Association Relationships
Database (Document-based)	one-to-one, one-to-many, many-to-one
Database (MySQL)	one-to-one, one-to-many, many-to-one, many-to-many
Self-hosted MySQL Database	one-to-one, one-to-many, many-to-one, many-to-many

Relationship Operations

Query Operations

💡 Note: For MySQL databases, association queries must use the query methods provided by the data model. Direct use of SQL JOIN statements is not supported.

Query results include associated data

// Query articles and their comments
const { data } = await models.post.get({
    filter: {
        where: {
            _id: { $eq: "post_123" }
        }
    },
    select: {
        _id: true,
        title: true,
        content: true,
        // Include associated comment data
        comments: {
            _id: true,
            content: true,
            createdAt: true
        }
    }
});

Filter based on association conditions

// Query articles with comments
const { data } = await models.post.list({
    filter: {
        relateWhere: {
            comments: {
                where: {
                    content: { $nempty: true }
                }
            }
        }
    },
    select: {
        _id: true,
        title: true,
        comments: {
            content: true
        }
    }
});

Create Operations

💡 Note: When adding association relationship fields, they must be passed in the format {_id: xxx}, where xxx is the _id of the associated data. No other fields need to be passed.

// Create a student and associate with class and course
const { data } = await models.student.create({
    data: {
        name: name: "Xiao Ming",
        age: 8,
        // Associate class (Many-to-One)
        class: {
            _id: "class_123"
        },
        // Associate multiple courses (Many-to-Many)
        courses: [
            { _id: "course_1" },
            { _id: "course_2" }
        ]
    }
});

Update Operations

💡 Note: When updating association relationship fields, they must be passed in the format {_id: xxx}, where xxx is the _id of the associated data. No other fields need to be passed.

// Update the student's class
const { data } = await models.student.update({
    filter: {
        where: {
            _id: { $eq: "student_123" }
        }
    },
    data: {
        class: {
            _id: "new_class_456"
        }
    }
});

Delete Operations

When configuring association relationships, different deletion behaviors can be set:

• Delete associated model data: When deleting the primary record, associated records are also deleted.
• Do not delete associated model data: Only the primary record is deleted, while associated records are retained.
• Prohibit deletion of data with existing associations: If associated records exist, deletion of the primary record is prohibited.

Best Practices

Design Principles

1. Properly select the association type: Select the appropriate association relationship type based on actual business requirements.
2. Avoid excessive nesting: Deeply nested queries may affect performance. It is recommended to limit nesting to no more than 3 levels.
3. Precise field selection: Query only the necessary associated fields to reduce the amount of data transmission.
4. Avoid circular references: Prevent circular associations between models that could result in query deadlocks.

Performance Optimization

1. Properly use indexes: Create indexes for associated fields to improve query efficiency.
2. Pagination queries: For large volumes of associated data, use pagination to avoid loading excessive data at once.
3. Cache policy: For frequently queried associated data, consider using a caching mechanism.

By properly utilizing relationships, flexible and efficient data models can be built to meet the requirements of complex business scenarios.