Data Model - Complex Query

[TOC]

How to Use Complex Queries

Basic Query Example

APIs Example - Simple Conditional Query

Query students with age greater than 18

module.exports = async function (params, context) {
  const result = await context.callModel({
    dataSourceName: 'student',
    methodName: 'wedaGetRecordsV2',
    params: {
      filter: {
        where: {
          age: {
            $gte: 18
          }
        }
      },
      select: { $master: true }
    }
  });
  return result;
};

View Results

{
  "records": [
    {
      "_id": "BH4UMKTNM6",
      "name": "Student Zhang",
      "age": 18
    },
    {
      "_id": "BERN1Y59JG",
      "name": "zhang2",
      "age": 33
    }
  ],
  "total": 2
}

Visual Development Editor Example

Query students whose name fuzzy matches 'zhang'.

async ({ params }) => {
  const data = await $w.cloud.callDataSource({
    dataSourceName: 'student',
    methodName: 'wedaGetRecordsV2',
    params: {
      filter: {
        where: {
          name: {
            $search: "zhang"
          }
        }
      },
      select: { $master: true }
    }
  });
  return data;
}

Query Parameter Description

Logical Operators

Name	Description
$and	Uses logical AND to join fields and returns data matching both field conditions
$or	Uses logical or to join fields and returns data matching any of the field conditions

Logical Operators Example

// $and example - Query students with age 18-30
module.exports = async function (params, context) {
  const result = await context.callModel({
    dataSourceName: 'student',
    methodName: 'wedaGetRecordsV2',
    params: {
      filter: {
        where: {
          $and: [
            { age: { $gte: 18 } },
            { age: { $lte: 30 } }
          ]
        }
      },
      select: { $master: true }
    }
  });
  return result;
};

// $or example - Query students whose name contains "zhang" or age is greater than 25
module.exports = async function (params, context) {
  const result = await context.callModel({
    dataSourceName: 'student',
    methodName: 'wedaGetRecordsV2',
    params: {
      filter: {
        where: {
          $or: [
            { name: { $search: "zhang" } },
            { age: { $gt: 25 } }
          ]
        }
      },
      select: { $master: true }
    }
  });
  return result;
};

Comparison Operators

Name	Description	Applicable Types
$eq	Matches values equal to a specified value	String, Boolean, Number
$neq	Matches all values not equal to a specified value	String, Boolean, Number
$gt	Matches values greater than a specified value	Number
$gte	Matches values greater than or equal to a specified value	Number
$lt	Matches values less than a specified value	Number
$lte	Matches values less than or equal to a specified value	Number
$in	Matches any value specified in an array	Array
$nin	Matches values that are not in the specified array	Array

Comparison Operators Example

// $in example - Query students with age 18, 20, or 25
module.exports = async function (params, context) {
  const result = await context.callModel({
    dataSourceName: 'student',
    methodName: 'wedaGetRecordsV2',
    params: {
      filter: {
        where: {
          age: { $in: [18, 20, 25] }
        }
      },
      select: { $master: true }
    }
  });
  return result;
};

// Compound query example - Query students with age greater than 18 and name not "foo"
module.exports = async function (params, context) {
  const result = await context.callModel({
    dataSourceName: 'student',
    methodName: 'wedaGetRecordsV2',
    params: {
      filter: {
        where: {
          $and:[
            {age: { $gt: 18 }},
            {name: { $neq: "foo" }}
          ]
        }
      },
      select: { $master: true }
    }
  });
  return result;
};

Special Operators

Name	Description	Applicable Types	Remarks
$search	Fuzzy search	String	Poor performance, avoid using whenever possible
$nsearch	Does not contain, and returns null values	String	Poor performance, avoid using whenever possible
$empty	Data is null	Any type
$nempty	Data is not null	Any type

$empty and $nempty in document databases query both non-existent fields and fields where the value is null.

Special Operators Example

// $search example - Fuzzy search for students whose name contains "zhang"
module.exports = async function (params, context) {
  const result = await context.callModel({
    dataSourceName: 'student',
    methodName: 'wedaGetRecordsV2',
    params: {
      filter: {
        where: {
          name: { $search: "zhang" }
        }
      },
      select: { $master: true }
    }
  });
  return result;
};

// $empty example - Query students who have not provided an email
module.exports = async function (params, context) {
  const result = await context.callModel({
    dataSourceName: 'student',
    methodName: 'wedaGetRecordsV2',
    params: {
      filter: {
        where: {
          email: { $empty: 1 }
        }
      },
      select: { $master: true }
    }
  });
  return result;
};

