Filter API Advanced Filtering Query

v1.1.22+

The filter API is a powerful data query interface provided by Lovrabet SDK, supporting complex condition filtering, field selection, sorting, and pagination. It is the recommended way for performing data list queries.

Prerequisites

Before starting, ensure you have completed SDK Configuration. It's recommended to use CLI auto-generation for configuration.

The following examples use alias format client.models.users for readability. You can also use the standard format client.models.dataset_xxx (functionally identical).

Full Mode Support

Starting from v1.1.22, the filter API supports both OpenAPI Mode and WebAPI Mode.

🎯 Main Features

• ✅ Complex Condition Query - Supports multi-condition combination, range queries, fuzzy matching, etc.
• ✅ Multi-table Relations - Supports related table queries with automatic JOIN
• ✅ Field Selection - Only return required fields to reduce data transfer
• ✅ Flexible Sorting - Supports multi-field sorting
• ✅ Paginated Query - Built-in pagination support
• ✅ Type Safety - Complete TypeScript type support

📝 API Signature

async filter<T = any>(params?: FilterParams): Promise<ListResponse<T>>

Parameter Description

interface FilterParams {
  where?: WhereCondition; // Query conditions
  select?: string[]; // Select fields to return
  orderBy?: SortList; // Sorting rules
  currentPage?: number; // Current page number (starts from 1, default: 1)
  pageSize?: number; // Items per page (default: 20)
}

Return Value

interface ListResponse<T> {
  tableData: T[]; // Data list
  total: number; // Total number of records
  currentPage: number; // Current page number
  pageSize: number; // Items per page
}

🔍 Where Condition Details

Condition Operators

The filter API supports the following condition operators (all operators start with $):

Operator	Description	Example
$eq	Equals	{ age: { $eq: 18 } }
$ne	Not equals	{ status: { $ne: 'deleted' } }
$gte / $gteq	Greater or equal	{ age: { $gte: 18 } }
$lte / $lteq	Less or equal	{ age: { $lte: 65 } }
$in	In set	{ country: { $in: ['China', 'USA'] } }
$contain	Contains (fuzzy)	{ name: { $contain: 'hello' } }
$startWith	Starts with	{ email: { $startWith: 'admin' } }
$endWith	Ends with	{ filename: { $endWith: '.pdf' } }

Purpose of $ Symbol

Expressions starting with $ are special operators in the filter API, used to represent query condition types. This is a common query language convention that avoids conflicts with regular field names.

Logical Connectors

Supports $and and $or for condition combination:

Connector	Description	Example
$and	AND (all conditions must match)	{ $and: [condition1, condition2] }
$or	OR (any condition matches)	{ $or: [condition1, condition2] }

💡 Usage Examples

Basic Queries

Simple Equality Query

// Query users aged 18
const result = await client.models.users.filter({
  where: {
    age: { $eq: 18 },
  },
});

Range Query

// Query users aged between 18 and 45
const result = await client.models.users.filter({
  where: {
    age: {
      $gte: 18, // Greater than or equal to 18
      $lte: 45, // Less than or equal to 45
    },
  },
});

Set Query

// Query users from China, USA, or Japan
const result = await client.models.users.filter({
  where: {
    country: {
      $in: ["China", "USA", "Japan"],
    },
  },
});

Fuzzy Matching

// Query users whose name contains "hello"
const result = await client.models.users.filter({
  where: {
    name: { $contain: "hello" },
  },
});

// Query users whose email starts with "admin"
const result = await client.models.users.filter({
  where: {
    email: { $startWith: "admin" },
  },
});

Composite Condition Queries

AND Condition Combination

// Query VIP users aged 18-45 from China
const result = await client.models.users.filter({
  where: {
    $and: [
      { age: { $gte: 18, $lte: 45 } },
      { country: { $eq: "China" } },
      { vip: { $ne: null } },
    ],
  },
});

OR Condition Combination

// Query users younger than 18 or older than 65
const result = await client.models.users.filter({
  where: {
    $or: [{ age: { $lte: 18 } }, { age: { $gte: 65 } }],
  },
});

Mixed Logic

// Query VIP users or active regular users from China
const result = await client.models.users.filter({
  where: {
    $or: [
      { vip: { $eq: true } },
      {
        $and: [{ active: { $eq: true } }, { country: { $eq: "China" } }],
      },
    ],
  },
});

Field Selection

Use the select parameter to return only required fields and improve query efficiency:

// Return only id, name, email fields
const result = await client.models.users.filter({
  where: {
    country: { $eq: "China" },
  },
  select: ["id", "name", "email"],
});

