List Page: Paginated Query and Filtering

←上一节

Project Creation

下一节→

Detail Page: View and Edit Data

What You'll Learn

• Query data lists using filter
• Implement keyword search
• Add status filtering
• Implement pagination

Estimated Time: 20 minutes

---

What Are We Building?

We'll develop a customer list page with the following features:

• Display customer data (name, phone, company, status)
• Support keyword search
• Support filtering by status
• Support paginated loading

---

Step 1: Understand the Data Structure

Before writing code, we need to know what fields are available in the customer table.

Method 1: Ask AI (Recommended)

If you have MCP configured, simply ask the AI:

You: Help me check what fields are in the customer table?

AI: Found the customer dataset with the following fields:
- id: Customer ID (Primary Key)
- name: Name
- phone: Phone
- company: Company
- status: Status (enum: potential/active/lost)
- create_time: Created Time

Method 2: Check the Generated Configuration File

After creating a project with the CLI, a src/api/api.ts file is automatically generated containing configurations for all datasets.

// Example content in src/api/api.ts
export const LOVRABET_MODELS_CONFIG = {
  models: [
    {
      datasetCode: '8d2dcbae08b54bdd84c00be558ed48df',
      tableName: 'customer',
      name: 'Customer',
      alias: 'customers'  // Alias for easier reference
    }
  ]
};

---

Step 2: Create a Basic List

Let's start by implementing a simple list page without search or filtering.

Create a file in the src/pages/ directory:

// src/pages/customer-list.tsx

/**
 * Title: Customer List
 */
import { useState, useEffect } from "react";
import { lovrabetClient } from "../api/client";

export default function CustomerList() {
  const [customers, setCustomers] = useState([]);
  const [loading, setLoading] = useState(true);

  useEffect(() => {
    // Fetch customer list
    async function fetchCustomers() {
      try {
        const result = await lovrabetClient.models.customers.filter({
          pageSize: 20,  // 20 items per page
        });
        setCustomers(result.tableData);
      } catch (error) {
        console.error('Failed to fetch data:', error);
      } finally {
        setLoading(false);
      }
    }

    fetchCustomers();
  }, []);

  if (loading) {
    return <div>Loading...</div>;
  }

  return (
    <div>
      <h1>Customer List</h1>
      <table>
        <thead>
          <tr>
            <th>Name</th>
            <th>Phone</th>
            <th>Company</th>
            <th>Status</th>
          </tr>
        </thead>
        <tbody>
          {customers.map((customer: any) => (
            <tr key={customer.id}>
              <td>{customer.name}</td>
              <td>{customer.phone}</td>
              <td>{customer.company || '-'}</td>
              <td>{customer.status}</td>
            </tr>
          ))}
        </tbody>
      </table>
    </div>
  );
}

Code Explanation

• lovrabetClient.models.customers - Access the customer dataset (customers is the alias)
• .filter() - Query method that returns paginated data
• result.tableData - The data list array

Verify the Result

lovrabet start

Open your browser and visit http://localhost:5173, you should see the customer list.

---

Step 3: Add Search Functionality

Now let's add a search box so users can search for customers.

// src/pages/customer-list.tsx

/**
 * Title: Customer List
 */
import { useState, useEffect } from "react";
import { lovrabetClient } from "../api/client";

export default function CustomerList() {
  const [customers, setCustomers] = useState([]);
  const [loading, setLoading] = useState(true);
  const [keyword, setKeyword] = useState("");  // New: search keyword

  useEffect(() => {
    async function fetchCustomers() {
      setLoading(true);
      try {
        const result = await lovrabetClient.models.customers.filter({
          where: keyword
            ? {
                // Search name, phone, or company (fuzzy match)
                $or: [
                  { name: { $contain: keyword } },
                  { phone: { $contain: keyword } },
                  { company: { $contain: keyword } },
                ],
              }
            : undefined,
          pageSize: 20,
        });
        setCustomers(result.tableData);
      } catch (error) {
        console.error('Failed to fetch data:', error);
      } finally {
        setLoading(false);
      }
    }

    fetchCustomers();
  }, [keyword]);  // Re-query when keyword changes

  return (
    <div>
      <h1>Customer List</h1>

      {/* New: Search box */}
      <input
        type="text"
        placeholder="Search name, phone, or company..."
        value={keyword}
        onChange={(e) => setKeyword(e.target.value)}
        style={{ marginBottom: 16, padding: 8, width: 300 }}
      />

      <table>
        {/* ... table content unchanged ... */}
      </table>
    </div>
  );
}

