TL;DR: Explore advanced query building with Blazor Query Builder, connecting tables seamlessly with complex data binding. Learn to construct SELECT and WHERE clauses, integrate with dialogs, and enhance user experiences effortlessly.
Introduction
In the realm of application development, the need to retrieve data from multiple database tables is a common challenge. Fortunately, Syncfusion Blazor Query Builder presents an elegant solution to streamline this process.
In this comprehensive guide, we will delve into the art of connecting two tables using the Query Builder, offering practical examples and expert insights. By the end of this journey, you will be equipped with the skills to seamlessly connect tables and fetch related data, empowering you in your development endeavors.
What is Query Builder
The Syncfusion Blazor Query Builder is a robust user interface component that simplifies the process of query building. It supports various features like data binding, complex data binding, templates, importing and exporting queries in JSON and SQL formats, and parsing queries into predicates for integration with data managers. Using the Query Builder, developers can efficiently construct queries and leverage them in SQL Where clauses to fetch the desired data from multiple database tables.
Let’s start our exploration of connecting two tables with joins using Blazor Query Builder.
Setting up the tables in your database
When managing data in your application, organizing it into tables is key. Tables help store structured data effectively, making it easier to retrieve and analyze. Here’s a straightforward approach to setting up two essential tables: Product and Category. These tables will form the backbone of your data organization, enabling you to connect and analyze related information seamlessly.
Before we dive into the setup, let’s understand why these tables are important:
Product Table: This table is designed to store information about individual products. It typically includes columns like product ID, name, price, description, and other details relevant to your products.
Category Table: This table helps organize your products into logical categories or groups. It usually includes columns like category ID, name, and description, which help classify products based on shared characteristics.
By creating these tables and establishing a relationship between them, you can easily manage and retrieve interconnected data, making your application more efficient and user-friendly.
How to connect the tables
To connect the Product and Category tables, you’ll use a foreign key column in the Product table that references the primary key column in the Category table. This foreign key acts as a bridge, linking each Product to its corresponding Category. Typically, this foreign key column is named CategoryId.
Refer to the following code example to create the Product and Category tables.
@code {
public class Product
{
public string? CategoryId { get; set; }
public string? ProductId { get; set; }
public string? ProductName { get; set; }
public string? SupplierId { get; set; }
public int QuantityPerUnit { get; set; }
public double UnitPrice { get; set; }
public double UnitInStock { get; set; }
}
public class Category
{
public string? CategoryId { get; set; }
public string? CategoryName { get; set; }
public string? Description { get; set; }
public string? Picture { get; set; }
}
}
Understanding Table Relationships and Joins
In databases, tables often have relationships that define how data in one table relates to data in another. Joins are the tools used to combine data from multiple tables based on these established relationships.
Table Relationships
Table relationships create meaningful connections within a database. The three primary types of relationships are:
One-to-One Relationship: Each record in one table is linked to exactly one record in another table.
One-to-Many Relationship: A single record in one table can be linked to multiple records in another table.
Many-to-Many Relationship: Multiple records in one table can be associated with multiple records in another table.
Table Joins
Table joins combine rows from two or more tables based on a related column or column. The purpose of table joins is to retrieve data distributed across multiple tables and create a result set that combines relevant information from those tables.
Inner Join: Returns only the rows with matching values in both joined tables. It combines rows based on the specified join condition.
Left Join: Returns all the rows from the left (or first) table and the matching rows from the right (or second) table. Null values are included if no match is found in the right table.
Right Join: Similar to the left join, it returns all the rows from the right table and the matching rows from the left table. Null values are included if no match is found in the left table.
Full Outer Join: Returns all the rows from both tables, including matching and non-matching rows. If no match is found, null values are included.
Connecting two tables using the Blazor Query Builder
The Blazor Query Builder component supports complex data binding, so we can generate rules and SQL queries using multiple tables in our Query Builder component. To achieve this, we just need to specify the complex tables using our column directive and specify our component’s Separator property. The query builder component is rendered with multiple tables based on the above properties.
Refer to the following code example for complex data binding using column directives.
In the above code example, we have connected two tables, Categories and Products, as two columns with their properties serving as sub-columns. The class Complex is used as a TValue for Blazor Query Builder to render complex data binding.
Best Practices for joining tables
We have developed a custom component named JoinComponent that enables join operations in SQL queries. To accomplish this, we have integrated a query builder component with advanced data binding capabilities, which allows the construction of complex WHERE clauses involving two separate tables. Additionally, we have incorporated a list box component to facilitate the creation of the SELECT clause and a dropdown list component for constructing the join query.
Refer to the following code example showcasing our custom component named JoinComponent with four parameters and two type params to get the table properties.
@typeparam LeftTable
@typeparam RightTable
@code {
[Parameter]
public string LeftTableName { get; set; }
[Parameter]
public string RightTableName { get; set; }
[Parameter]
public string LeftOperand { get; set; }
[Parameter]
public string RightOperand { get; set; }
}
Building Select clause
The SELECT clause is a fundamental SQL (Structured Query Language) query component. It specifies the columns or expressions you want to retrieve from a table or multiple tables in a database. The SELECT clause determines the information you want to fetch and present as the result of your query.
Refer to the following code example showcasing the retrieval of a SELECT query from tables using the Listbox component. To accomplish this functionality, we have two tables named Categories and Products.
In the above code example, a custom class called CustomColumn has been explicitly created to define the items for the Listbox component. Moreover, a method named getColumnModel has been implemented to generate the data source required for the Listbox component.
Building Table Joins
To perform a join operation, you’ll require relational columns and the appropriate join type. The dropdown list components allow selecting the desired columns or table names for the left and right operands.
Additionally, 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 condition dropdown list allows you to specify the condition to connect the two operands.
To achieve the described functionality, the DataSource for the left and right operand Dropdown Lists is configured to the LeftTableItems and RightTableItems lists, respectively. Moreover, a custom class named CustomField has also been created to define the field settings for the join and operator Dropdown Lists. The Data sources for the join and operator Dropdown Lists are populated with join types and operators.
JoinComponent.razor
<div class="right">
<div class="e-relation-property">
<div class="e-content">
<div class="heading"><b style="padding: 10px;line-height: 30px;">Relation Properties</b></div>
</div>
<div class="e-content">
<span>Left Operand</span>
<SfDropDownList Placeholder="Select Operand" @bind-Value="LeftOperand" DataSource="LeftTableItems">
<DropDownListEvents TValue="string" TItem="CustomColumn" ValueChange="operandChange"></DropDownListEvents>
<DropDownListFieldSettings Text="Label" Value="Field"></DropDownListFieldSettings>
</SfDropDownList>
</div>
<div class="e-content">
<span>Right Operand</span>
<SfDropDownList Placeholder="Select Operand" @bind-Value="RightOperand" DataSource="RightTableItems ">
<DropDownListEvents TValue="string" TItem="CustomColumn" ValueChange="operandChange"></DropDownListEvents>
<DropDownListFieldSettings Text="Label" Value="Field"></DropDownListFieldSettings>
</SfDropDownList>
</div>
<div class="e-content">
<span>Join Type</span>
<SfDropDownList Placeholder="Select Join" @bind-Value="joinType" DataSource="Joins">
<DropDownListEvents TValue="string" TItem="CustomField" ValueChange="joinChange"></DropDownListEvents>
<DropDownListFieldSettings Text="Text" Value="Id"></DropDownListFieldSettings>
</SfDropDownList>
</div>
<div class="e-content">
<span>Operator</span>
<SfDropDownList Placeholder="Select Operator" @bind-Value="selectOperator" DataSource="Operators">
<DropDownListEvents TValue="string" TItem="CustomField" ValueChange="joinChange"></DropDownListEvents>
<DropDownListFieldSettings Text="Text" Value="Id"></DropDownListFieldSettings>
</SfDropDownList>
</div>
</div>
</div>
@code {
private List<CustomField>? Joins;
private List<CustomField>? Operators;
private string joinType = "";
private string selectOperator = "";
protected override void OnInitialized()
{
joinType = "INNER JOIN";
selectOperator = "=";
Joins = new List<CustomField>()
{
new CustomField() { Id = "INNER JOIN", Text = "Inner Join" },
new CustomField() { Id = "LEFT OUTER JOIN", Text = "Left Outer Join" },
new CustomField() { Id = "Right OUTER JOIN", Text = "Right Outer Join" }
};
Operators = new List<CustomField>
{
new CustomField() { Id = "=", Text = "Equals to" },
new CustomField() { Id = "<>", Text = "Does not equals to" },
new CustomField() { Id = "<", Text = "Is greater than" },
new CustomField() { Id = "<=", Text = "Is greater than or equal to" },
new CustomField() { Id = ">", Text = "Is less than" },
new CustomField() { Id = ">=", Text = "Is less than or equal to" }
};
}
private void operandChange(Syncfusion.Blazor.DropDowns.ChangeEventArgs<string, CustomColumn> e)
{
getContent();
}
private void joinChange(Syncfusion.Blazor.DropDowns.ChangeEventArgs<string, CustomField> e)
{
getContent();
}
private void operatorChange(Syncfusion.Blazor.DropDowns.ChangeEventArgs<string, CustomField> e)
{
getContent();
}
private string getJoinContent() {
string joinContent = LeftTableName + " " + joinType + " " + RightTableName + " ON (" +
LeftTableName + "." + LeftOperand + " " + selectOperator + " " + RightTableName + "." +
RightOperand + ")";
return joinContent;
}
private void getContent() {
string selectContent; string joinContent;
if ((leftSelectValue == null || leftSelectValue.Count() < 1) && (rightSelectValue == null || rightSelectValue.Count() < 1)) {
content = "Select atleast 1 Column";
}
else
{
selectContent = getSelectContent();
joinContent = getJoinContent();
content = selectContent + joinContent + ')';
}
}
public class CustomField
{
public string? Id { get; set; }
public string? Text { get; set; }
}
}
Building Where clause
The WHERE clause in SQL filters records within a query based on specific conditions. It enables developers to retrieve data from a table that meets particular criteria. When used with the SELECT statement, the WHERE clause allows for precise data retrieval by specifying the conditions that must be satisfied for a record to be included in the query result.
Add Query Builder to the Dialog component
You can enhance the user interface by incorporating the Query Builder into a dialog component. This dialog will open when the user clicks the Query button and close when the OK or Cancel button is clicked.
The visibility of the dialog, which contains the Query Builder for generating conditions, is controlled by the isOpen variable.
The Query Builder is used to construct conditions or rules in an SQL query’s WHERE clause. Complex data binding enables the creation of complex queries by leveraging nested queries, such as the example (Categories.CategoryID = 1), through advanced data binding techniques.
Connect multiple tables using complex data binding
The Query Builder component facilitates complex data binding, to generate the rules and SQL queries using multiple tables. To achieve this, we specify the complex tables using our column directive and set the component’s Separator property accordingly. As a result, the query builder component is rendered with multiple tables based on these configurations.
Refer to the following code example demonstrating complex data binding using column directives.
In the above code example, we have connected two tables, Categories and Products, as two columns with their properties serving as sub-columns. The class Complex is used as a TValue for Query Builder to render complex data binding.
Get SQL query from Query Builder
The Query Builder is used for creating the conditions or rules used in the WHERE clause of SQL query. Using complex data binding, we can get the query for the where clause in the join query. The resulting SQL WHERE clause query is then utilized in the generation of JOIN queries.
Finally, we created a sample razor component to render our custom component JoinComponent in this application.
<JoinComponent LeftOperand="@leftOperand" RightOperand="@rightOperand" RightTable="@Category" LeftTable="@Product" LeftTableName="@leftTableName" RightTableName="@rightTableName">
</JoinComponent>
@code {
private string leftTableName = "Categories";
private string rightTableName = "Products";
private string leftOperand = "CategoryId";
private string rightOperand = "CategoryId";
public class Product
{
public string? CategoryId { get; set; }
public string? ProductId { get; set; }
public string? ProductName { get; set; }
public string? SupplierId { get; set; }
public int QuantityPerUnit { get; set; }
public double UnitPrice { get; set; }
public double UnitInStock { get; set; }
}
public class Category
{
public string? CategoryId { get; set; }
public string? CategoryName { get; set; }
public string? Description { get; set; }
public string? Picture { get; set; }
}
}
Refer to the following images.
Connecting two tables: The user interface of the custom componentConnecting two tables: The user interface of the Query Builder in the custom componentConnecting two tables: The user interface of JOIN query creation using a custom component
Thanks for reading! In this blog, we’ve explored how to add queries or conditions with various connectors within the same group using Syncfusion Blazor Query Builder. We appreciate your feedback, which you can leave in the comments section below.
If you’re not a Syncfusion customer, you can download a free trial of Essential Studio® for Blazor to start exploring its controls immediately.
Satheeskumar works as a product manager at Syncfusion, where he specializes in the development of web components with cutting-edge technologies. He is interested in the latest web technologies and provides solutions for great products.