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.
✅ 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);

🎨 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)

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.

🎯 Main Features​

📝 API Signature​

Parameter Description​

Return Value​

🔍 Where Condition Details​

Condition Operators​

Logical Connectors​

💡 Usage Examples​

Basic Queries​

Simple Equality Query​

Range Query​

Set Query​

Fuzzy Matching​

Composite Condition Queries​

AND Condition Combination​

OR Condition Combination​

Mixed Logic​

Field Selection​

Sorting​

Pagination​

🎨 Complete Examples​

Example 1: User Management System​

Example 2: Order Query​

Example 3: Search Functionality​

Example 4: React Component Integration​

⚠️ Precautions​

1. Operator Case Sensitivity​

2. Condition Types​

3. Performance Optimization​

📚 Related Documentation​