Code Explanation

• $or - OR condition, any match is sufficient
• $contain - Fuzzy match, equivalent to SQL LIKE
• useEffect dependency array includes keyword, automatically re-queries on input

---

Step 4: Add Status Filtering

Besides search, we also want to filter customers by status.

// src/pages/customer-list.tsx

/**
 * Title: Customer List
 */
import { useState, useEffect } from "react";
import { lovrabetClient } from "../api/client";

export default function CustomerList() {
  const [customers, setCustomers] = useState([]);
  const [loading, setLoading] = useState(true);
  const [keyword, setKeyword] = useState("");
  const [status, setStatus] = useState("");  // New: status filter

  useEffect(() => {
    async function fetchCustomers() {
      setLoading(true);
      try {
        // Build query conditions
        const conditions: any[] = [];

        // Status filter
        if (status) {
          conditions.push({ status: { $eq: status } });
        }

        // Keyword search
        if (keyword) {
          conditions.push({
            $or: [
              { name: { $contain: keyword } },
              { phone: { $contain: keyword } },
              { company: { $contain: keyword } },
            ],
          });
        }

        const result = await lovrabetClient.models.customers.filter({
          where: conditions.length > 0 ? { $and: conditions } : undefined,
          pageSize: 20,
        });
        setCustomers(result.tableData);
      } catch (error) {
        console.error('Failed to fetch data:', error);
      } finally {
        setLoading(false);
      }
    }

    fetchCustomers();
  }, [keyword, status]);  // Re-query when keyword or status changes

  return (
    <div>
      <h1>Customer List</h1>

      {/* Search and filter */}
      <div style={{ marginBottom: 16 }}>
        <input
          type="text"
          placeholder="Search..."
          value={keyword}
          onChange={(e) => setKeyword(e.target.value)}
          style={{ padding: 8, width: 200 }}
        />

        <select
          value={status}
          onChange={(e) => setStatus(e.target.value)}
          style={{ marginLeft: 8, padding: 8 }}
        >
          <option value="">All Status</option>
          <option value="potential">Potential Customer</option>
          <option value="active">Active Customer</option>
          <option value="lost">Lost Customer</option>
        </select>
      </div>

      <table>
        {/* ... table content ... */}
      </table>
    </div>
  );
}

Code Explanation

• $and - AND condition, all conditions must be satisfied
• $eq - Equal to, exact match
• When search and filter are both active, use $and to connect them

---

Step 5: Add Pagination

When data grows large, pagination becomes necessary. Let's add pagination functionality.

// src/pages/customer-list.tsx

/**
 * Title: Customer List
 */
import { useState, useEffect } from "react";
import { lovrabetClient } from "../api/client";

