Skip to main content

Join Query: Display Customer Information in Orders

Previous chapters covered single-table operations. However, in real business scenarios, data is often interconnected—orders need to display customer information, articles need to show author details, and employees need to display department names.

This chapter introduces two approaches to implement multi-table join queries: the JOIN capability of Filter API and Custom SQL, helping you choose the right approach for your scenario.

What You'll Learn in This Chapter
  • Filter API multi-table join syntax
  • Custom SQL for complex join queries
  • How to choose the appropriate query approach
  • Configuration methods for table relationships

Requirements​

  • Order list needs to display customer name and phone number
  • Article list needs to display author name and avatar
  • Employee list needs to display department name
  • Support filtering and sorting by related table fields

Final Result:

Order List Page (Multi-table Join)

Comparison of Two Approaches​

FeatureFilter Multi-table QueryCustom SQL
ComplexityLow (uses JS object syntax)Medium (requires SQL writing)
Type SafetyFull TypeScript supportRequires manual type definition
MaintainabilityHigh (readable code)Medium (scattered SQL)
FlexibilityMedium (supports standard joins)High (any complex query)
Use CasesStandard join queries (1:1, N:1)Complex statistics, multi-table aggregation
Return FormatStructured objects (nested)Flattened results

Generally, prefer Filter multi-table queries (simple, type-safe), and use Custom SQL when that approach falls short.

Important Prerequisite: Relationship Configuration​

Lovrabet Reverse Inference Engine

Filter multi-table queries run based on table relationships analyzed by the Lovrabet Reverse Inference Engine. Only tables identified by the engine as having relationships can be joined.

Sources of Relationships:

  1. Automatic Detection: The system automatically identifies relationships between tables through reverse analysis of database structure (e.g., foreign keys, naming conventions)
  2. Manual Configuration: If two tables have a relationship but it wasn't detected, you can manually add it in the ER diagram configuration interface

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

Query Not Working?

If Filter multi-table queries don't return related table data, please check:

  1. Whether the two tables have a configured relationship in the ER diagram
  2. Whether the relationship fields are correctly configured
  3. Whether you're using the database table name, not the dataset code

Filter multi-table query is a built-in multi-table join feature of the SDK. It's simple to use and type-safe. The system automatically detects relationships between tables, uses LEFT JOIN uniformly, and supports up to 5 levels of nesting.

Syntax Format​

Use tableName.fieldName format to reference related table fields:

await client.models.orders.filter({
  select: [
    "id", // Main table field
    "order_no",
    "customer.name", // Related table field: tableName.fieldName
    "customer.phone",
    "customer.level",
  ],
  where: {
    status: { $eq: "pending" },
    "customer.level": { $eq: "VIP" }, // Filter by related table field
  },
  orderBy: [
    { create_time: "desc" },
    { "customer.name": "asc" }, // Sort by related table field
  ],
});

Implementation Steps​

Step 1: View and Configure Table Relationships​

Filter multi-table queries are based on table relationships analyzed by the Lovrabet Reverse Inference Engine. Before using it, you need to confirm that the relationships between tables are correctly configured.

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

ER Diagram Configuration Interface

Operation Steps:

  1. Navigate to the ER diagram configuration interface
  2. View the automatically detected table relationships (shown as connection lines)
  3. If two tables have a relationship but no line is displayed, you can manually add it:
    • Click the "Add Relationship" button
    • Select the main table and related table
    • Configure the relationship fields (e.g., orders.customer_id → customers.id)
    • Choose the relationship type (1:1 or N:1)

Common Relationship Types:

Relationship TypeDescriptionExample
1:1One-to-oneUser ← User Profile
N:1Many-to-oneOrder → Customer
1:NOne-to-manyCustomer ← Order (reverse query not currently supported)

Step 2: Query Using Filter Multi-table Query​

Scenario 1: Orders with Customer Information

