Skip to main content

Data Model - Relationships

[TOC]

About Relationships​

Relationships refer to the way different data entities are connected, describing how data is interrelated. In database design and programming, there are mainly three basic types of relationships:

Three Basic Relationships and Solutions

  1. One-to-Many Relationship (1:N) Simple explanation: One "parent" entity can correspond to multiple "child" entities, but one "child" entity can only correspond to one "parent" entity.

Example:

One department has multiple employees, but one employee can only belong to one department. One user can publish multiple articles, but one article can only be published by one user.

Solution:

Add a foreign key on the "many" side (employee table/article table) pointing to the "one" side (department table/user table) For example, the employee table has a department_id field pointing to the ID of the department table.

  1. Many-to-One Relationship (N:1)

Note: This is actually the reverse perspective of a one-to-many relationship, and the solution is the same as that for one-to-many.

  1. Many-to-Many Relationship (M:N) Simple explanation: Two entities can have multiple associations with each other.

Example:

Students and Courses: One student can enroll in multiple courses, and one course can have multiple students. Users and Roles: One user can have multiple roles, and one role can be assigned to multiple users.

Solution:

Use an intermediate table/association table (junction table) to store the association relationships between two entities. A junction table typically contains only two foreign keys, each pointing to the primary key of the associated tables. For example: The student-course association table contains two fields: student_id and course_id.

Relationships in Data Model​

Student Model: student Teacher Model: teacher Course Selection Model: course

Many-to-One and One-to-Many​

Take the student table and teacher table as an example.

The student table is associated with the teacher table through the teacher_id association relationship. Then the student model needs to establish a many-to-one relationship to the teacher model, creating an associated teacher. In the teacher model, a one-to-many relationship is automatically generated: managed student list.

Many-to-Many​

Take the student table and course selection table as an example.

The student table requires an intermediate table to associate with the teacher table.

The student model needs to establish a many-to-many relationship to the course selection model, creating a course selection list. In the course selection model, a many-to-many relationship is automatically generated: student list.

Query Association​

With Associated Tables​

Only specifying the additional association fields is required. This requires the use of the keyword: select.

APIs Example​

module.exports = async function (params, context) {
  const result = await context.callModel({
    dataSourceName: 'student', // Data model identifier, which can be viewed on the "Data Source - Data Model" list page
    methodName: 'wedaGetRecordsV2', // Data model method identifier. For supported methods, navigate to the details page of any data model under "Data Source - Data Model" to view methods supported by the current model.
    params: {
      select : {
        "$master" : true, "teacher_id" : true
      },
      getCount : true,
      pageSize : 10,
      pageNumber : 1
    }, // Parameters for the data model method
  });

  // Return the result of this method here, which must map to the structure defined in the output parameters.
  return result;
};

View results
{
  "total":3,
  "records":[
  {
    "owner":"1871099876542291970",
    "createdAt":1748340164367,
    "createBy":"1871099876542291970",
    "updateBy":"1871099876542291970",
    "_openid":"1871099876542291970",
    "name":"Student Zhang",
    "_id":"BH4UMKTNM6",
    "age":18,
    "updatedAt":1748340164367,
  },
  {
    "owner":"1871099876542291970",
    "createdAt":1747299013876,
    "createBy":"1871099876542291970",
    "updateBy":"1871099876542291970",
    "teacher_id":    {
      "owner":"1871099876542291970",
      "createdAt":1747298935881,
      "createBy":"1871099876542291970",
      "updateBy":"1871099876542291970",
      "_openid":"1871099876542291970",
      "name":"zhang",
      "_id":"BERMUX1E6L",
      "updatedAt":1748341135384,
    },
    "_openid":"1871099876542291970",
    "name":"zhang2",
    "_id":"BERN1Y59JG",
    "age":33,
    "updatedAt":1747299013876,
  },
  {
    "owner":"1871099876542291970",
    "createdAt":1747298998990,
    "createBy":"1871099876542291970",
    "updateBy":"1871099876542291970",
    "teacher_id":    {
      "owner":"1871099876542291970",
      "createdAt":1747298935881,
      "createBy":"1871099876542291970",
      "updateBy":"1871099876542291970",
      "_openid":"1871099876542291970",
      "name":"zhang",
      "_id":"BERMUX1E6L",
      "updatedAt":1748341135384,
    },
    "_openid":"1871099876542291970",
    "name":"foo",
    "_id":"BERN0LHV2Y",
    "age":12,
    "updatedAt":1748340983535,
  },
],
}