export default function CustomerList() {
  const [customers, setCustomers] = useState([]);
  const [loading, setLoading] = useState(true);
  const [keyword, setKeyword] = useState("");
  const [status, setStatus] = useState("");

  // New: pagination state
  const [page, setPage] = useState(1);
  const [total, setTotal] = useState(0);
  const pageSize = 20;

  useEffect(() => {
    async function fetchCustomers() {
      setLoading(true);
      try {
        const conditions: any[] = [];

        if (status) {
          conditions.push({ status: { $eq: status } });
        }

        if (keyword) {
          conditions.push({
            $or: [
              { name: { $contain: keyword } },
              { phone: { $contain: keyword } },
              { company: { $contain: keyword } },
            ],
          });
        }

        const result = await lovrabetClient.models.customers.filter({
          where: conditions.length > 0 ? { $and: conditions } : undefined,
          currentPage: page,      // Current page
          pageSize: pageSize,     // Items per page
          orderBy: [{ id: "desc" }],  // Sort by ID descending
        });

        setCustomers(result.tableData);
        setTotal(result.total);   // Total count
      } catch (error) {
        console.error('Failed to fetch data:', error);
      } finally {
        setLoading(false);
      }
    }

    fetchCustomers();
  }, [keyword, status, page]);  // Re-query when page changes too

  // Reset to first page when search or filter changes
  useEffect(() => {
    setPage(1);
  }, [keyword, status]);

  const totalPages = Math.ceil(total / pageSize);

  return (
    <div>
      <h1>Customer List</h1>

      {/* Search and filter */}
      <div style={{ marginBottom: 16 }}>
        <input
          type="text"
          placeholder="Search..."
          value={keyword}
          onChange={(e) => setKeyword(e.target.value)}
          style={{ padding: 8, width: 200 }}
        />

        <select
          value={status}
          onChange={(e) => setStatus(e.target.value)}
          style={{ marginLeft: 8, padding: 8 }}
        >
          <option value="">All Status</option>
          <option value="potential">Potential Customer</option>
          <option value="active">Active Customer</option>
          <option value="lost">Lost Customer</option>
        </select>
      </div>

      {/* Data table */}
      <table>
        <thead>
          <tr>
            <th>Name</th>
            <th>Phone</th>
            <th>Company</th>
            <th>Status</th>
          </tr>
        </thead>
        <tbody>
          {loading ? (
            <tr><td colSpan={4}>Loading...</td></tr>
          ) : customers.length === 0 ? (
            <tr><td colSpan={4}>No data available</td></tr>
          ) : (
            customers.map((customer: any) => (
              <tr key={customer.id}>
                <td>{customer.name}</td>
                <td>{customer.phone}</td>
                <td>{customer.company || '-'}</td>
                <td>{customer.status}</td>
              </tr>
            ))
          )}
        </tbody>
      </table>

      {/* New: Pagination controls */}
      <div style={{ marginTop: 16 }}>
        <span>Total {total} items, Page {page}/{totalPages}</span>
        <button
          disabled={page <= 1}
          onClick={() => setPage(p => p - 1)}
          style={{ marginLeft: 8 }}
        >
          Previous
        </button>
        <button
          disabled={page >= totalPages}
          onClick={() => setPage(p => p + 1)}
          style={{ marginLeft: 8 }}
        >
          Next
        </button>
      </div>
    </div>
  );
}

Code Explanation

• currentPage - Current page number (starts from 1)
• pageSize - Items per page
• result.total - Total number of matching records
• orderBy - Sort order, desc for descending, asc for ascending

---

Complete Code

Here's the complete page combining all the code above:

Click to view complete code

// src/pages/customer-list.tsx

/**
 * Title: Customer List
 */
import { useState, useEffect } from "react";
import { lovrabetClient } from "../api/client";

// Define customer data type
interface Customer {
  id: number;
  name: string;
  phone: string;
  company: string;
  status: "potential" | "active" | "lost";
  create_time: string;
}

// Status display names
const STATUS_LABELS: Record<string, string> = {
  potential: "Potential Customer",
  active: "Active Customer",
  lost: "Lost Customer",
};

