Relationship Details

About Relationships

Relationships refer to the connections between data models, linking data from different models through association fields. For example:

• A student belongs to a class (Student → Class)
• An article has multiple comments (Article → Comment)
• User has a profile (User → Profile)

Types of Relationships

One-to-One (1:1)

One record can only be associated with one record from another model.

Example: User ↔ Profile

// User Model
{
    name: "Zhang San",
    profile: {
        _id: "profile_123"
    } // Associate Profile
}

// Profile Model  
{
    _id: "profile_123",
    avatar: "avatar.jpg",
    bio: "Biography"
}

One-to-Many (1: N)

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

Example: Class ↔ Student

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

Many-to-One (N:1)

Multiple records are associated with one record of another model.

Example: Student ↔ Class

// Student Model
{
    name: "Xiao Ming",
    class: {
        _id: "class_123"
    } // Multiple students belong to the same class
}

Many-to-Many (M: N)

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

Example: Student ↔ Course

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

Supported Database Types

Database Type	Supported Relationships
Cloud database (Document-based)	One-to-One, One-to-Many, Many-to-One
Cloud 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

CRUD

Query

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
                    } // Comment content is not empty
                }
            }
        }
    },
    select: {
        _id: true,
        title: true,
        comments: {
            content: true
        }
    }
});

Create

Establish Association During Creation

Association fields need to be passed in the format {_id:xxx}, where xxx is the _id of the associated data.

// Create students and associate with classes and courses
const {
    data
} = await models.student.create({
    data: {
        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

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

Delete

When configuring associations, different deletion behaviors can be set:

• Delete associated model data: When deleting the primary record, simultaneously delete associated records.
• Do not delete associated model data: Only delete the primary record, retain associated records.
• Disallow deletion of data with existing associations: If associated records exist, prohibit deletion of the primary record.

Best Practices

1. Design relationships appropriately: Select the appropriate relationship type based on business requirements
2. Avoid overly deep nested queries: impacts query performance
3. Use precise field selection: Query only the necessary associated fields.
4. Note circular references: Avoid circular references between models

Through associations, complex data queries and operations can be easily performed, bringing the data model closer to real business scenarios.