Query Filtering

Enfyra provides MongoDB-like filtering operators for querying data.

• HTTP / REST: pass the predicate as the filter query parameter (JSON in the query string).
• Handlers, hooks, flows ($ctx.$repos.*.find, #table.find, etc.): pass the same predicate as filter or where — both are equivalent (DynamicRepository uses filter ?? where).

Quick Navigation

• Comparison Operators - _eq, _gt, _lt, etc.
• Array Operators - _in, _not_in
• Text Search - _contains, _starts_with, _ends_with
• Null Checks - _is_null, _is_not_null
• Logical Operators - _and, _or, _not
• Range Operators - _between
• Complex Examples - Real-world patterns

Comparison Operators

_eq (Equal)

Match exact value.

// Find products with price = 100
const result = await $ctx.$repos.products.find({
  where: { price: { _eq: 100 } }
});

// URL: /products?filter={"price":{"_eq":100}}

_neq (Not Equal)

Exclude matching value.

// Find products where price != 0
const result = await $ctx.$repos.products.find({
  where: { price: { _neq: 0 } }
});

_gt (Greater Than)

Match values greater than.

// Find products with price > 100
const result = await $ctx.$repos.products.find({
  where: { price: { _gt: 100 } }
});

_gte (Greater Than or Equal)

Match values greater than or equal to.

// Find products with price >= 100
const result = await $ctx.$repos.products.find({
  where: { price: { _gte: 100 } }
});

_lt (Less Than)

Match values less than.

// Find products with price < 500
const result = await $ctx.$repos.products.find({
  where: { price: { _lt: 500 } }
});

_lte (Less Than or Equal)

Match values less than or equal to.

// Find products with price <= 500
const result = await $ctx.$repos.products.find({
  where: { price: { _lte: 500 } }
});

Array Operators

_in (In Array)

Match any value in the array.

// Find products in specific categories
const result = await $ctx.$repos.products.find({
  where: {
    category: { _in: ['electronics', 'gadgets', 'phones'] }
  }
});

// URL: /products?filter={"category":{"_in":["electronics","gadgets"]}}

_not_in (Not In Array)

Exclude values in the array.

// Find products not in these categories
const result = await $ctx.$repos.products.find({
  where: {
    category: { _not_in: ['discontinued', 'old'] }
  }
});

Text Search

Text search operators are case-insensitive.

_contains (Contains Text)

Match fields containing the text.

// Find products with name containing "phone"
const result = await $ctx.$repos.products.find({
  where: {
    name: { _contains: 'phone' }
  }
});

// Matches: "iPhone", "Phone Case", "Smartphone"

_starts_with (Starts With Text)

Match fields starting with the text.

// Find products starting with "Apple"
const result = await $ctx.$repos.products.find({
  where: {
    name: { _starts_with: 'Apple' }
  }
});

// Matches: "Apple iPhone", "Apple Watch"
// Doesn't match: "iPhone", "My Apple Product"

_ends_with (Ends With Text)

Match fields ending with the text.

// Find products ending with "Pro"
const result = await $ctx.$repos.products.find({
  where: {
    name: { _ends_with: 'Pro' }
  }
});

// Matches: "iPhone Pro", "MacBook Pro"
// Doesn't match: "Pro iPhone", "Professional"

Null Checks

_is_null (Field Is Null)

Match fields that are null.

// Find products without description
const result = await $ctx.$repos.products.find({
  where: {
    description: { _is_null: true }
  }
});

_is_not_null (Field Is Not Null)

Match fields that are not null.

// Find products with description
const result = await $ctx.$repos.products.find({
  where: {
    description: { _is_not_null: true }
  }
});

Range Operators

_between (Between Values)

Match values between two numbers (inclusive).

// Find products with price between 100 and 500
const result = await $ctx.$repos.products.find({
  where: {
    price: { _between: [100, 500] }
  }
});

// Equivalent to: price >= 100 AND price <= 500

Logical Operators

_and (All Conditions Must Match)

Combine multiple conditions with AND logic.

// Find active products in electronics category with price >= 100
const result = await $ctx.$repos.products.find({
  where: {
    _and: [
      { category: { _eq: 'electronics' } },
      { price: { _gte: 100 } },
      { isActive: { _eq: true } }
    ]
  }
});

_or (At Least One Condition Must Match)

Combine multiple conditions with OR logic.

// Find products that are active OR on sale
const result = await $ctx.$repos.products.find({
  where: {
    _or: [
      { isActive: { _eq: true } },
      { isOnSale: { _eq: true } }
    ]
  }
});

_not (Negate Condition)

Negate a condition.

// Find products that are not discontinued
const result = await $ctx.$repos.products.find({
  where: {
    _not: {
      status: { _eq: 'discontinued' }
    }
  }
});

Complex Logical Combinations

Combine logical operators for complex queries.

// Find active products in electronics OR gadgets, with price between 100-500
const result = await $ctx.$repos.products.find({
  where: {
    _and: [
      {
        _or: [
          { category: { _eq: 'electronics' } },
          { category: { _eq: 'gadgets' } }
        ]
      },
      { price: { _between: [100, 500] } },
      { isActive: { _eq: true } }
    ]
  }
});

Complex Examples

Example 1: Multiple Conditions

// Find active products in electronics or gadgets category
// with price between 100-500 and name containing "phone"
const result = await $ctx.$repos.products.find({
  where: {
    _and: [
      {
        _or: [
          { category: { _eq: 'electronics' } },
          { category: { _eq: 'gadgets' } }
        ]
      },
      { price: { _between: [100, 500] } },
      { name: { _contains: 'phone' } },
      { isActive: { _eq: true } }
    ]
  }
});

Example 2: Exclude Specific Values

// Find products not in discontinued or old categories
// with description and price >= 50
const result = await $ctx.$repos.products.find({
  where: {
    _and: [
      {
        category: { _not_in: ['discontinued', 'old'] }
      },
      { description: { _is_not_null: true } },
      { price: { _gte: 50 } }
    ]
  }
});

Example 3: Text Search with Other Conditions

// Find products with name starting with "Apple"
// price >= 200, and not discontinued
const result = await $ctx.$repos.products.find({
  where: {
    _and: [
      { name: { _starts_with: 'Apple' } },
      { price: { _gte: 200 } },
      {
        _not: {
          status: { _eq: 'discontinued' }
        }
      }
    ]
  }
});

Example 4: Date Range Queries

// Find orders created between two dates
const startDate = new Date('2024-01-01');
const endDate = new Date('2024-12-31');

const result = await $ctx.$repos.orders.find({
  where: {
    createdAt: { _between: [startDate, endDate] }
  }
});

Filtering by Relations

Filter records based on related table data. You can filter relations in two ways:

Method 1: Filter by Relation ID Directly

Filter directly on the relation using ID comparison operators. This is the simplest way to filter by relation ID.

// Find menu items without a parent (parent is null)
const result = await $ctx.$repos.menu_definition.find({
  where: {
    parent: { _is_null: true }
  }
});

// Find menu items with a parent (parent is not null)
const result = await $ctx.$repos.menu_definition.find({
  where: {
    parent: { _is_not_null: true }
  }
});

// Find menu items where parent ID equals 3
const result = await $ctx.$repos.menu_definition.find({
  where: {
    parent: { _eq: 3 }
  }
});

// Find menu items where parent ID is in [3, 4, 5]
const result = await $ctx.$repos.menu_definition.find({
  where: {
    parent: { _in: [3, 4, 5] }
  }
});

// Find menu items where parent ID is not 3
const result = await $ctx.$repos.menu_definition.find({
  where: {
    parent: { _neq: 3 }
  }
});

// Find menu items where parent ID is not in [1, 2]
const result = await $ctx.$repos.menu_definition.find({
  where: {
    parent: { _not_in: [1, 2] }
  }
});

Method 2: Filter by Relation ID via id/_id Field

You can also filter by explicitly specifying the id or _id field of the relation.

// Find menu items where parent ID equals 3
const result = await $ctx.$repos.menu_definition.find({
  where: {
    parent: {
      id: { _eq: 3 }
    }
  }
});

// Find menu items without a parent
const result = await $ctx.$repos.menu_definition.find({
  where: {
    parent: {
      id: { _is_null: true }
    }
  }
});

// Find menu items where parent ID is in [3, 4, 5]
const result = await $ctx.$repos.menu_definition.find({
  where: {
    parent: {
      id: { _in: [3, 4, 5] }
    }
  }
});

Method 3: Filter by Relation Fields

Filter by fields within the related table.

// Find products where category name is "Electronics"
const result = await $ctx.$repos.products.find({
  where: {
    category: {
      name: { _eq: 'Electronics' }
    }
  }
});

// Find orders where customer email contains "@example.com"
const result = await $ctx.$repos.orders.find({
  where: {
    customer: {
      email: { _contains: '@example.com' }
    }
  }
});

Note: Both Method 1 and Method 2 achieve the same result when filtering by relation ID. Use Method 1 for simpler syntax, or Method 2 if you prefer explicit field specification.

Using Filters in URL Queries

Filters can be used in URL query strings for REST API calls.

# Simple filter
GET /products?filter={"category":{"_eq":"electronics"}}

# Multiple conditions
GET /products?filter={"price":{"_gte":100},"isActive":{"_eq":true}}

# Complex filter with logical operators
GET /products?filter={"_and":[{"category":{"_eq":"electronics"}},{"price":{"_between":[100,500]}}]}

# Text search
GET /products?filter={"name":{"_contains":"phone"}}

Combining with Sorting and Pagination

Combine filters with sorting and pagination.

const result = await $ctx.$repos.products.find({
  where: {
    category: { _eq: 'electronics' },
    price: { _between: [100, 500] },
    isActive: { _eq: true }
  },
  fields: 'id,name,price',
  sort: '-price',
  limit: 20,
  meta: 'totalCount'
});

Sort behavior: - If no sort is specified, results are sorted by id ascending - Sort applies only to the parent table - Nested arrays are always sorted by id internally - createdAt, updatedAt, and scalar date, datetime, or timestamp columns get auto-generated single-field indexes. SQL includes id as a stable tie-breaker; Mongo includes _id. - Add explicit compound indexes for hot query shapes that combine time with other predicates, such as status + lastMessageAt or project + createdAt. Do not add duplicate single-field indexes for time columns. - Parent rows can be sorted by direct list-relation aggregates with _count(relationName), _max(relationName.fieldName), or _min(relationName.fieldName) - Raw dotted sort through a to-many relation, such as messages.createdAt, is not valid for parent ordering

Encrypted Field Restrictions

Fields marked isEncrypted=true are encrypted at rest and decrypted after records are selected. Do not use encrypted fields in filter or sort, including root queries and nested deep queries.

// Not supported: api_token is encrypted
await $ctx.$repos.integrations.find({
  where: {
    api_token: { _eq: 'plaintext-token' }
  }
});

// Not supported: api_token is encrypted
await $ctx.$repos.integrations.find({
  sort: 'api_token'
});

Encrypted ciphertext cannot be compared meaningfully for equality, text search, range queries, or ordering, and exposing these operations would leak implementation details. Store a separate non-secret lookup key or hash when records need to be searched by a secret-derived value.

Best Practices

1. Use appropriate operators - Choose the right operator for your use case
2. Combine conditions logically - Use _and and _or to build complex queries
3. Use indexes - Create database indexes on frequently filtered fields
4. Limit results - Always use limit for queries that might return many records
5. Use text search carefully - Text search can be slower, use it with other filters

Deep Queries (Nested Relations)

Query multiple levels of related data in a single request using the deep parameter. This is particularly useful for fetching complex object graphs.

Basic Deep Query Syntax

// Fetch users with their posts
const result = await $ctx.$repos.users.find({
  fields: 'id,name,email',
  deep: {
    posts: {
      fields: 'id,title,content'
    }
  }
});

Multi-Level Nested Queries

Fetch deeply nested relations across multiple tables:

// Fetch users with posts, comments, and authors
const result = await $ctx.$repos.users.find({
  fields: 'id,name',
  deep: {
    posts: {
      fields: 'id,title',
      deep: {
        comments: {
          fields: 'id,content',
          deep: {
            author: {
              fields: 'id,name,email'
            }
          }
        }
      }
    }
  }
});

Deep Query with Filter

Filter nested relations at any level:

// Fetch users with active posts only
const result = await $ctx.$repos.users.find({
  fields: 'id,name',
  deep: {
    posts: {
      fields: 'id,title',
      filter: {
        isActive: { _eq: true }
      }
    }
  }
});

// Nested filter at multiple levels
const result = await $ctx.$repos.users.find({
  fields: 'id,name',
  deep: {
    posts: {
      fields: 'id,title',
      filter: {
        status: { _eq: 'published' }
      },
      deep: {
        comments: {
          fields: 'id,content',
          filter: {
            isApproved: { _eq: true }
          }
        }
      }
    }
  }
});

Deep Query with Sort

Sort nested relations at any level using the deep parameter:

// Fetch users with posts sorted by date
const result = await $ctx.$repos.users.find({
  fields: 'id,name',
  deep: {
    posts: {
      fields: 'id,title,createdAt',
      sort: '-createdAt'
    }
  }
});

// Multi-level sorting
const result = await $ctx.$repos.users.find({
  fields: 'id,name',
  deep: {
    posts: {
      fields: 'id,title',
      sort: '-createdAt',
      deep: {
        comments: {
          fields: 'id,content',
          sort: 'createdAt'
        }
      }
    }
  }
});

Deep sort orders the loaded child rows inside each parent. It does not reorder the parent result set. To sort parent rows by child data, use a root aggregate sort:

const result = await $ctx.$repos.cloud_support_tickets.find({
  fields: 'id,subject,status,project.id,project.name',
  sort: '-_max(messages.createdAt),-createdAt',
  limit: 25,
  deep: {
    messages: {
      fields: 'id,authorKind,body,createdAt',
      sort: '-createdAt',
      limit: 3
    }
  }
});

Use _max(messages.createdAt) for latest child value, _min(messages.createdAt) for earliest child value, and _count(messages) for child count. Aggregate sort helpers only support direct one-to-many and many-to-many relations, and the aggregate field must be a non-encrypted scalar field on the related table.

Deep Query with Limit and Offset

Limit the number of nested records returned:

// Fetch users with their 5 most recent posts
const result = await $ctx.$repos.users.find({
  fields: 'id,name',
  deep: {
    posts: {
      fields: 'id,title,createdAt',
      sort: '-createdAt',
      limit: 5
    }
  }
});

// Pagination for nested relations
const result = await $ctx.$repos.users.find({
  fields: 'id,name',
  deep: {
    posts: {
      fields: 'id,title',
      page: 2,      // Page 2
      limit: 10     // 10 items per page
    }
  }
});

Combining All Deep Options

Combine filter, sort, and pagination in deep queries:

// Complex deep query example
const result = await $ctx.$repos.users.find({
  fields: 'id,name',
  deep: {
    posts: {
      fields: 'id,title,createdAt',
      filter: {
        status: { _eq: 'published' }
      },
      sort: '-createdAt',
      limit: 10,
      deep: {
        comments: {
          fields: 'id,content,createdAt',
          filter: {
            isApproved: { _eq: true }
          },
          sort: 'createdAt',
          limit: 5
        }
      }
    }
  }
});

URL Examples

Deep queries work in REST API URLs:

# Single level deep
GET /users?fields=id,name&deep={"posts":{"fields":"id,title"}}

# Multi-level deep
GET /users?fields=id,name&deep={"posts":{"fields":"id,title","deep":{"comments":{"fields":"id,content"}}}}

# Deep with filter
GET /users?fields=id,name&deep={"posts":{"fields":"id,title","filter":{"status":{"_eq":"published"}}}}

# Deep with sort and limit
GET /users?fields=id,name&deep={"posts":{"fields":"id,title","sort":"-createdAt","limit":5}}

# Complete deep query
GET /users?fields=id,name&deep={"posts":{"fields":"id,title,createdAt","filter":{"isActive":{"_eq":true}},"sort":"-createdAt","limit":10}}

Performance Considerations

1. Limit nested results - Always use limit on one-to-many and many-to-many relations
2. Filter at the right level - Apply filters as early as possible to reduce data transfer
3. Select only needed fields - Specify exact fields in fields parameter instead of using *
4. Avoid excessive nesting - Deep queries with many levels can impact performance
5. Use pagination - For large nested datasets, use page and limit instead of fetching all

SQL vs MongoDB

SQL Databases (PostgreSQL, MySQL): - Uses CTE (Common Table Expressions) for optimization - Generates efficient subqueries for each nested level - Automatically adds WHERE clauses to join related tables correctly

MongoDB: - Uses aggregation pipeline with $lookup operations - Automatically determines localField and foreignField from metadata - Preserves type handling (ObjectId conversion)

Both database types handle the same deep query syntax transparently.

Next Steps

• See Repository Methods for complete find() documentation
• Learn about Context Reference for accessing query parameters
• Check API Lifecycle to understand query processing