console.log(result.tableData);
// [
//   { id: 1, name: 'Zhang San', email: 'zhangsan@example.com' },
//   { id: 2, name: 'Li Si', email: 'lisi@example.com' }
// ]

Performance Optimization

When a data table has many fields but you only need a few of them, using select can significantly reduce network data transfer and query time.

Sorting

Use the orderBy parameter to specify sorting rules:

// Single field sorting: by last login time descending
const result = await client.models.users.filter({
  where: {
    country: { $eq: "China" },
  },
  orderBy: [{ lastLoginAt: "desc" }],
});

// Multi-field sorting: first by priority descending, then by creation time descending
const result = await client.models.tasks.filter({
  where: {
    status: { $ne: "completed" },
  },
  orderBy: [
    { priority: "desc" }, // First priority
    { createTime: "desc" }, // Second priority
    { name: "asc" }, // Third priority
  ],
});

Pagination

Use currentPage and pageSize parameters for pagination:

// Get page 2, with 50 records per page
const result = await client.models.users.filter({
  where: {
    country: { $eq: "China" },
  },
  currentPage: 2,
  pageSize: 50,
});

console.log(`Total: ${result.total}`);
console.log(`Current Page: ${result.currentPage}`);
console.log(`Page Size: ${result.pageSize}`);
console.log(`Data:`, result.tableData);

🔗 Multi-Table Relation Queries

v1.2.0+

filter API supports multi-table relation queries (JOIN), allowing you to fetch related table data in one query without manual multiple queries.

Relation Prerequisite

filter multi-table query is based on Lovrabet reverse inference engine analysis results. Only tables identified as having relationships can be JOIN queried. If two tables have a relationship but aren't recognized, you can manually add the relationship in the ER diagram configuration interface.

Relation Query Features

Feature	Description
JOIN Type	Unified use of LEFT JOIN to ensure main table data is not lost
Relation Detection	Automatically analyzed by Lovrabet reverse inference engine
Supported Relations	1:1, N:1 relations
Nesting Levels	Up to 5 levels of nesting
Field Reference	Use tableName.fieldName format

Configure Table Relations

ER Diagram Configuration: https://app.lovrabet.com/app/{appCode}/data/er

In the ER diagram configuration interface, you can:

• View system-auto-detected table relations
• Manually add or modify table relations
• Configure relation fields and relation types

Basic Usage

Use tableName.fieldName format to reference related table fields in select, where, and orderBy:

// Query articles and their author information
// Main table: article (articles), related table: profile (authors), 1:1 relation
const result = await client.models.article.filter({
  select: [
    "id",
    "title",
    "content",
    "create_at",
    "profile.username",      // Related table field
    "profile.avatar_url"     // Related table field
  ],
  where: {
    "status": { "$eq": "published" },
    "profile.is_signed": { "$eq": true }  // Filter by related table condition
  },
  orderBy: [
    { "create_at": "desc" },
    { "profile.username": "desc" }  // Sort by related table field
  ],
  currentPage: 1,
  pageSize: 20,
});

// Result automatically includes related table data
console.log(result.tableData[0]);
// {
//   id: 1,
//   title: "Article Title",
//   content: "Article content",
//   create_at: "2024-01-15T10:30:00Z",
//   profile: {
//     username: "Zhang San",
//     avatar_url: "https://...",
//     is_signed: true
//   }
// }

Relation Query Scenarios

Scenario 1: Order with Customer Information

// Query orders and their customer information
// Main table: orders (orders), related table: customers (customers), N:1 relation
const result = await client.models.orders.filter({
  select: [
    "id",
    "order_no",
    "total_amount",
    "status",
    "customer.name",           // Customer name
    "customer.phone",          // Customer phone
    "customer.level"           // Customer level
  ],
  where: {
    "status": { "$eq": "pending" },
    "customer.level": { "$eq": "VIP" }  // Only VIP customer orders
  },
  orderBy: [{ "create_time": "desc" }],
  currentPage: 1,
  pageSize: 20,
});

console.log(result.tableData[0]);
// {
//   id: 1,
//   order_no: "ORD20240115001",
//   total_amount: 1000,
//   status: "pending",
//   customer: {
//     name: "Zhang San",
//     phone: "138****8000",
//     level: "VIP"
//   }
// }

Scenario 2: Employee with Department Information

