如何使用 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中文网其他相关文章！