Querybuilder with dynamic QueryBuilderColumns
|
<div class="col-lg-12 control-section">
<SfQueryBuilder TValue="ExpandoObject" DataSource="@Orders">
<QueryBuilderColumns>
<QueryBuilderColumn Field="OrderID" Label="OrderID" Type=Syncfusion.Blazor.QueryBuilder.ColumnType.Number></QueryBuilderColumn>
<QueryBuilderColumn Field="CustomerID" Label="CustomerID" Type=Syncfusion.Blazor.QueryBuilder.ColumnType.String></QueryBuilderColumn>
</QueryBuilderColumns>
</SfQueryBuilder>
</div>
@code{
public List<ExpandoObject> Orders { get; set; } = new List<ExpandoObject>();
protected override void OnInitialized()
{
Orders = Enumerable.Range(1, 75).Select((x) =>
{
dynamic d = new ExpandoObject();
d.OrderID = 1000 + x;
d.CustomerID = (new string[] { "ALFKI", "ANANTR", "ANTON", "BLONP", "BOLID" })[new Random().Next(5)];
d.Freight = (new double[] { 2, 1, 4, 5, 3 })[new Random().Next(5)] * x;
d.OrderDate = (new DateTime[] { new DateTime(2010, 11, 5), new DateTime(2018, 10, 3), new DateTime(1995, 9, 9), new DateTime(2012, 8, 2), new DateTime(2015, 4, 11) })[new Random().Next(5)];
d.ShipCountry = (new string[] { "USA", "UK" })[new Random().Next(2)];
d.Verified = (new bool[] { true, false })[new Random().Next(2)];
return d;
}).Cast<ExpandoObject>().ToList<ExpandoObject>();
}
} |
Gayathri ,
Your sample does not address the question. The question was how to create dynamic columns... not hard code them.
The below would typically the way to do this now that the referenced columns propert is deprecated.
However, using the foreach method does not allow the Operators parameter to be used. If you have a requirement to be able to specify custom operators per column (IE... potentially, every column has its own set of operators allowed).... how would you build columns dynamicly from a list.
|
<SfQueryBuilder TValue="ExpandoObject">
<QueryBuilderColumns>
@{
foreach(var column in columns)
{
<QueryBuilderColumn Field="@column.Field" Label="@column.Label" Type="@column.Type" Operators="@column.Operators"></QueryBuilderColumn>
}
}
</QueryBuilderColumns>
</SfQueryBuilder>
@code {
private List<QueryBuilderColumn> columns = new();
private static string[] Values = new string[] { "Mr.", "Mrs." };
public List<OperatorsModel> CustomOperators = new List<OperatorsModel> {
new OperatorsModel { Text="Equal", Value="equal"},
new OperatorsModel { Text="Not equal", Value="notequal"}
};
protected override async Task OnInitializedAsync()
{
columns = new List<QueryBuilderColumn>()
{
new QueryBuilderColumn(){ Field="EmployeeID", Label="Employee ID", Type=ColumnType.Number, Operators=CustomOperators},
new QueryBuilderColumn(){ Field="FirstName", Label="First Name", Type=ColumnType.String, Operators=CustomOperators},
new QueryBuilderColumn(){ Field="TitleOfCourtesy", Label="Title of Courtesy", Type=ColumnType.Boolean, Values=Values },
};
await base.OnInitializedAsync();
}
} |
I want to add that while we are able to add columns dynamically, in the event that we want to replace the columns and call StateHasChanged(), the columns are not updated, it still has the previous columns.
To describe the scenario, we have loaded QueryBuilder and dynamically defined the columns like the example above. Upon triggering an event (button click for example), we have a new list of columns that we wanted to load in the QueryBuilder but upon trying so, it did not show us the new columns we needed.
Can you provide a sample that updates the dynamic columns by triggering StateHasChanged()?
|
<SfQueryBuilder TValue="ExpandoObject" @ref="qrbobj">
<QueryBuilderColumns>
@{
foreach(var column in columns)
{
<QueryBuilderColumn Field="@column.Field" Label="@column.Label" Type="@column.Type" Operators="@column.Operators"></QueryBuilderColumn>
}
}
</QueryBuilderColumns>
</SfQueryBuilder>
<SfButton OnClick="click">Column Change</SfButton>
@code {
SfQueryBuilder<ExpandoObject> qrbobj;
private List<QueryBuilderColumn> columns = new();
private List<QueryBuilderColumn> DynamicColumns = new();
private static string[] Values = new string[] { "Mr.", "Mrs." };
public List<OperatorsModel> CustomOperators = new List<OperatorsModel> {
new OperatorsModel { Text="Equal", Value="equal"},
new OperatorsModel { Text="Not equal", Value="notequal"}
};
protected override async Task OnInitializedAsync()
{
columns = new List<QueryBuilderColumn>()
{
new QueryBuilderColumn(){ Field="EmployeeID", Label="Employee ID", Type=ColumnType.Number, Operators=CustomOperators},
new QueryBuilderColumn(){ Field="FirstName", Label="First Name", Type=ColumnType.String, Operators=CustomOperators},
new QueryBuilderColumn(){ Field="TitleOfCourtesy", Label="Title of Courtesy", Type=ColumnType.Boolean, Values=Values },
};
DynamicColumns = new List<QueryBuilderColumn>()
{
new QueryBuilderColumn(){ Field="EmployeeID1", Label="Employee ID1", Type=ColumnType.Number},
new QueryBuilderColumn(){ Field="FirstName1", Label="First Name1", Type=ColumnType.String},
new QueryBuilderColumn(){ Field="TitleOfCourtesy1", Label="Title of Courtesy1", Type=ColumnType.Boolean, Values=Values },
};
await base.OnInitializedAsync();
}
public void click()
{
qrbobj.Reset();
columns = DynamicColumns;
}
} |
Hi Gayathri K,
We have extended your sample code to give you the issue we are having.
Your given solution above works only if the number of columns are always the same (in the case above, 3 initially then 3 again when updated).
Please refer to the attached source code for our scenario. We are using the query builder inside a modal. We load the columns dynamically when we open the modal. Every time we open the modal, we already have the new set of column definitions we want to be loaded in the query builder.
From the extended sample, you can replicate it by clicking on [Open1] button, check the dropdown (there are 3 expected values as defined from code). Then close the modal and click on [Open2] button, check the dropdown (there should only be 1 value from dropdown as defined from code), you will see that the previous values are not removed and still there from the previous column definition values.
Thank you.
Clarence
Attachment: Sample_d89ab75d.zip
Hi,
Was the issue fixed now in current release?
Thank you.
Clarence
Hi,
Was this released?
Thank you.
Clarence
This is all good, but what I want to do is pull the list of fields and for the QueryBuilderColumns from a table, the problem I am currently having is that I cannot set the QueryBuilderColumn, as it tells me that the parameter Field, Label, Type should not be set outside of its component, is it possible to do this, or do I have to hard code the columns and types, if so this makes it difficult to make a re-usable component for my s
@if (QBFields != null) {
<SfQueryBuilder @ref="AppQBuilder" TValue="ExpandoObject">
<QueryBuilderColumns>
@foreach (var line in QBFields) {
<QueryBuilderColumn Field="@line.Field" Label="@line.Label" Type="@line.Type" />
}
</QueryBuilderColumns>
</SfQueryBuilder>
}
@code {
private SfQueryBuilder<ExpandoObject> AppQBuilder;
public List<QueryBuilderColumn> QBFields { get; set; } = new();
protected override async Task OnInitializedAsync() {
await GetFieldInfo();
}
private async Task GetFieldInfo() {
try {
var apiResponse = await httpClient.GetFromJsonAsync<ApiResponseDto<List<ColumnTypeInfo>>>(BaseURLs.GET_APPLICANT_FIELDS);
if (apiResponse.IsSuccessStatusCode) {
var fields = apiResponse.Result;
var newFields = new List<QueryBuilderColumn>();
foreach(var f in fields) {
newFields.Add(new QueryBuilderColumn() {
Field = f.Name, // These give me the warnings, but cause a problem
Label = f.Label,
Type = GetType(f.Type)
});
}
QBFields = newFields;
} else {
await swal.FireAsync("Info", apiResponse.Message, SweetAlertIcon.Info);
}
} catch (Exception ex) {
await swal.FireAsync("Error", ex.Message, SweetAlertIcon.Error);
}
}
private Syncfusion.Blazor.QueryBuilder.ColumnType GetType(string type) {
if (type.Contains("number")) return Syncfusion.Blazor.QueryBuilder.ColumnType.Number;
if (type.Contains("date")) return Syncfusion.Blazor.QueryBuilder.ColumnType.Date;
if (type.Contains("string")) return Syncfusion.Blazor.QueryBuilder.ColumnType.String;
if (type.Contains("bool")) return Syncfusion.Blazor.QueryBuilder.ColumnType.Boolean;
return Syncfusion.Blazor.QueryBuilder.ColumnType.Object;
}
}
Hi Simon,
After fetching the data from the table, we can set the query builder columns as shown in the below code snippet.
|
@page "/customquerybuilder"
@using Newtonsoft.Json @using Syncfusion.Blazor.QueryBuilder @using QbServerAPI.Data @using QbServerAPI.Shared @using System.Dynamic
@if (dataLoaded) { <div id="container"> <SfQueryBuilder TValue="ExpandoObject"> <QueryBuilderColumns> @{ foreach (CustomColumn col in columns) { ColumnType type = ColumnType.String; switch (col.Type) { case "Boolean": type = ColumnType.Boolean; break; case "Number": type = ColumnType.Number; break; case "Date": type = ColumnType.Date; break; } <QueryBuilderColumn Field="@col.Id" Label="@col.Label" Type="@type"></QueryBuilderColumn> } } </QueryBuilderColumns> </SfQueryBuilder> </div> }
@code { public List<CustomColumn> columns { get; set; } = new List<CustomColumn>(); public bool dataLoaded = false;
protected override async Task OnInitializedAsync() { using (var httpClient = new HttpClient()) { using (var response = await httpClient.GetAsync("https://localhost:7016/api/querybuilder/")) { string apiResponse = await response.Content.ReadAsStringAsync(); columns = JsonConvert.DeserializeObject<List<CustomColumn>>(apiResponse); dataLoaded = true; StateHasChanged(); } } } }
|
Please get back to us if you need any further assistance on this.
Regards,
YuvanShankar A
Hi Support,
I'm also using a DynamicDictionary to accomplish a very similar thing as the posters above (dynamically building the QueryBuilderColumns). The main difference is that I have a custom component with both a SfGrid and SfQueryBuilder but I'm using a parameter for the Table name I'm querying against.
In this situation I cannot use OnInitializedAsync() to reset the columns as it has already been run, but rather in the OnParametersSetAsync(). Is that too late to set the columns? When I perform that task there I cannot get the query builder columns to update after the first time it's set.
I've tried resetting the QueryBuilder and resetting the Rules but nothing works.
I know we're pushing the boundaries for this component, and possibly Blazor itself. If it is impossible, don't be shy to tell me to use something else.
Razor class:
```
Code behind:
```
```
Thanks,
Joe
Hi Joseph,
We have prepared the sample based on your requirement (OnParameterAsync method with local data). Refer to the below attached sample. If you still facing the issue, please share the issue replicable issue or replicate the issue in our sample. Based on that, we will check and provide better solution quickly.
Regards,
YuvanShankar A
Attachment: ContextMenuSample_25cc7429.zip
Hi Support,
Thanks for your reply. I'm still experiencing the same issue. Let me clarify what is happening. I have a `SfTreeView` displaying a list of tables. When I select a tablename in the tree, it updates the 'tablename' parameter in the component that contains the `SfQueryBuilder` and `SfGrid`.
Once that changes I reload the columns that are available to the QueryBuilder in that OnParametersSet method.
The problem is that the QueryBuilder loads with a default unpopulated Rule that has the list of the columns set the first time it's loaded. On subsequent table selection changes, that default rule never changes and for some reason I cannot delete it in code. I can manually delete it on the screen, create a new condition, and that will have the updated columns to select from.
So as a workaround, is there a way to remove the default condition on a table selection change? Should I do some kind of Navigation from the treeview instead so it completely resets the page? Do I need to look into using the Javascript version of this control instead?
Thanks,
Joe
I was able to get it working by tricking the UI to disable and reenable the panel with the SfGrid and SfQueryBuilder. Thanks for your help with this item.
You're welcome, Joseph. We are glad to hear that your issue has been resolved now. Please get back to us if you need any other assistance.
Hi,
the example where you change the column on click doesnt work with newer versions - could you provide an updated version ?
We strugle to find a way to update the columns after the initial render
Hi Christoph,
We have confirmed the issue as "Dynamically change the columns in Querybuilder was not working" We logged a defect report. We will include the fix for this issue in the upcoming weekly patch release scheduled for September 6, 2023.
You will be informed regarding this once the fix is published.
Disclaimer: "Inclusion of this solution in the weekly release may change due to other factors, including but not limited to QA checks and work reprioritization."
Regards,
KeerthiKaran K V
Hi Christoph,
We are glad to announce that our weekly patch release (23.1.41) has been rolled out. We have included the fix for this “Dynamically change the columns in Querybuilder was not working” issue in this release. So, we suggest you upgrade our Syncfusion packages to our latest version to resolve this issue on your end (23.1.41).
Package link: https://www.nuget.org/packages/Syncfusion.Blazor.QueryBuilder
Get back to us if you need any further assistance on this.
Regards,
KeerthiKaran K V
- 25 Replies
- 11 Participants
-
MA Michael Aston
- Dec 3, 2020 09:34 PM UTC
- Oct 17, 2023 04:42 PM UTC
