Create SQL Queries With React formatQuery and valueProcessor
Preparing for the server setup by processing the output of the query builder into database-ready SQL
This lesson preview is part of the Building Advanced Admin Reporting in React course and can be unlocked immediately with a \newline Pro subscription or a single-time purchase. Already have access to this course? Log in here.
Get unlimited access to Building Advanced Admin Reporting in React, plus 70+ \newline books, guides and courses with the \newline Pro subscription.
[00:00 - 00:22] In this lesson, we'll focus on the format query function exported by React Query Builder. Format Query is useful for displaying different representations of the query, namely the internal JSON format, the internal format with rule identifier stripped out, SQL format, the where clause only, and a parameterized format useful for interacting with APIs.
[00:23 - 00:39] Besides the format of the query representation, Format Query has a couple of other options that can be set. Value Processor is a function that will process rule values, and quote field names with will quote field names with the provided quote character in SQL mode.
[00:40 - 00:57] The value processor function is useful when your database engine requires special handling of certain value types. For example, the Oracle RDBMS does not have a native Boolean column type, so people typically use VAR-TR2 with the values Y and N representing true and false respectively.
[00:58 - 01:11] The default value processor function will use true and false in uppercase, so we would need to convert that to Y and N. Another value type that typically needs special handling is the date type.
[01:12 - 01:27] Most database engines have their own special syntax or functions for dates. To demonstrate the capabilities of the value processor function, we'll wrap the default value processor from React Query Builder and adjust the output to show what really happens to date fields on the server.
[01:28 - 01:51] Create a file called valueprocessor.ts in the client source folder. Create a file called valueprocessor.ts and we'll start that off with defining the value processor function.
[01:52 - 02:14] We'll import valueprocessor type from React Query Builder. This is a function that takes a field, an operator, and a value.
[02:15 - 02:48] We'll declare a variable here called Val and we'll initialize it to be the value in single quotes. If the field that we're passed in is date, order date, or ship date, then we're going to add the word date in front of the value.
[02:49 - 03:44] Date or field equals order date or field equals ship date, then Val is going to equal date value. Otherwise, Val is going to equal the default value processor with the same arguments, field, operator, and value.
[03:45 - 03:55] Then we'll just return Val. Let's export that as the default.
[03:56 - 04:10] We're done with that file. Now we can use this new value processor in the SQL representation of our query in the application.
[04:11 - 04:51] Back in app.tsx, we want to import from valueprocessor, valueprocessor, and down in the SQL representation of our query, we're going to change this to be an object that takes a format of SQL and valueprocessor. Save that.
[04:52 - 05:01] View the application and choose the date field to filter on to see the changes we've made to the SQL representation. You should see the word date before the value.
[05:02 - 05:21] Let's go back to our application, reload everything, add a rule for order date, and choose a date. You can see the word date in front of the string for our isoformatted date.