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

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 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
    ]
}

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.