Visualization Development Editor Example​

Request

async ({ params }) => {
  const data = await $w.cloud.callDataSource({
    dataSourceName: 'student', // Data model identifier, which can be viewed on the "Data Source - Data Model" list page
    methodName: 'wedaGetRecordsV2', // Data model method identifier. For supported methods, navigate to the details page of any data model under "Data Source - Data Model" to view methods supported by the current model.
    params: {
      select : {
        "$master" : true, "teacher_id" : true
      },
      getCount : true,
      pageSize : 10,
      pageNumber : 1
    }, // Parameters for the data model method
  });

  console.log("Request result", data);
  return data;
}
View results
{
  "records": [
    {
      "owner": "1871099876542291970",
      "createdAt": 1748340164367,
      "createBy": "1871099876542291970",
      "updateBy": "1871099876542291970",
      "_openid": "1871099876542291970",
      "name": "Student Zhang",
      "_id": "BH4UMKTNM6",
      "age": 18,
      "updatedAt": 1748340164367
    },
    {
      "owner": "1871099876542291970",
      "teacher_id": {
        "owner": "1871099876542291970",
        "createdAt": 1747298935881,
        "createBy": "1871099876542291970",
        "updateBy": "1871099876542291970",
        "_openid": "1871099876542291970",
        "name": "zhang",
        "_id": "BERMUX1E6L",
        "updatedAt": 1748341135384
      },
      "createdAt": 1747299013876,
      "createBy": "1871099876542291970",
      "updateBy": "1871099876542291970",
      "_openid": "1871099876542291970",
      "name": "zhang2",
      "_id": "BERN1Y59JG",
      "age": 33,
      "updatedAt": 1747299013876
    },
    {
      "owner": "1871099876542291970",
      "teacher_id": {
        "owner": "1871099876542291970",
        "createdAt": 1747298935881,
        "createBy": "1871099876542291970",
        "updateBy": "1871099876542291970",
        "_openid": "1871099876542291970",
        "name": "zhang",
        "_id": "BERMUX1E6L",
        "updatedAt": 1748341135384
      },
      "createdAt": 1747298998990,
      "createBy": "1871099876542291970",
      "updateBy": "1871099876542291970",
      "_openid": "1871099876542291970",
      "name": "foo",
      "_id": "BERN0LHV2Y",
      "age": 12,
      "updatedAt": 1748340983535
    }
  ],
  "total": 3,
  "profile": {
    "requestTimeCost": 1042,
    "trrigerTimeCost": 1043,
    "resolveParamsTimecost": 1
  }
}

With Associated Tables - Old Associations or System Fields​

System fields are all old associations. In addition to specifying additional fields, it is necessary to enable the switch for old associations. Note: To query data for associated system fields, you need to switch to the production environment. System field data does not exist in the trial environment.

APIs Example​

Here we take owner as an example.

module.exports = async function (params, context) {
  const result = await context.callModel({
    dataSourceName: 'student', // Data model identifier, which can be viewed on the "Data Source - Data Model" list page
    methodName: 'wedaGetRecordsV2', // Data model method identifier. For supported methods, navigate to the details page of any data model under "Data Source - Data Model" to view methods supported by the current model.
    params: {
      select : {
       "$master" : true, "owner" : true, 
      },
      compatibleWithV1:true,
      getCount : true,
      pageSize : 10,
      pageNumber : 1
    }, // Parameters for the data model method
  });

  // Return the result of this method here, which must map to the structure defined in the output parameters.
  return result;
};

View results
{
  "records": [
    {
      "createdAt": 1748402459055,
      "updateBy": "1871099876542291970",
      "updatedAt": 1748402459055,
      "owner": "1871099876542291970",
      "createBy": "1871099876542291970",
      "_openid": "1871099876542291970",
      "name": "luke",
      "_id": "BH9PTF6U9W",
      "age": 24,
      "@owner": {
        "v1": {
          "primaryColumn": "name",
          "record": {
            "parent_user_id": "",
            "source": 1,
            "type": 0,
            "uuid": "1871099876542291970",
            "user_desc": "Super Administrator User",
            "createdAt": 1734939948634,
            "createBy": "administrator",
            "env_id": "lowcode-7gc7c86g01c220de",
            "updateBy": "administrator",
            "name": "administrator",
            "internal_user_type": 1,
            "uin": "100021405902",
            "_id": "1871099876542291970",
            "sub_uin": "100021405902",
            "updatedAt": 1734939948634
          }
        }
      }
    }
  ],
  "total": 1
}