// Compound query example - Query students who have provided an email and whose name does not contain "test"
module.exports = async function (params, context) {
  const result = await context.callModel({
    dataSourceName: 'student',
    methodName: 'wedaGetRecordsV2',
    params: {
      filter: {
        where: {
          $and: [
            {email: { $nempty: 1 }},
            {name: { $nsearch: "test" }}
          ]
        }
      },
      select: { $master: true }
    }
  });
  return result;
};

List Data Query ($in)

Basic Usage

The $in operator is used to query records where a field's value equals any value in a specified list, equivalent to the IN operation in SQL.

// Query students with age being 18, 20, or 25
module.exports = async function (params, context) {
  const result = await context.callModel({
    dataSourceName: 'student',
    methodName: 'wedaGetRecordsV2',
    params: {
      filter: {
        where: {
          age: { $in: [18, 20, 25] }
        }
      },
      select: { $master: true }
    }
  });
  return result;
};

String List Query

// Query students with name being "Zhang San", "Li Si", or "Wang Wu"
module.exports = async function (params, context) {
  const result = await context.callModel({
    dataSourceName: 'student',
    methodName: 'wedaGetRecordsV2',
    params: {
      filter: {
        where: {
          name: name: { $in: ["Zhang San", "Li Si", "Wang Wu"] }
        }
      },
      select: { $master: true }
    }
  });
  return result;
};

Compound Query

// Query students with age being 18 or 20 and class being "Class 3-2"
module.exports = async function (params, context) {
  const result = await context.callModel({
    dataSourceName: 'student',
    methodName: 'wedaGetRecordsV2',
    params: {
      filter: {
        where: {
          $and:[
            {age: { $in: [18, 20] }},
            {className: { $eq: "Class 3-2" }}
          ]
        }
      },
      select: { $master: true }
    }
  });
  return result;
};

Negated Query ($nin)

// Query students with age not being 18, 20, or 25
module.exports = async function (params, context) {
  const result = await context.callModel({
    dataSourceName: 'student',
    methodName: 'wedaGetRecordsV2',
    params: {
      filter: {
        where: {
          age: { $nin: [18, 20, 25] }
        }
      },
      select: { $master: true }
    }
  });
  return result;
};

Visual Editor Example

async ({ params }) => {
  const data = await $w.cloud.callDataSource({
    dataSourceName: 'student',
    methodName: 'wedaGetRecordsV2',
    params: {
      filter: {
        where: {
          status: { $in: ["active", "pending"] }
        }
      }
    }
  });
  return data;
}

Special Scenarios: How to Ignore Conditions

1. Scenario 1: When the customer selects Chinese and Mathematics, query the Chinese and Mathematics courses.
2. Scenario 2: When the customer makes no selection, query all courses. How to achieve making no selection? Simply set $in to null.

// Scenario 1
module.exports = async function (params, context) {
  const result = await context.callModel({
    dataSourceName: 'course',
    methodName: 'wedaGetRecordsV2',
    params: {
      filter: {
        where: {
          course_name: course_name: { $in: ["Chinese", "Mathematics"] }
        }
      },
      select: { $master: true }
    }
  });
  return result;
};

// Scenario 2
module.exports = async function (params, context) {
  const result = await context.callModel({
    dataSourceName: 'course',
    methodName: 'wedaGetRecordsV2',
    params: {
      filter: {
        where: {
          course_name: { $in: null }
        }
      },
      select: { $master: true }
    }
  });
  return result;
};

Notes

1. $in does not support including null values; null in the query conditions will be automatically filtered.
2. The list can be an empty array, but it will not match any records.
3. Performance Considerations:
   • The number of values in the list should not be excessive (recommended not to exceed 100)
   • Using $in on indexed fields is more efficient
4. Differences between MySQL databases and document databases:
   • Document databases support using $in on array fields
   • MySQL requires null values in $in to be handled separately

How to Perform Fuzzy Search ($search)

Basic Fuzzy Search

// Simple Fuzzy Search - Query students with names containing "Zhang"
module.exports = async function (params, context) {
  const result = await context.callModel({
    dataSourceName: 'student',
    methodName: 'wedaGetRecordsV2',
    params: {
      filter: {
        where: {
          name: name: { $search: "Zhang" }
        }
      },
      select: { $master: true }
    }
  });
  return result;
};

