如何使用 JavaScript 查詢產生器連接兩個表？

TL;DR: 讓我們看看如何使用 Syncfusion JavaScript 查詢產生器來連接兩個表。本部落格將引導您建立自訂 JoinComponent 並使用列錶框和下拉清單配置 WHERE、SELECT 和 JOIN 子句。這些步驟可確保高效的查詢生成，從而輕鬆連接和管理複雜的資料來源。請參閱 Stackblitz 演示以取得完整的程式碼範例。

Syncfusion JavaScript 查詢產生器是一個互動式 UI 元素，旨在建立查詢。其豐富的功能包括複雜的資料綁定、模板化、匯入和匯出 JSON 和 SQL 格式的查詢。此外，它還可以將查詢轉換為謂詞以與資料管理器一起使用。

本部落格介紹如何使用 JavaScript 查詢產生器元件連接兩個表。在這裡，我們將查詢生成器元件與複雜的資料綁定支援集成，以連接兩個不同的表。我們將為 SQL WHERE 子句建立查詢，嵌入一個用於製作 SELECT 子句的列錶框，以及一個下拉列表來簡化連接查詢的構造。

注意： 在繼續之前，請參閱 JavaScript 查詢產生器入門文件。

使用 JavaScript 查詢產生器建立自訂元件

讓我們建立一個名為 JoinComponent 的自訂元件，以方便建立連接查詢並透過一組參數提供彈性。使用此元件，使用者可以指定元素 ID、表的資料來源、表名稱以及左右操作數，這些都是建立連接查詢所必需的。

在此 JoinComponent 中，我們將把 JavaScript 查詢產生器整合到對話框元件中。我們還將合併列錶框和下拉清單元件，以增強使用者體驗並簡化配置和執行連接操作的過程。結果是一個多功能且用戶友好的元件，簡化了連接查詢的創建。

您可以參考此 Stackblitz 儲存庫中建立自訂 JoinComponent 的程式碼範例。

使用 JavaScript 查詢產生器連接兩個表

建立自訂元件後，請依照下列步驟連接兩個表。

步驟 1：建立 WHERE 子句

SQL WHERE 子句根據指定條件過濾資料庫中的記錄。

在這種情況下，我們的 JavaScript 查詢產生器元件在取得 WHERE 子句的值方面發揮著至關重要的作用。它支援複雜的資料綁定，透過組合兩個表中的資訊來產生規則和 SQL 查詢。此功能是透過使用 column 指令 指定複雜表並在元件中包含 separator 屬性來實現的。

透過配置這些屬性，查詢產生器將使用兩個表進行渲染，產生類似於下面給出的程式碼片段的結果連接查詢。

Employees.FirstName LIKE (“%Nancy%”)

步驟 2：建立 SELECT 子句

SQL 中的 SELECT 子句指定我們希望從一個或多個資料庫表中擷取的資料列或運算式。為了實現這一點，我們將渲染一個列錶框元件以從左表和右表中選擇所需的列。

步驟 3：建立 JOIN 子句

連接表涉及根據相關列組合兩個或多個表中的行。它檢索分佈在多個表中的數據，並建立一個組合這些表中相關資訊的結果集。

以下是連接表的關鍵面向：

• Related columns: Table joins rely on columns that establish relationships between tables. Typically, these columns represent primary and foreign keys. A primary key identifies each row in a table, and a foreign key creates a link between two tables by referring to the primary key of another table.
• Join types: There are different types of joins, including inner, left, right, and full outer joins.
• Join conditions: Join conditions specify the criteria for combining rows from different tables. They typically involve comparing the related columns using operators such as =, <>, <, >, etc. Join conditions can also involve multiple columns or complex expressions.

To perform a join operation, we need relational columns, a join type, and a join condition. To facilitate this, we’ll render a dropdown list component to select the Left and Right Operands. The Join Type dropdown list provides options for different types of joins, such as INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN. Lastly, the Operator dropdown list allows you to specify the conditions for connecting the two operands.

Refer to the following image.

Join component user interface

Step 4: Integrating the custom component into the app

To incorporate the custom JoinComponent into your app, import it and place it within a div element during rendering. You can provide essential properties to tailor the component to your needs, streamlining its integration into your app’s user interface.

Upon clicking the Filter button, the Query Builder component will be displayed, allowing users to construct a query. Subsequently, clicking the Copy button will copy the generated query to the clipboard.

Refer to the following code example to render the custom component on the HTML page.

 <div id="join"></div>

Refer to the following Typescript code to render the custom component.

import { JoinComponent } from './JoinComponent';

let ordersData = [
  { "OrderID": 10248, "CustomerID": 9, "EmployeeID": 5,"OrderDate": "7/4/1996","ShipperID": 3},
  { "OrderID": 10249, "CustomerID": 81, "EmployeeID": 6,"OrderDate": "7/5/1996","ShipperID": 1}
];

let employeesData = [
  { "EmployeeID": 1, "LastName": "Davolio", "FirstName": "Nancy", "BirthDate": "12/8/1968"},
  { "EmployeeID": 2, "LastName": "Fuller", "FirstName": "Andrew", "BirthDate": "2/19/1952 "},
  { "EmployeeID": 3, "LastName": "Leverling", "FirstName": "Janet", "BirthDate": "8/30/1963"},
  { "EmployeeID": 4, "LastName": "Peacock", "FirstName": "Margaret", "BirthDate": "9/19/1958"},
  { "EmployeeID": 5, "LastName": "Buchanan", "FirstName": "Steven", "BirthDate": "3/4/1955"},
  { "EmployeeID": 6, "LastName": "Suyama", "FirstName": "Michael", "BirthDate": "7/2/1963"}
];

let comp: JoinComponent = new JoinComponent(
          'join', // component ID
          ordersData, // left table
          employeesData, // right table
          'Orders', // left table name
          'Employees', // right table name
          'EmployeeID’, // left operand
          'EmployeeID' // right operand
);

Refer to the following images displaying the Query Builder and the join component user interfaces.

JavaScript Query Builder user interface

Joining two tables using the JavaScript Query Builder

The sample join query is as follows, and you can directly validate this query using this link.

SELECT Orders.OrderID, Orders.OrderDate, Employees.EmployeeID FROM (Orders INNER JOIN Employees ON (Orders.EmployeeID = Employees.EmployeeID)) WHERE(Employees.FirstName LIKE ('%Nancy%'))

Reference

For more details, refer to the entire code example for joining two tables using the JavaScript Query Builder on Stackblitz.

Conclusion

Thanks for reading! In this blog, we’ve explored how to join two tables using Syncfusion JavaScript Query Builder. Follow these steps to achieve similar results, and feel free to share your thoughts or questions in the comments below.

If you’re an existing customer, you can download the latest version of Essential Studio from the License and Downloads page. For those new to Syncfusion, try our 30-day free trial to explore all our features.

You can contact us through our support forum, support portal, or feedback portal. We are here to help you succeed!

Related blogs

• Top 5 Techniques to Protect Web Apps from Unauthorized JavaScript Execution
• Easily Render Flat JSON Data in JavaScript File Manager
• Effortlessly Synchronize JavaScript Controls Using DataManager
• Optimizing Productivity: Integrate Salesforce with JavaScript Scheduler

以上是如何使用 JavaScript 查詢產生器連接兩個表？的詳細內容。更多資訊請關注PHP中文網其他相關文章！