Visualization Development Editor Example​

Request

async ({ params }) => {
  const data = await $w.cloud.callDataSource({
    dataSourceName: 'student', // Data model identifier, which can be viewed on the "Data Source - Data Model" list page
    methodName: 'wedaGetRecordsV2', // Data model method identifier. For supported methods, navigate to the details page of any data model under "Data Source - Data Model" to view methods supported by the current model.
    params: {
      select : {
       "$master" : true, "owner" : true, 
      },
      compatibleWithV1:true,
      getCount : true,
      pageSize : 10,
      pageNumber : 1
    }, // Parameters for the data model method
  });

  console.log("Request result", data);
  return data;
}
View results
{
  "records": [
    {
      "createdAt": 1748402459055,
      "updateBy": "1871099876542291970",
      "updatedAt": 1748402459055,
      "owner": "1871099876542291970",
      "createBy": "1871099876542291970",
      "_openid": "1871099876542291970",
      "name": "luke",
      "_id": "BH9PTF6U9W",
      "age": 24,
      "@owner": {
        "v1": {
          "primaryColumn": "name",
          "record": {
            "parent_user_id": "",
            "source": 1,
            "type": 0,
            "uuid": "1871099876542291970",
            "user_desc": "Super Administrator User",
            "createdAt": 1734939948634,
            "createBy": "administrator",
            "env_id": "lowcode-7gc7c86g01c220de",
            "updateBy": "administrator",
            "name": "administrator",
            "internal_user_type": 1,
            "uin": "100021405902",
            "_id": "1871099876542291970",
            "sub_uin": "100021405902",
            "updatedAt": 1734939948634
          }
        }
      }
    }
  ],
  "total": 1,
  "profile": {
    "requestTimeCost": 185,
    "trrigerTimeCost": 185,
    "resolveParamsTimecost": 0
  }
}

Using Association Query - Querying Many-to-One​

Using association query requires the keyword: relateWhere.

The query result here is student information associated with the teacher whose name='li', while also including the associated teacher information.

APIs Example​

module.exports = async function (params, context) {
  const result = await context.callModel({
    dataSourceName: 'student', // Data model identifier, which can be viewed on the "Data Source - Data Model" list page
    methodName: 'wedaGetRecordsV2', // Data model method identifier. For supported methods, navigate to the details page of any data model under "Data Source - Data Model" to view methods supported by the current model.
    params: {
      select : {
        "$master" : true, "teacher_id" : true
      },
      filter:{relateWhere:{
        teacher_id:{
          where:{name:{
            $eq: "li"
          }
        }}
      }
      },
      getCount : true,
      pageSize : 3,
      pageNumber : 1
    }, // Parameters for the data model method
  });

  // Return the result of this method here, which must map to the structure defined in the output parameters.
  return result;
};

View results
{
  "total":1,
  "records":[
  {
    "owner":"1871099876542291970",
    "createdAt":1747299013876,
    "createBy":"1871099876542291970",
    "updateBy":"1871099876542291970",
    "teacher_id":    {
      "owner":"1871099876542291970",
      "createdAt":1747298939873,
      "createBy":"1871099876542291970",
      "updateBy":"1871099876542291970",
      "_openid":"1871099876542291970",
      "name":"li",
      "_id":"BERMV99BBN",
      "updatedAt":1747298939873,
    },
    "_openid":"1871099876542291970",
    "name":"zhang2",
    "_id":"BERN1Y59JG",
    "age":33,
    "updatedAt":1748403261024,
  },
],
}

Visualization Development Editor Example​

Request