Reverse Fuzzy Search

// Simple Fuzzy Search - Query students whose names do not contain "Zhang"
module.exports = async function (params, context) {
  const result = await context.callModel({
    dataSourceName: 'student',
    methodName: 'wedaGetRecordsV2',
    params: {
      filter: {
        where: {
          name: name: { $nsearch: "Zhang" }
        }
      },
      select: { $master: true }
    }
  });
  return result;
};

Compound Fuzzy Search

// Compound Fuzzy Search - Query students with names containing "Zhang" and age greater than 18
module.exports = async function (params, context) {
  const result = await context.callModel({
    dataSourceName: 'student',
    methodName: 'wedaGetRecordsV2',
    params: {
      filter: {
        where: {
          $and: [
            { name: { $search: "Zhang" } },
            { age: { $gt: 18 } }
          ]
        }
      },
      select: { $master: true }
    }
  });
  return result;
};

Multi-field Fuzzy Search

// Multi-field Fuzzy Search - Query students with names or descriptions containing "Youxiu"
module.exports = async function (params, context) {
  const result = await context.callModel({
    dataSourceName: 'student',
    methodName: 'wedaGetRecordsV2',
    params: {
      filter: {
        where: {
          $or: [
            { name: { $search: "excellent" } },
            { description: { $search: "excellent" } }
          ]
        }
      },
      select: { $master: true }
    }
  });
  return result;
};

Combined Fuzzy and Exact Search

// Combined Fuzzy and Exact Search - Query students in class "Class 3-2" with names containing "Zhang"
module.exports = async function (params, context) {
  const result = await context.callModel({
    dataSourceName: 'student',
    methodName: 'wedaGetRecordsV2',
    params: {
      filter: {
        where: {
          $and: [
            { className: { $eq: "Class 3-2" } },
            { name: { $search: "Zhang" } }
          ]
        }
      },
      select: { $master: true }
    }
  });
  return result;
};

Notes

1. $search can only be used for fields of string type
2. Fuzzy search has poor performance; avoid using it in scenarios with large datasets whenever possible.
3. Fuzzy search does not support wildcards, such as * or ?
4. For Chinese search, it is recommended to use 2-4 characters as the search term to achieve better performance.

How to Query null Values

Basic Query Methods

1. Use $eq to query null values

module.exports = async function (params, context) {
  const result = await context.callModel({
    dataSourceName: 'student',
    methodName: 'wedaGetRecordsV2',
    params: {
      filter: {
        where: {
          email: {
            $eq: null
          }
        }
      }
    }
  });
  return result;
};

2. Use $empty to query null values or non-existing fields

module.exports = async function (params, context) {
  const result = await context.callModel({
    dataSourceName: 'student',
    methodName: 'wedaGetRecordsV2',
    params: {
      filter: {
        where: {
          email: {
            $empty: 1
          }
        }
      }
    }
  });
  return result;
};

Database Handling of $in Discrepancies

Document Database Query Example

Document databases support directly using $in to include null values.

module.exports = async function (params, context) {
  const result = await context.callModel({
    dataSourceName: 'student',
    methodName: 'wedaGetRecordsV2',
    params: {
      filter: {
        where: {
          email: {
            $in: [null, "test@example.com"]
          }
        }
      }
    }
  });
  return result;
};

MySQL Query Example

MySQL requires converting $in queries to $or conditions.

module.exports = async function (params, context) {
  const result = await context.callModel({
    dataSourceName: 'student',
    methodName: 'wedaGetRecordsV2',
    params: {
      filter: {
        where: {
          $or: [
            {email: {$in: ["test@example.com"]}},
            {email: {$eq: null}}
          ]
        }
      }
    }
  });
  return result;
};

Compound Query Example

Query for null values and meet other conditions

module.exports = async function (params, context) {
  const result = await context.callModel({
    dataSourceName: 'student',
    methodName: 'wedaGetRecordsV2',
    params: {
      filter: {
        where: {
          $and: [
            {email: {$eq: null}},
            {age: {$gte: 18}}
          ]
        }
      }
    }
  });
  return result;
};

Query non-null values

module.exports = async function (params, context) {
  const result = await context.callModel({
    dataSourceName: 'student',
    methodName: 'wedaGetRecordsV2',
    params: {
      filter: {
        where: {
          email: {
            $nempty: 1
          }
        }
      }
    }
  });
  return result;
};

Notes

1. MySQL does not support including null values in the $in operator.