export default function CustomerList() {
  // Data state
  const [customers, setCustomers] = useState<Customer[]>([]);
  const [loading, setLoading] = useState(true);

  // Filter conditions
  const [keyword, setKeyword] = useState("");
  const [status, setStatus] = useState("");

  // Pagination
  const [page, setPage] = useState(1);
  const [total, setTotal] = useState(0);
  const pageSize = 20;

  // Fetch data
  useEffect(() => {
    async function fetchCustomers() {
      setLoading(true);
      try {
        // Build query conditions
        const conditions: any[] = [];

        if (status) {
          conditions.push({ status: { $eq: status } });
        }

        if (keyword) {
          conditions.push({
            $or: [
              { name: { $contain: keyword } },
              { phone: { $contain: keyword } },
              { company: { $contain: keyword } },
            ],
          });
        }

        const result = await lovrabetClient.models.customers.filter({
          where: conditions.length > 0 ? { $and: conditions } : undefined,
          currentPage: page,
          pageSize: pageSize,
          orderBy: [{ create_time: "desc" }],
        });

        setCustomers(result.tableData || []);
        setTotal(result.total || 0);
      } catch (error) {
        console.error("Failed to fetch data:", error);
      } finally {
        setLoading(false);
      }
    }

    fetchCustomers();
  }, [keyword, status, page]);

  // Reset page when filters change
  useEffect(() => {
    setPage(1);
  }, [keyword, status]);

  const totalPages = Math.ceil(total / pageSize);

  return (
    <div style={{ padding: 24 }}>
      <h1>Customer List</h1>

      {/* Search and filter */}
      <div style={{ marginBottom: 16, display: "flex", gap: 8 }}>
        <input
          type="text"
          placeholder="Search name, phone, or company..."
          value={keyword}
          onChange={(e) => setKeyword(e.target.value)}
          style={{ padding: 8, width: 240 }}
        />
        <select
          value={status}
          onChange={(e) => setStatus(e.target.value)}
          style={{ padding: 8 }}
        >
          <option value="">All Status</option>
          <option value="potential">Potential Customer</option>
          <option value="active">Active Customer</option>
          <option value="lost">Lost Customer</option>
        </select>
      </div>

      {/* Data table */}
      <table style={{ width: "100%", borderCollapse: "collapse" }}>
        <thead>
          <tr style={{ backgroundColor: "#f5f5f5" }}>
            <th style={thStyle}>Name</th>
            <th style={thStyle}>Phone</th>
            <th style={thStyle}>Company</th>
            <th style={thStyle}>Status</th>
            <th style={thStyle}>Created Time</th>
          </tr>
        </thead>
        <tbody>
          {loading ? (
            <tr>
              <td colSpan={5} style={tdStyle}>Loading...</td>
            </tr>
          ) : customers.length === 0 ? (
            <tr>
              <td colSpan={5} style={tdStyle}>No data available</td>
            </tr>
          ) : (
            customers.map((customer) => (
              <tr key={customer.id}>
                <td style={tdStyle}>{customer.name}</td>
                <td style={tdStyle}>{customer.phone}</td>
                <td style={tdStyle}>{customer.company || "-"}</td>
                <td style={tdStyle}>{STATUS_LABELS[customer.status]}</td>
                <td style={tdStyle}>
                  {new Date(customer.create_time).toLocaleDateString()}
                </td>
              </tr>
            ))
          )}
        </tbody>
      </table>

      {/* Pagination */}
      <div style={{ marginTop: 16, display: "flex", alignItems: "center", gap: 8 }}>
        <span>Total {total} items</span>
        <button disabled={page <= 1} onClick={() => setPage((p) => p - 1)}>
          Previous
        </button>
        <span>
          Page {page} / {totalPages || 1}
        </span>
        <button disabled={page >= totalPages} onClick={() => setPage((p) => p + 1)}>
          Next
        </button>
      </div>
    </div>
  );
}

// Simple styles
const thStyle: React.CSSProperties = {
  padding: 12,
  textAlign: "left",
  borderBottom: "1px solid #ddd",
};

const tdStyle: React.CSSProperties = {
  padding: 12,
  borderBottom: "1px solid #eee",
};

---

Section Summary

You've learned:

Topic	Description
filter()	Query data lists
where	Query conditions
$eq	Equal to
$contain	Fuzzy match
$and / $or	Logical operators
currentPage / pageSize	Pagination parameters
orderBy	Sorting

---

Frequently Asked Questions

Q: Why use customers instead of dataset_xxx?

customers is an alias configured in src/api/api.ts for easier reference. You can also use the standard approach:

// Standard approach (using full datasetCode)
lovrabetClient.models.dataset_8d2dcbae08b54bdd84c00be558ed48df.filter()

// Alias approach (more readable)
lovrabetClient.models.customers.filter()

Q: What if search doesn't work?

Check the following:

1. Is the field name correct? (Use database field names, not Chinese display names)
2. Make sure you use $contain, not $like
3. Ensure there's matching data in the database

Q: Why is the pagination total incorrect?

result.total returns the total number of records matching the filter conditions, not the number of records on the current page.

---

Next Steps

←上一节

Preparation

下一节→

Scenario 2: Customer Detail Page