async ({ params }) => {
  const data = await $w.cloud.callDataSource({
    dataSourceName: 'student', // Data model identifier, which can be viewed on the "Data Source - Data Model" list page
    methodName: 'wedaGetRecordsV2', // Data model method identifier. For supported methods, navigate to the details page of any data model under "Data Source - Data Model" to view methods supported by the current model.
    params: {
      select : {
        "$master" : true, "teacher_id" : true
      },
      filter:{relateWhere:{
        teacher_id:{
          where:{name:{
            $eq: "li"
          }
        }}
      }
      },
      getCount : true,
      pageSize : 3,
      pageNumber : 1
    }, // Parameters for the data model method
  });

  console.log("Request result", data);
  return data;
}
View results
{
  "records": [
    {
      "owner": "1871099876542291970",
      "teacher_id": {
        "owner": "1871099876542291970",
        "createdAt": 1747298939873,
        "createBy": "1871099876542291970",
        "updateBy": "1871099876542291970",
        "_openid": "1871099876542291970",
        "name": "li",
        "_id": "BERMV99BBN",
        "updatedAt": 1747298939873
      },
      "createdAt": 1747299013876,
      "createBy": "1871099876542291970",
      "updateBy": "1871099876542291970",
      "_openid": "1871099876542291970",
      "name": "zhang2",
      "_id": "BERN1Y59JG",
      "age": 33,
      "updatedAt": 1748403261024
    }
  ],
  "total": 1,
  "profile": {
    "requestTimeCost": 203,
    "trrigerTimeCost": 203,
    "resolveParamsTimecost": 0
  }
}

Using Association Query - Querying One-to-Many​

Using association query requires the relateWhere keyword.

The query for associated students includes teacher information meeting the criteria.

APIs Example​

module.exports = async function (params, context) {
  const result = await context.callModel({
    dataSourceName: 'teacher', // Data model identifier, which can be viewed on the "Data Source - Data Model" list page
    methodName: 'wedaGetRecordsV2', // Data model method identifier. For supported methods, navigate to the details page of any data model under "Data Source - Data Model" to view methods supported by the current model.
    params: {
      select : {
        "$master" : true, "student_list" : true
      },
      filter:{relateWhere:{
        student_list:{
          where:{name:{
            $eq: "zhang2"
          }
        }}
      }
      },
      getCount : true,
      pageSize : 3,
      pageNumber : 1
    }, // Parameters for the data model method
  });

  // Return the result of this method here, which must map to the structure defined in the output parameters.
  return result;
};

View results
{
  "total":1,
  "records":[
  {
    "owner":"1871099876542291970",
    "createdAt":1747298939873,
    "createBy":"1871099876542291970",
    "updateBy":"1871099876542291970",
    "_openid":"1871099876542291970",
    "name":"li",
    "_id":"BERMV99BBN",
    "student_list":[
    {
      "owner":"1871099876542291970",
      "createdAt":1747299013876,
      "createBy":"1871099876542291970",
      "updateBy":"1871099876542291970",
      "_openid":"1871099876542291970",
      "name":"zhang2",
      "_id":"BERN1Y59JG",
      "age":33,
      "updatedAt":1748403261024,
    },
],
    "updatedAt":1747298939873,
  },
],
}

Visualization Development Editor Example​

Request

async ({ params }) => {
  const data = await $w.cloud.callDataSource({
    dataSourceName: 'student', // Data model identifier, which can be viewed on the "Data Source - Data Model" list page
    methodName: 'wedaGetRecordsV2', // Data model method identifier. For supported methods, navigate to the details page of any data model under "Data Source - Data Model" to view methods supported by the current model.
    params: {
      select : {
        "$master" : true, "student_list" : true
      },
      filter:{relateWhere:{
        student_list:{
          where:{name:{
            $eq: "zhang2"
          }
        }}
      }
      },
      getCount : true,
      pageSize : 3,
      pageNumber : 1
    }, // Parameters for the data model method
  });

  console.log("Request result", data);
  return data;
}
View results
{
  "records": [
    {
      "owner": "1871099876542291970",
      "createdAt": 1748340164367,
      "createBy": "1871099876542291970",
      "updateBy": "1871099876542291970",
      "_openid": "1871099876542291970",
      "name": "Student Zhang",
      "_id": "BH4UMKTNM6",
      "age": 18,
      "updatedAt": 1748412619234
    },
    {
      "owner": "1871099876542291970",
      "createdAt": 1747299013876,
      "createBy": "1871099876542291970",
      "updateBy": "1871099876542291970",
      "_openid": "1871099876542291970",
      "name": "zhang2",
      "_id": "BERN1Y59JG",
      "age": 33,
      "updatedAt": 1748403261024
    },
    {
      "owner": "1871099876542291970",
      "createdAt": 1747298998990,
      "createBy": "1871099876542291970",
      "updateBy": "1871099876542291970",
      "_openid": "1871099876542291970",
      "name": "foo",
      "_id": "BERN0LHV2Y",
      "age": 12,
      "updatedAt": 1748340983535
    }
  ],
  "total": 3,
  "profile": {
    "requestTimeCost": 838,
    "trrigerTimeCost": 838,
    "resolveParamsTimecost": 0
  }
}