// Query employees and their department information
// Main table: employees (employees), related table: departments (departments), N:1 relation
const result = await client.models.employees.filter({
  select: [
    "id",
    "name",
    "position",
    "dept.name",              // Department name
    "dept.location"           // Department location
  ],
  where: {
    "status": { "$eq": "active" },
    "dept.location": { "$eq": "Beijing" }  // Only Beijing region employees
  },
  orderBy: [{ "dept.name": "asc" }, { "name": "asc" }],
  currentPage: 1,
  pageSize: 50,
});

Scenario 3: Multi-level Relation Query

// Query orders, their customers, and customers' sales representatives
// Main table: orders → customers → users (sales reps)
// Supports up to 5 levels of nesting
const result = await client.models.orders.filter({
  select: [
    "id",
    "order_no",
    "total_amount",
    "customer.name",                    // Customer name
    "customer.sales.name",              // Sales rep name
    "customer.sales.department"         // Sales rep department
  ],
  where: {
    "status": { "$eq": "completed" },
    "customer.sales.department": { "$eq": "North China" }
  },
  orderBy: [{ "create_time": "desc" }],
  currentPage: 1,
  pageSize: 20,
});

Relation Query + Complex Conditions

// Complex scenario: Query high-value orders from active customers
const result = await client.models.orders.filter({
  select: [
    "id",
    "order_no",
    "total_amount",
    "customer.name",
    "customer.level",
    "customer.phone"
  ],
  where: {
    $and: [
      { "total_amount": { "$gte": 10000 } },           // Order amount >= 10000
      { "create_time": { "$gte": "2024-01-01" } },     // Orders this year
      { "customer.level": { "$in": ["VIP", "SVIP"] } }, // VIP or SVIP customers
      { "customer.status": { "$eq": "active" } }       // Customer status is active
    ]
  },
  orderBy: [
    { "total_amount": "desc" },
    { "customer.level": "desc" }
  ],
  currentPage: 1,
  pageSize: 50,
});

Precautions for Relation Queries

1. Table Name Reference: Use database table name (e.g., profile), not dataset code (e.g., dataset_xxx)

2. Relation Detection: The system automatically detects relationships between tables (based on foreign keys, etc.), no manual specification needed

3. Data Structure: Related table data is nested in the corresponding object, e.g., customer.name returns { customer: { name: "..." } }

4. Performance Considerations:

   • Relation queries increase database load, use as needed
   • Use select to choose only needed fields
   • Set pageSize appropriately to avoid returning too much data

5. Nesting Limit: Supports up to 5 levels of nested relations

🎨 Complete Examples

Example 1: User Management System

import { createClient } from "@lovrabet/sdk";

const client = createClient();

// Query active VIP users from China aged between 18 and 45
// Return only necessary fields, sorted by last login time descending
const result = await client.models.users.filter({
  where: {
    $and: [
      { age: { $gte: 18, $lte: 45 } },
      { country: { $in: ["China", "USA", "Japan"] } },
      { vip: { $ne: null } },
      { active: { $eq: true } },
      { name: { $contain: "hello" } },
    ],
  },
  select: ["id", "name", "age", "country", "lastLoginAt", "email"],
  orderBy: [{ lastLoginAt: "desc" }, { name: "asc" }],
  currentPage: 1,
  pageSize: 20,
});

console.log(`Found ${result.total} matching users`);
result.tableData.forEach((user) => {
  console.log(`${user.name} (${user.age} years old) - ${user.email}`);
});

Example 2: Order Query

// Query orders from the last 30 days with amount greater than 100 and status pending or processing
const thirtyDaysAgo = new Date();
thirtyDaysAgo.setDate(thirtyDaysAgo.getDate() - 30);

const result = await client.models.orders.filter({
  where: {
    $and: [
      { createTime: { $gte: thirtyDaysAgo.toISOString() } },
      { amount: { $gte: 100 } },
      {
        $or: [
          { status: { $eq: "pending" } },
          { status: { $eq: "processing" } },
        ],
      },
    ],
  },
  select: ["id", "orderNo", "amount", "status", "createTime", "customerName"],
  orderBy: [{ amount: "desc" }, { createTime: "desc" }],
  currentPage: 1,
  pageSize: 50,
});

Example 3: Search Functionality

// Implement a search function: contains keyword in title or content
async function searchArticles(keyword: string, page: number = 1) {
  return await client.models.articles.filter({
    where: {
      $or: [
        { title: { $contain: keyword } },
        { content: { $contain: keyword } },
      ],
    },
    select: ["id", "title", "summary", "author", "publishTime"],
    orderBy: [{ publishTime: "desc" }],
    currentPage: page,
    pageSize: 10,
  });
}