import { lovrabetClient } from "./api/client";

/**
 * Query order list with customer information
 */
async function getOrderListWithCustomer(page = 1) {
  const result = await lovrabetClient.models.dataset_orders.filter({
    select: [
      // Main table fields
      "id",
      "order_no",
      "total_amount",
      "status",
      "create_time",
      // Related table fields (customer is the related table name)
      "customer.name",
      "customer.phone",
      "customer.level",
      "customer.address",
    ],
    where: {
      // Filter by related table field
      "customer.level": { $in: ["VIP", "SVIP"] },
      status: { $ne: "cancelled" },
    },
    orderBy: [
      { create_time: "desc" },
      { "customer.level": "desc" }, // VIP shown first
    ],
    currentPage: page,
    pageSize: 20,
  });

  return result;
}

// Usage example
const result = await getOrderListWithCustomer(1);

console.log(result.tableData[0]);
// Output:
// {
//   id: 1,
//   order_no: "ORD20240115001",
//   total_amount: 1000,
//   status: "completed",
//   create_time: "2024-01-15T10:30:00Z",
//   customer: {                    // Related table data automatically nested
//     name: "Zhang San",
//     phone: "138****8000",
//     level: "VIP",
//     address: "Chaoyang District, Beijing"
//   }
// }

Scenario 2: Articles with Author Information

/**
 * Query published articles with author information
 */
async function getArticleListWithAuthor(page = 1) {
  const result = await lovrabetClient.models.dataset_articles.filter({
    select: [
      "id",
      "title",
      "summary",
      "publish_time",
      "author.username", // Author username
      "author.avatar_url", // Author avatar
      "author.bio", // Author bio
    ],
    where: {
      status: { $eq: "published" },
      "author.is_signed": { $eq: true }, // Only show contracted authors
    },
    orderBy: [{ publish_time: "desc" }, { "author.username": "asc" }],
    currentPage: page,
    pageSize: 10,
  });

  return result;
}

Scenario 3: Multi-level Join Query

/**
 * Query orders with customer and customer's sales representative
 * Supports up to 5 levels of nesting: orders → customers → sales_users
 */
async function getOrderListWithSales(page = 1) {
  const result = await lovrabetClient.models.dataset_orders.filter({
    select: [
      "id",
      "order_no",
      "total_amount",
      "customer.name", // Customer name (level 1)
      "customer.level", // Customer level
      "customer.sales.name", // Sales representative name (level 2)
      "customer.sales.phone", // Sales representative phone
      "customer.sales.dept.name", // Sales rep's department (level 3)
    ],
    where: {
      status: { $eq: "pending" },
      "customer.sales.dept.region": { $eq: "North China" },
    },
    orderBy: [{ create_time: "desc" }],
    currentPage: page,
    pageSize: 20,
  });

  return result;
}

Step 3: Use in React Component​

// src/pages/order-list.tsx

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

interface OrderWithCustomer {
  id: number;
  order_no: string;
  total_amount: number;
  status: string;
  create_time: string;
  customer: {
    name: string;
    phone: string;
    level: string;
  };
}