Using Association Query - Querying Many-to-Many​

Using association query requires the relateWhere keyword.

The query for associated course selections includes student information meeting the criteria. For example, students who have selected the Chinese language course.

APIs Example​

module.exports = async function (params, context) {
  const result = await context.callModel({
    dataSourceName: 'student', // Data model identifier, which can be viewed on the "Data Source - Data Model" list page
    methodName: 'wedaGetRecordsV2', // Data model method identifier. For supported methods, navigate to the details page of any data model under "Data Source - Data Model" to view methods supported by the current model.
    params: {
      select : {
        "$master" : true, "course_id_list" : true
      },
      filter:{relateWhere:{
        course_id_list:{
          where:{name:{
            $eq: "Chinese"
          }
        }}
      }
      },
      getCount : true,
      pageSize : 10,
      pageNumber : 1
    }, // Parameters for the data model method
  });

  // Return the result of this method here, which must map to the structure defined in the output parameters.
  return result;
};

View results
{
  "total":1,
  "records":[
  {
    "owner":"1871099876542291970",
    "createdAt":1748340164367,
    "createBy":"1871099876542291970",
    "course_id_list":[
    {
      "owner":"1871099876542291970",
      "schedule":[1,2,3],
      "createdAt":1748333699720,
      "createBy":"1871099876542291970",
      "updateBy":"1871099876542291970",
      "_openid":"1871099876542291970",
      "name":"Chinese",
      "_id":"BH4BGT241E",
      "course_hour":30,
      "updatedAt":1748333699720,
    },
],
    "updateBy":"1871099876542291970",
    "_openid":"1871099876542291970",
    "name":"Student Zhang",
    "_id":"BH4UMKTNM6",
    "age":18,
    "updatedAt":1748412619234,
  },
],
}

Visualization Development Editor Example​

Request

async ({ params }) => {
  const data = await $w.cloud.callDataSource({
    dataSourceName: 'student', // Data model identifier, which can be viewed on the "Data Source - Data Model" list page
    methodName: 'wedaGetRecordsV2', // Data model method identifier. For supported methods, navigate to the details page of any data model under "Data Source - Data Model" to view methods supported by the current model.
    params: {
      select : {
        "$master" : true, "course_id_list" : true
      },
      filter:{relateWhere:{
        course_id_list:{
          where:{name:{
            $eq: "Chinese"
          }
        }}
      }
      },
      getCount : true,
      pageSize : 10,
      pageNumber : 1
    }, // Parameters for the data model method
  });

  console.log("Request result", data);
  return data;
}
View results
{
  "records": [
    {
      "owner": "1871099876542291970",
      "createdAt": 1748340164367,
      "createBy": "1871099876542291970",
      "updateBy": "1871099876542291970",
      "_openid": "1871099876542291970",
      "name": "Student Zhang",
      "_id": "BH4UMKTNM6",
      "age": 18,
      "updatedAt": 1748412619234
    },
    {
      "owner": "1871099876542291970",
      "createdAt": 1747299013876,
      "createBy": "1871099876542291970",
      "updateBy": "1871099876542291970",
      "_openid": "1871099876542291970",
      "name": "zhang2",
      "_id": "BERN1Y59JG",
      "age": 33,
      "updatedAt": 1748403261024
    },
    {
      "owner": "1871099876542291970",
      "createdAt": 1747298998990,
      "createBy": "1871099876542291970",
      "updateBy": "1871099876542291970",
      "_openid": "1871099876542291970",
      "name": "foo",
      "_id": "BERN0LHV2Y",
      "age": 12,
      "updatedAt": 1748340983535
    }
  ],
  "total": 3,
  "profile": {
    "requestTimeCost": 549,
    "trrigerTimeCost": 549,
    "resolveParamsTimecost": 0
  }
}