// Using the search
const results = await searchArticles("artificial intelligence", 1);
console.log(`Found ${results.total} articles`);

Example 4: React Component Integration

import { useState, useEffect } from "react";
import { client } from "./api/client";

function UserList() {
  const [users, setUsers] = useState([]);
  const [total, setTotal] = useState(0);
  const [currentPage, setCurrentPage] = useState(1);
  const [loading, setLoading] = useState(false);

  // Query conditions
  const [filters, setFilters] = useState({
    country: "China",
    minAge: 18,
    maxAge: 65,
    vipOnly: false,
  });

  useEffect(() => {
    loadUsers();
  }, [currentPage, filters]);

  const loadUsers = async () => {
    setLoading(true);
    try {
      const result = await client.models.users.filter({
        where: {
          $and: [
            { age: { $gte: filters.minAge, $lte: filters.maxAge } },
            { country: { $eq: filters.country } },
            ...(filters.vipOnly ? [{ vip: { $ne: null } }] : []),
          ],
        },
        select: ["id", "name", "age", "email", "country", "vip"],
        orderBy: [{ vip: "desc" }, { age: "asc" }],
        currentPage,
        pageSize: 20,
      });

      setUsers(result.tableData);
      setTotal(result.total);
    } catch (error) {
      console.error("Failed to load users:", error);
    } finally {
      setLoading(false);
    }
  };

  return (
    <div>
      <h1>User List (Total: {total})</h1>

      {/* Filter conditions */}
      <div className="filters">
        <input
          type="number"
          value={filters.minAge}
          onChange={(e) =>
            setFilters({ ...filters, minAge: Number(e.target.value) })
          }
          placeholder="Minimum Age"
        />
        <input
          type="number"
          value={filters.maxAge}
          onChange={(e) =>
            setFilters({ ...filters, maxAge: Number(e.target.value) })
          }
          placeholder="Maximum Age"
        />
        <label>
          <input
            type="checkbox"
            checked={filters.vipOnly}
            onChange={(e) =>
              setFilters({ ...filters, vipOnly: e.target.checked })
            }
          />
          VIP Only
        </label>
      </div>

      {/* User list */}
      {loading ? (
        <div>Loading...</div>
      ) : (
        <table>
          <thead>
            <tr>
              <th>ID</th>
              <th>Name</th>
              <th>Age</th>
              <th>Email</th>
              <th>VIP</th>
            </tr>
          </thead>
          <tbody>
            {users.map((user) => (
              <tr key={user.id}>
                <td>{user.id}</td>
                <td>{user.name}</td>
                <td>{user.age}</td>
                <td>{user.email}</td>
                <td>{user.vip ? "✓" : ""}</td>
              </tr>
            ))}
          </tbody>
        </table>
      )}

      {/* Pagination */}
      <div className="pagination">
        <button
          disabled={currentPage === 1}
          onClick={() => setCurrentPage(currentPage - 1)}
        >
          Previous
        </button>
        <span>Page {currentPage}</span>
        <button
          disabled={currentPage * 20 >= total}
          onClick={() => setCurrentPage(currentPage + 1)}
        >
          Next
        </button>
      </div>
    </div>
  );
}

⚠️ Precautions

1. Operator Case Sensitivity

All operators must start with lowercase $, and are case-sensitive:

// ✅ Correct
{
  age: {
    $eq: 18;
  }
}

// ❌ Incorrect
{
  age: {
    $EQ: 18;
  }
} // Uppercase not recognized
{
  age: {
    eq: 18;
  }
} // Missing $

2. Condition Types

Ensure condition value types match field types:

// ✅ Correct - numeric field with numeric value
{
  age: {
    $gte: 18;
  }
}

// ❌ Incorrect - numeric field with string value
{
  age: {
    $gte: "18";
  }
}

// ✅ Correct - date field with ISO string
{
  createTime: {
    $gte: "2025-01-01T00:00:00.000Z";
  }
}

3. Performance Optimization

• Use select to reduce returned fields
• Set pageSize appropriately to avoid returning too much data at once
• Add indexes for frequently queried fields (contact platform administrator)

📚 Related Documentation

• Multi-Table Relation Queries - Complete guide to filter multi-table query and custom SQL
• Sales Reports: Custom SQL - Custom SQL query examples
• API Reference Manual - Complete API documentation
• Quick Start - Get started in 5 minutes
• Practical Examples - More real-world examples
• Troubleshooting - Common issue resolution

---

Have questions? Check out the Troubleshooting Documentation or contact technical support.