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
}

Visualization Development Editor Example

Query students with fuzzy name matching 'zhang'

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

Description of Query Parameters

Logical Operators

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

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 Type
$eq	Matches values that equal the specified value	String, Boolean, Number
$neq	Matches all values that are not equal to the specified value	String, Boolean, Number
$gt	Matches values greater than the specified value	Number
$gte	Matches values greater than or equal to the specified value	Number
$lt	Matches values less than the specified value	Number
$lte	Matches values less than or equal to the specified value	Number
$in	Matches any value specified in the array	Array
$nin	Matches values that are not in the specified array	Array

Comparison Operators Examples

// $in example - Query students aged 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;
};

// Combined 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 Type	Remarks
$search	Fuzzy search	String	Poor performance, avoid using it whenever possible
$nsearch	Does not contain, and returns null values	String	Poor performance, avoid using it whenever possible
$empty	Data is null	Any type
$nempty	Data is not null	Any type

In Flexdb, the $empty and $nempty operators query both non-existing fields and fields with null values.

Special Operators Examples

// $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;
};

// Combined query example - Query students who have 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 whose age is 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 whose name is "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: { $in: ["Zhang San", "Li Si", "Wang Wu"] }
        }
      },
      select: { $master: true }
    }
  });
  return result;
};

Composite Query

// Query students whose age is 18 or 20 and whose class is "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;
};

Negation Query ($nin)

// Query students whose age is not 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 make 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: { $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 containing null values; null in the query conditions is 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 too large (recommended not to exceed 100).
   • Using $in on indexed fields is more efficient.
4. Differences Between MySQL and Flexdb:
   • Flexdb supports using $in on array fields.
   • In MySQL, null values in $in need to be handled separately.

How to Perform Fuzzy Search ($search)

Basic Fuzzy Search

// Simple fuzzy search - Query 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;
};

Reverse Fuzzy Search

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

Composite Fuzzy Search

// Composite fuzzy search - Query students whose name contains "Zhang" and age is 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 whose name or description contains "excellent"
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;
};

Combination of Fuzzy and Exact Search

// Combination of Fuzzy and Exact Search - Query students whose class is "Class 3-2" and name contains "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; try to avoid using it in scenarios with large data volumes.
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 Method

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 Differences

Flexdb Query Example

Flexdb supports directly including null values using $in:

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 needs to convert $in queries into $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;
};

Composite Query Example

Querying null values while meeting 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;
};

Querying 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.