SearchBuilder - How Do You Control The Where Clause data Types From SearchBuilder

richard.hetherington

Hi, my company are testing your Editor ASP.Net Core library with a view to subscribing to a license once we have tested it thoroughly.

Any advice would be greatly welcomed.

Thanks in advance,

Richard Hetherington

Link to test case:

Debugger code (debug.datatables.net):

{
"draw": "2",
"columns[0][data]": "titles.tle_description",
"columns[0][name]": "",
"columns[0][searchable]": "true",
"columns[0][orderable]": "true",
"columns[0][search][value]": "",
"columns[0][search][regex]": "false",
"columns[1][data]": "title_editions.tld_edition_combined",
"columns[1][name]": "",
"columns[1][searchable]": "true",
"columns[1][orderable]": "true",
"columns[1][search][value]": "",
"columns[1][search][regex]": "false",
"columns[2][data]": "financial_years.bdg_year",
"columns[2][name]": "",
"columns[2][searchable]": "true",
"columns[2][orderable]": "true",
"columns[2][search][value]": "",
"columns[2][search][regex]": "false",
"columns[3][data]": "publishers.pub_company_name",
"columns[3][name]": "",
"columns[3][searchable]": "true",
"columns[3][orderable]": "true",
"columns[3][search][value]": "",
"columns[3][search][regex]": "false",
"columns[4][data]": "suppliers.sup_company_name",
"columns[4][name]": "",
"columns[4][searchable]": "true",
"columns[4][orderable]": "true",
"columns[4][search][value]": "",
"columns[4][search][regex]": "false",
"columns[5][data]": "title_editions.aqua_title_editions_count",
"columns[5][name]": "",
"columns[5][searchable]": "false",
"columns[5][orderable]": "true",
"columns[5][search][value]": "",
"columns[5][search][regex]": "false",
"columns[6][data]": "title_formats.aqua_title_formats_count",
"columns[6][name]": "",
"columns[6][searchable]": "false",
"columns[6][orderable]": "true",
"columns[6][search][value]": "",
"columns[6][search][regex]": "false",
"columns[7][data]": "title_formats.customer_selections_count",
"columns[7][name]": "",
"columns[7][searchable]": "false",
"columns[7][orderable]": "true",
"columns[7][search][value]": "",
"columns[7][search][regex]": "false",
"columns[8][data]": "",
"columns[8][name]": "",
"columns[8][searchable]": "true",
"columns[8][orderable]": "false",
"columns[8][search][value]": "",
"columns[8][search][regex]": "false",
"columns[9][data]": "",
"columns[9][name]": "",
"columns[9][searchable]": "true",
"columns[9][orderable]": "false",
"columns[9][search][value]": "",
"columns[9][search][regex]": "false",
"columns[10][data]": "titles.tle_core_title_2",
"columns[10][name]": "",
"columns[10][searchable]": "true",
"columns[10][orderable]": "true",
"columns[10][search][value]": "",
"columns[10][search][regex]": "false",
"columns[11][data]": "titles.tle_protocol_title",
"columns[11][name]": "",
"columns[11][searchable]": "true",
"columns[11][orderable]": "true",
"columns[11][search][value]": "",
"columns[11][search][regex]": "false",
"order[0][column]": "0",
"order[0][dir]": "asc",
"order[0][name]": "",
"start": "0",
"length": "10",
"search[value]": "",
"search[regex]": "false",
"searchBuilder[criteria][0][condition]": "=",
"searchBuilder[criteria][0][data]": "Protocol+Title",
"searchBuilder[criteria][0][origData]": "titles.tle_protocol_title",
"searchBuilder[criteria][0][type]": "string",
"searchBuilder[criteria][0][value][]": "false",
"searchBuilder[criteria][0][value1]": "false",
"searchBuilder[logic]": "AND"
}

Error messages shown:

Npgsql.PostgresException (0x80004005): 42883: operator does not exist: boolean = text

POSITION: 511
at Npgsql.Internal.NpgsqlConnector.ReadMessageLong(Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
at System.Runtime.CompilerServices.PoolingAsyncValueTaskMethodBuilder1.StateMachineBox1.System.Threading.Tasks.Sources.IValueTaskSource<TResult>.GetResult(Int16 token)
at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
at Npgsql.NpgsqlDataReader.NextResult()
at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior)
at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at DataTables.DatabaseUtil.Postgres.Query._Exec()
at DataTables.Query._Select()
at DataTables.Query.Exec(String sql)
at DataTables.Editor._SspQuery(Query query, DtRequest http)
at DataTables.Editor._Get(Object id, DtRequest http)
at DataTables.Editor._Process(DtRequest data)
at DataTables.Editor.Process(DtRequest data)
at DataTables.Editor.Process(IEnumerable`1 data, String culture)
at DataTables.Editor.Process(HttpRequest request, String culture)
at EditorNetCoreDemo.Controllers.TitlesController.GetList() in C:\inetpub\wwwroot\asp_net_core_framework\JSClassLibraryWebTest\JSClassLibraryWebApp\Controllers\TitlesController.cs:line 197
at lambda_method16(Closure, Object, Object[])
at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.SyncActionResultExecutor.Execute(ActionContext actionContext, IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeActionMethodAsync()
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeNextActionFilterAsync()
--- End of stack trace from previous location ---
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()
--- End of stack trace from previous location ---
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|25_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeFilterPipelineAsync()
--- End of stack trace from previous location ---
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddlewareImpl.Invoke(HttpContext context)
Exception data:
Severity: ERROR
SqlState: 42883
MessageText: operator does not exist: boolean = text
Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
Position: 511
File: parse_oper.c
Line: 656
Routine: op_error

POSTGRES LOG FILE

2024-04-25 17:15:31.814 BST [57396] STATEMENT:

tle_protocol_title is a boolean field in my Postgres Table

SELECT COUNT( titles.tle_id ) as "cnt"
FROM "titles"
WHERE ("titles"."tle_protocol_title" = $1 )

Description of problem:

I am using the SearchBuilder and trying to figure out how to control the data types of the final WHERE clause in the Query.

I am searching against a Boolean field in Postgres. I have the True and False values populated in SearchBuilder and can use them however the final WHERE clause is not casting the boolean field values to string to match the SearchBuilder value or vice versa.

I have tried the following in my Serverside Editor Config

.Field(new Field("(my_boolean_field::TEXT)", "my_boolean_field", typeof(string))
.Set(false) // Hide from the client-side table
.SearchBuilderOptions(new SearchBuilderOptions()
.Table("my_table")
.Value("(my_boolean_field::TEXT)")
)
)

In my datatables client-side config I have this

{
data: "my_boolean_field",
visible: false,
searchable: true,
searchBuilderType: "string",
searchBuilderTitle: "Protocol Title"
}

2024-04-25 17:15:31.814 BST [57396] ERROR: operator does not exist: boolean = text at character 511
2024-04-25 17:15:31.814 BST [57396] HINT: No operator matches the given name and argument types. You might need to add explicit type casts.