export default function OrderList() {
  const [orders, setOrders] = useState<OrderWithCustomer[]>([]);
  const [total, setTotal] = useState(0);
  const [currentPage, setCurrentPage] = useState(1);
  const [loading, setLoading] = useState(false);

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

  const loadOrders = async () => {
    setLoading(true);
    try {
      const result = await lovrabetClient.models.dataset_orders.filter({
        select: [
          "id",
          "order_no",
          "total_amount",
          "status",
          "customer.name",
          "customer.phone",
          "customer.level",
        ],
        where: {
          status: { $ne: "deleted" },
        },
        orderBy: [{ create_time: "desc" }],
        currentPage,
        pageSize: 20,
      });

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

  const getStatusName = (status: string) => {
    const names: Record<string, string> = {
      pending: "Pending Payment",
      paid: "Paid",
      shipped: "Shipped",
      completed: "Completed",
      cancelled: "Cancelled",
    };
    return names[status] || status;
  };

  const getLevelColor = (level: string) => {
    const colors: Record<string, string> = {
      SVIP: "#ff6b6b",
      VIP: "#ffd93d",
      Regular: "#a8dadc",
    };
    return colors[level] || "#ccc";
  };

  return (
    <div className="order-list">
      <h1>Order List (Total: {total})</h1>

      {loading ? (
        <div>Loading...</div>
      ) : (
        <table>
          <thead>
            <tr>
              <th>Order No.</th>
              <th>Amount</th>
              <th>Status</th>
              <th>Customer Name</th>
              <th>Customer Phone</th>
              <th>Customer Level</th>
            </tr>
          </thead>
          <tbody>
            {orders.map((order) => (
              <tr key={order.id}>
                <td>{order.order_no}</td>
                <td>¥{order.total_amount.toFixed(2)}</td>
                <td>{getStatusName(order.status)}</td>
                <td>{order.customer.name}</td>
                <td>{order.customer.phone}</td>
                <td>
                  <span
                    style={{
                      padding: "2px 8px",
                      borderRadius: "4px",
                      backgroundColor: getLevelColor(order.customer.level),
                    }}
                  >
                    {order.customer.level}
                  </span>
                </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>
  );
}

Filter Multi-table Query Limitations​

  1. Relationship Prerequisite: Two tables must have a configured relationship in the ER diagram (automatically detected or manually added), otherwise the query won't work
  2. Relationship Type Limitation: Currently supports 1:1 and N:1 relationships, reverse 1:N relationships are not yet supported
  3. Nesting Level: Maximum 5 levels of nesting supported
  4. Table Name Reference: Must use database table names, not dataset codes
Query Not Working?

If Filter multi-table queries don't return expected related data:

  1. Check ER diagram configuration: https://app.lovrabet.com/app/{appCode}/data/er
  2. Confirm there's a relationship line between the two tables
  3. Confirm you're using the database table name (e.g., customer) not the dataset code (e.g., dataset_xxx)

Approach 2: Custom SQL​

When Filter multi-table queries can't meet your needs (e.g., complex statistics, multi-table aggregation), you can use Custom SQL.

Use Cases​

  • Complex data statistics and aggregation
  • Need to use database-specific functions
  • Multi-table GROUP BY, HAVING queries
  • Performance optimization (reduce query count)

Implementation Steps​

Step 1: Create Custom SQL on Platform​

SQL Name: getOrderWithCustomer

Platform Configuration URL: https://app.lovrabet.com/app/{appCode}/data/custom-sql

SELECT
  o.id,
  o.order_no,
  o.total_amount,
  o.status,
  o.create_time,
  c.name AS customer_name,
  c.phone AS customer_phone,
  c.level AS customer_level,
  c.address AS customer_address,
  u.name AS sales_name,
  d.name AS sales_dept
FROM dataset_orders o
LEFT JOIN dataset_customers c ON o.customer_id = c.id
LEFT JOIN dataset_users u ON c.sales_id = u.id
LEFT JOIN dataset_departments d ON u.dept_id = d.id
WHERE o.status != 'deleted'
  <if test="minAmount">
    AND o.total_amount >= #{minAmount}
  </if>
  <if test="customerLevel">
    AND c.level = #{customerLevel}
  </if>
  <if test="salesDept">
    AND d.name = #{salesDept}
  </if>
ORDER BY o.create_time DESC
LIMIT #{pageSize} OFFSET #{offset}

Step 2: Call Custom SQL from Frontend​

import { lovrabetClient } from "./api/client";

interface OrderWithCustomerFlat {
  id: number;
  order_no: string;
  total_amount: number;
  status: string;
  create_time: string;
  customer_name: string;
  customer_phone: string;
  customer_level: string;
  customer_address: string;
  sales_name: string;
  sales_dept: string;
}

/**
 * Query orders with related information using custom SQL
 */
async function getOrderListBySQL(params: {
  page?: number;
  pageSize?: number;
  minAmount?: number;
  customerLevel?: string;
  salesDept?: string;
}) {
  const {
    page = 1,
    pageSize = 20,
    minAmount,
    customerLevel,
    salesDept,
  } = params;

  const data = await lovrabetClient.sql.execute({
    sqlCode: "getOrderWithCustomer",
    params: {
      minAmount,
      customerLevel,
      salesDept,
      pageSize,
      offset: (page - 1) * pageSize,
    },
  });

  if (!data.execSuccess) {
    throw new Error("Query failed");
  }

  return {
    tableData: data.execResult || [],
    total: data.execResult?.length || 0, // Simplified handling, should return actual total
    currentPage: page,
    pageSize,
  };
}

// Usage example
const result = await getOrderListBySQL({
  page: 1,
  pageSize: 20,
  minAmount: 1000,
  customerLevel: "VIP",
});

console.log(result.tableData[0]);
// Output (flattened structure):
// {
//   id: 1,
//   order_no: "ORD20240115001",
//   total_amount: 1000,
//   status: "completed",
//   create_time: "2024-01-15T10:30:00Z",
//   customer_name: "Zhang San",
//   customer_phone: "138****8000",
//   customer_level: "VIP",
//   customer_address: "Chaoyang District, Beijing",
//   sales_name: "Li Si",
//   sales_dept: "North China"
// }

Step 3: Complex Statistical Query​

-- SQL Name: getCustomerOrderStats
-- Query customer order statistics

SELECT
  c.id,
  c.name,
  c.phone,
  c.level,
  COUNT(o.id) AS order_count,
  COALESCE(SUM(o.total_amount), 0) AS total_amount,
  COALESCE(SUM(CASE WHEN o.status = 'completed' THEN o.total_amount ELSE 0 END), 0) AS completed_amount,
  MAX(o.create_time) AS last_order_time
FROM dataset_customers c
LEFT JOIN dataset_orders o ON c.id = o.customer_id
  <if test="startDate">
    AND o.create_time >= #{startDate}
  </if>
  <if test="endDate">
    AND o.create_time &lt;= #{endDate}
  </if>
WHERE c.status = 'active'
  <if test="minLevel">
    AND c.level = #{minLevel}
  </if>
GROUP BY c.id, c.name, c.phone, c.level
HAVING COUNT(o.id) >= #{minOrderCount}
ORDER BY total_amount DESC
LIMIT #{pageSize} OFFSET #{offset}
/**
 * Query customer order statistics
 */
async function getCustomerStats(params: {
  page?: number;
  pageSize?: number;
  startDate?: string;
  endDate?: string;
  minLevel?: string;
  minOrderCount?: number;
}) {
  const { page = 1, pageSize = 20, ...rest } = params;

  const data = await lovrabetClient.sql.execute({
    sqlCode: "getCustomerOrderStats",
    params: {
      ...rest,
      minOrderCount: rest.minOrderCount ?? 1,
      pageSize,
      offset: (page - 1) * pageSize,
    },
  });

  if (!data.execSuccess) {
    throw new Error("Query failed");
  }

  return data.execResult || [];
}

Custom SQL Return Data Structure​

FeatureFilter Multi-table QueryCustom SQL
Data StructureNested objects { customer: { name: "..." } }Flattened { customer_name: "..." }
Field NamingOriginal field names preservedRequires AS aliases
Type InferenceAutomatic type inferenceRequires manual type definition

Approach Comparison and Selection​

Feature Comparison​

ScenarioFilter Multi-table QueryCustom SQL
Basic join query✅ Recommended✅ Available
Multi-level joins✅ Supported (5 levels)✅ Unlimited
Complex condition filtering✅ Supported✅ More flexible
Statistical aggregation❌ Not supported✅ Recommended
GROUP BY❌ Not supported✅ Supported
Window functions❌ Not supported✅ Supported
Database-specific functions❌ Not supported✅ Supported

Performance Comparison​

ScenarioFilter Multi-table QueryCustom SQL
Simple joinsComparableComparable
Complex joinsMay have multiple queriesSingle query complete
Large data volumeLimited by paginationCan optimize LIMIT
Statistical queriesNot applicableMore efficient

Selection Guide​

┌─────────────────────────────────────────────────────────────────┐
│  Multi-table Join Query Selection Guide                         │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│  Do you need complex statistics, GROUP BY, or window functions? │
│     │                                                          │
│     ├── Yes → Use [Custom SQL]                                 │
│     │         - Order statistics, customer analysis, etc.       │
│     │         - Sales reports, data dashboards                  │
│     │         - Scenarios requiring database-specific functions │
│     │                                                          │
│     └── No → Does it exceed 5 levels of nesting?                │
│               │                                                │
│               ├── Yes → Use [Custom SQL]                       │
│               │                                                │
│               └── No → Use [Filter Multi-table Query] (Recommended) │
│                       - Order list + customer info              │
│                       - Article list + author info              │
│                       - Employee list + department info         │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

Practical Case Study​

Case: Sales Order Dashboard​

Combining Filter multi-table queries and Custom SQL to implement a complete sales order dashboard.

Frontend Component​

// src/pages/sales-dashboard.tsx

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

export default function SalesDashboard() {
  const [summary, setSummary] = useState({
    totalOrders: 0,
    totalAmount: 0,
    vipCustomerCount: 0,
  });
  const [orders, setOrders] = useState([]);
  const [topCustomers, setTopCustomers] = useState([]);

  useEffect(() => {
    loadDashboard();
  }, []);

  const loadDashboard = async () => {
    // 1. Use Filter multi-table query to get order list (with customer info)
    const orderResult = await lovrabetClient.models.dataset_orders.filter({
      select: [
        "id",
        "order_no",
        "total_amount",
        "status",
        "create_time",
        "customer.name",
        "customer.level",
      ],
      where: {
        create_time: {
          $gte: new Date(Date.now() - 30 * 24 * 60 * 60 * 1000).toISOString(),
        },
      },
      orderBy: [{ create_time: "desc" }],
      currentPage: 1,
      pageSize: 10,
    });

    setOrders(orderResult.tableData);

    // 2. Use Custom SQL to get statistics
    const statsData = await lovrabetClient.sql.execute({
      sqlCode: "getSalesSummary",
      params: {
        startDate: new Date(Date.now() - 30 * 24 * 60 * 60 * 1000)
          .toISOString()
          .split("T")[0],
      },
    });

    if (statsData.execSuccess && statsData.execResult) {
      setSummary(statsData.execResult[0] || {});
    }

    // 3. Use Custom SQL to get top customers
    const topData = await lovrabetClient.sql.execute({
      sqlCode: "getTopCustomers",
      params: {
        limit: 10,
      },
    });

    if (topData.execSuccess && topData.execResult) {
      setTopCustomers(topData.execResult);
    }
  };

  return (
    <div className="sales-dashboard">
      <h1>Sales Order Dashboard</h1>

      {/* Summary cards */}
      <div className="summary-cards">
        <div className="card">
          <h3>Total Orders</h3>
          <p className="value">{summary.totalOrders}</p>
        </div>
        <div className="card">
          <h3>Total Amount</h3>
          <p className="value">¥{summary.totalAmount?.toFixed(2) || 0}</p>
        </div>
        <div className="card">
          <h3>VIP Customers</h3>
          <p className="value">{summary.vipCustomerCount}</p>
        </div>
      </div>

      {/* Latest orders */}
      <div className="section">
        <h2>Latest Orders</h2>
        <table>
          <thead>
            <tr>
              <th>Order No.</th>
              <th>Amount</th>
              <th>Status</th>
              <th>Customer Name</th>
              <th>Customer Level</th>
            </tr>
          </thead>
          <tbody>
            {orders.map((order: any) => (
              <tr key={order.id}>
                <td>{order.order_no}</td>
                <td>¥{order.total_amount.toFixed(2)}</td>
                <td>{order.status}</td>
                <td>{order.customer.name}</td>
                <td>{order.customer.level}</td>
              </tr>
            ))}
          </tbody>
        </table>
      </div>

      {/* Top customers */}
      <div className="section">
        <h2>Top Customers</h2>
        <ul>
          {topCustomers.map((customer: any, index) => (
            <li key={index}>
              {index + 1}. {customer.name} - ¥{customer.total_amount.toFixed(2)}
            </li>
          ))}
        </ul>
      </div>
    </div>
  );
}

Key Concepts Summary​

Filter Multi-table Query Syntax​

// select: Use tableName.fieldName
select: [
  "id",              // Main table field
  "customer.name",   // Related table field
  "customer.level"
]

// where: Use tableName.fieldName
where: {
  "customer.level": { "$eq": "VIP" }  // Related table field filter
}

// orderBy: Use tableName.fieldName
orderBy: [
  { "customer.name": "asc" }  // Related table field sort
]

Custom SQL Parameters​

// Use #{paramName} in SQL
const data = await lovrabetClient.sql.execute({
  sqlCode: "getOrderWithCustomer",
  params: {
    minAmount: 1000,
    customerLevel: "VIP",
    pageSize: 20,
    offset: 0,
  },
});

FAQ​

Q: Can Filter multi-table query and Custom SQL be used together?​

A: Yes. In the same application, you can choose different approaches based on scenarios:

  • List queries use Filter multi-table query (high development efficiency)
  • Statistical reports use Custom SQL (better performance)

Q: How do I get the relationships between tables?​

A: Filter multi-table queries are based on table relationships analyzed by the Lovrabet Reverse Inference Engine.

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

The system automatically identifies relationships between tables through reverse analysis (e.g., foreign keys, naming conventions). If two tables have a relationship that wasn't detected, you can manually add the relationship in the ER diagram interface.

A: Please troubleshoot with these steps:

  1. Check ER diagram configuration: Confirm a relationship has been established between the two tables

    • Visit https://app.lovrabet.com/app/{appCode}/data/er
    • Check if there's a connection line between the two tables
    • If not, manually add the relationship

  2. Check table name reference: Confirm you're using the database table name

    // ✅ Correct: Using database table name
    "customer.name";

    // ❌ Wrong: Using dataset code
    "dataset_xxx.name";

  3. Check relationship direction: Confirm the relationship type is a supported 1:1 or N:1

Q: How is the performance of Filter multi-table queries?​

A: Filter multi-table queries are ultimately converted to SQL LEFT JOIN, with performance comparable to hand-written SQL. For complex queries, Custom SQL may be more flexible and allow further optimization.

A: Filter multi-table query returns a nested structure that needs to be accessed through objects:

// Filter multi-table query
order.customer.name;

// Custom SQL (flattened)
order.customer_name;

Chapter Summary​

Congratulations on mastering multi-table join queries! Here's a comparison of the two approaches:

ApproachUse CaseCharacteristics
Filter Multi-table QueryStandard join queriesSimple, type-safe, nested structure
Custom SQLComplex statistics and aggregationFlexible, high performance, flattened results
Selection Guide
  • Prefer Filter multi-table query (simple and easy to use)
  • Use Custom SQL when needed (complex statistics)
  • Ensure table relationships are configured in the ER diagram

Next Steps​

Core Documentation​

Advanced Topics​

Difficulty Level: L2 | Estimated Time: 50 minutes