Implementing Many-to-Many Association Queries

Many-to-many fields themselves cannot be used as filter conditions, but they support using drill-down fields from the association table as filter conditions.

Example

There is a many-to-many association relationship between the student table and the course table, and the association field in the student table is student course selection (xsxk).

Using APIs custom methods as an example, this section describes how to configure query conditions.

Querying the List of Students Enrolled in Chinese Courses

module.exports = async function (params, context) {
  const result = await context.callModel({
    name: 'xsb_sipojkw', // Data model identifier, which can be viewed on the "Data Source - Data Model" list page
    methodName: 'wedaGetRecordsV2', // Data model method identifier
    params: {
      filter: {
          where: {
          },
          relateWhere:{ // Associated table query
            xsxk:{ // Association field in the student table
              where:{
                kcmc:{ // Field name to be queried in the course table
                    $in: ["Chinese"]
                }  
              }
            }
          }
        },
        // Sort
        orderBy: [
          {
            createdAt: "desc", // Creation time, descending order
          },
        ],
        // Return field selection
        select: {
          $master: true, // returns the fields in the main table
          xsxk: true // returns association field data
        },
        // Return the total field
        getCount: true,
        // Page size
        pageSize: 10,
        // Current page
        pageNumber: 1,
      },

  });


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

};

Query Result

{
  "total":2,
  "records":[
  {
    "owner":"1534736158943625218",
    "createdAt":1694070086572,
    "createBy":"1534736158943625218",
    "updateBy":"1534736158943625218",
    "xsxk":[
    {
      "owner":"1534736158943625218",
      "createdAt":1693972871239,
      "createBy":"1534736158943625218",
      "updateBy":"1534736158943625218",
      "_id":"7UF1G8XKK6",
      "kcmc":"Chinese",
      "updatedAt":1693972871239,
    },
],
    "xsxm":"Zhang San",
    "_id":"7UNM87D3FW",
    "updatedAt":1694070086572,
  },
  {
    "owner":"1534736158943625218",
    "createdAt":1693972984077,
    "createBy":"1534736158943625218",
    "updateBy":"1534736158943625218",
    "xsxk":[
    {
      "owner":"1534736158943625218",
      "createdAt":1693972871239,
      "createBy":"1534736158943625218",
      "updateBy":"1534736158943625218",
      "_id":"7UF1G8XKK6",
      "kcmc":"Chinese",
      "updatedAt":1693972871239,
    },
],
    "xsxm":"Li Si",
    "_id":"7UF1SESBCC",
    "updatedAt":1693972984077,
  },
],
}

Querying Zhang San's English Course Selection Record

module.exports = async function (params, context) {
  const result = await context.callModel({
    name: 'xsb_sipojkw', // Data model identifier, which can be viewed on the "Data Source - Data Model" list page
    methodName: 'wedaGetRecordsV2', // Data model method identifier
    params: {
      filter: {
        where: { // Query on the main table
          $and: [{
              xsxm: { // Field name to be queried in the student table
                $eq: "Zhang San"
              }
            }]
          },
          relateWhere:{ // Associated table query
            xsxk:{ // Association field in the student table
              where:{
                kcmc:{ // Field name to be queried in the course table
                    $in: ["English"]
                }
              }
            }
          }
        },
        // Sort
        orderBy: [
          {
            createdAt: "desc", // Creation time, descending order
          },
        ],
        // Return field selection
        select: {
          $master: true,
          xsxk: true
        },
        // Return the total field
        getCount: true,
        // Page size
        pageSize: 10,
        // Current page
        pageNumber: 1,
      },

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

{
  "total":1,
  "records":[
  {
    "owner":"1534736158943625218",
    "createdAt":1694072317951,
    "createBy":"1534736158943625218",
    "updateBy":"1534736158943625218",
    "xsxk":[
    {
      "owner":"1534736158943625218",
      "createdAt":1693972887373,
      "createBy":"1534736158943625218",
      "updateBy":"1534736158943625218",
      "_id":"7UF1HQD1QL",
      "kcmc":"English",
      "updatedAt":1693972887373,
    },
],
    "xsxm":"Zhang San",
    "_id":"7UNT5CS91A",
    "updatedAt":1694072317951,
  },
],
}