Build a Query in MuleSoft With Optional Parameters

This tutorial article demonstrates a simple approach to building a query in MuleSoft using optional parameters for both filtering and sorting.

Daniela Mogini

Updated May. 03, 22 · Tutorial

Likes (5)

Comment

Save

8.3K Views

In this tutorial we will see how to:

Define a RAML API specification to search products in a catalog database
Establish and configure a database connection to query a MySQL database
Build a query to support multiple optional filters
Add optional sorting capabilities to our query at DB level

1. Define the RAML of a Search API

Imagine that you want to create a MuleSoft API to search products inside a catalog. This API will need to accept multiple filter parameters and an optional sorting parameter. All these parameters can be optional and will be used to generate a dynamic query to search products in your catalog database.

1.1 RAML Definition

The RAML definition of this API could be like the following:

    YAML
   
 

   #%RAML 1.0
title: search-products-demo

types:
  SearchProductRequest:
    type: object
    properties:
      season?: string
      size?: string
      line?: string
      category?: string
      subcategory?: string

/search-products:
  post:
    displayName: Search products
    body:
      application/json:
        type: SearchProductRequest
    responses:
      200:
        body:
          application/json:
            type: array
            items:
              type: object
  

In this example, you can use season, line, category, subcategory, and size to filter products in your catalog. All these parameters are optional.

2. Flow Implementation

Create a new mule application project, add the API specification defined from the RAML, and allow the scaffolding process to complete.

2.1 Configure the Database Connection

Drag a new database select component from the palette and add a new database configuration object to configure the connection.

In this example, we will use a MySQL connection.

Add the required Maven dependency from Exchange.

Configure the connection properties and click OK.

2.2 Write the Query to Search Your Products

Now it’s time to add the first version of our query that will take all the input parameters from the API. In step three, we will then refactor this query to accept empty filters.

    SQL
   
   SELECT * FROM db_demo.product_catalog
WHERE category = :category
AND subcategory = :subcategory
AND line = :line
AND season = :season
AND size = :size;

Bind input parameters. To provide input parameters, we read values from the payload of the Ingress API call and assign them to the input parameters of our query

    JSON
   
 

   {
    "category": payload.category,
    "subcategory": payload.subcategory,
    "line": payload.line,
    "season": payload.season,
    "size": payload.size
}
  

2.3 Add a Transformation to Return Back the Query Results in JSON Format

Add a transformer component with output application/JSON and this simple transformation logic:

%dw 2.0
output application/json
---
{
"result_count": sizeOf(payload),
"products": payload
}

2.4 Test the First Version of the API

At this point, the flow implementation should look like this:

We can use our favorite REST API client to test the first version of the API.

However, if we try to remove one or more filters from the request payload, we get no results.

3. Refactoring the Query to Work With Missing Filters

In this step, we are going to refactor our query in order to accept requests with missing filters.

3.1 Refactoring the Query

The key idea is to modify our filtering conditions by adding an OR statement to check if the filter is populated or not.

Example: AND (subcategory = :subcategory) becomes

AND (1 = :emptySubcategoryFilter OR subcategory = :subcategory)

In this way, if the filter is missing, the first condition of the OR statement will always be met and the filter will have no effect on the query results.

Refactoring all the filters, our query becomes:

    SQL
   
 

   SELECT * FROM db_demo.product_catalog
WHERE (1 = :emptyCategoryFilter OR category = :category)
AND (1 = :emptySubcategoryFilter OR subcategory = :subcategory)
AND (1 = :emptyLineFilter OR line = :line)
AND (1 = :emptySeasonFilter OR season = :season)
AND (1 = :emptySizeFilter OR size = :size);
  

3.2 Populating the New Input Parameters to Check if the Filters Are Missing

To populate the new input parameters that we use to check if a filter is missing, we can use the ? operator to check if the filter attribute is contained in the payload of the request.

    JSON
   
 

   {
    "category": payload.category,
    "emptyCategoryFilter": if (payload.category?) 0 else 1,
    "subcategory": payload.subcategory,
    "emptySubcategoryFilter": if (payload.subcategory?) 0 else 1,
    "line": payload.line,
    "emptyLineFilter": if (payload.line?) 0 else 1,
    "season": payload.season,
    "emptySeasonFilter": if (payload.season?) 0 else 1,
    "size": payload.size,
    "emptySizeFilter": if (payload.size?) 0 else 1
}
  

Now we can test the API with missing filters again and see that it returns results, even with missing filters.

4. Adding Sorting Capabilities

In a similar way, we can add optional sorting capabilities to our API.

We will use the ORDER BY function at a database level (instead of ordering our results in DataWeave) because this approach can be useful to support large result sets use cases, limiting the number of results and providing pagination capabilities.

To do that we need to first modify our RAML API spec to add two new parameters for the sorting attributes and the sorting direction.

4.1 Adding Sorting Parameters to Our RAML API Spec

sorting_attribute will be a string.

sorting_direction will be an enum with ASC and DESC as possible values.

Both parameters will be optional.

    
  

   #%RAML 1.0
title: search-products-demo

types:
  SearchProductRequest:
    type: object
    properties:
      season?: string
      size?: string
      line?: string
      category?: string
      subcategory?: string
      sorting_attribute?: string
      sorting_direction?: 
        type: string
        enum: [ASC, DESC]

/search-products:
  post:
    displayName: Search products
    body:
      application/json:
        type: SearchProductRequest
    responses:
      200:
        body:
          application/json:
            type: array
            items:
              type: object

  

4.2 Modifying the Flow Implementation to Support Sorting Parameters

After updating the API version in our project, we will need to slightly modify our implementation.

Store the sorting attribute in a variable sortAttr using the default to provide a value even if the sorting_attribute is missing from the original query.

In the same way, store the sorting direction attribute in a variable providing a default “ASC” direction.

Store the query in a variable and add an ORDER BY clause using the newly create variables for sortAttr and sortDir.

Modify the database select operation to read the query from a variable

Attention! Be careful with this approach of building queries by concatenating variables because, if misused, it can lead to SQL injection problems. Use parameter binding whenever possible!

In our example, we used variable concatenation only to build the ORDER BY clause, where it was not possible to use parameter binding like we did for all the other filter params.

4.3 Test the API With Sorting Parameters

Test the API with the following:

    
       "sorting_attribute": "line",
    "sorting_direction": "DESC"

It returns “Woman” products first, followed by “Man” products.

5. Wrap-Up: The Final Project

In this tutorial we showed how to:

Define a RAML API specification to search products in a catalog database
Establish and configure a database connection to query a MySQL database
Build a query to support multiple optional filters
Add optional sorting capabilities to our query at DB level

The key approach that we adopted to support optional filtering was to refactor our query in order to add an OR condition to check if the filter is present or if it missing

For example, AND (subcategory = :subcategory) becomes

AND (1 = :emptySubcategoryFilter OR subcategory = :subcategory).

To add optional sorting capabilities, we had to use a slightly different approach because we wanted to use an ORDER BY clause in our query but it was not possible to build it using parameter binding.

Here the strategy was to implement the ORDER BY clause by concatenating the fixed part of the query (where we can use parameter binding) with the variables to define sorting attributes and sorting directions.

It was also highlighted that this strategy has to be adopted carefully, only when parameter binding cannot be used, in order to prevent SQL injection issues.

The Final Project

    XML
   
 

   <?xml version="1.0" encoding="UTF-8"?>
<mule xmlns="http://www.mulesoft.org/schema/mule/core" xmlns:apikit="http://www.mulesoft.org/schema/mule/mule-apikit" xmlns:db="http://www.mulesoft.org/schema/mule/db" xmlns:doc="http://www.mulesoft.org/schema/mule/documentation" xmlns:ee="http://www.mulesoft.org/schema/mule/ee/core" xmlns:http="http://www.mulesoft.org/schema/mule/http" xmlns:json-logger="http://www.mulesoft.org/schema/mule/json-logger" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/current/mule.xsd http://www.mulesoft.org/schema/mule/json-logger http://www.mulesoft.org/schema/mule/json-logger/current/mule-json-logger.xsd http://www.mulesoft.org/schema/mule/mule-apikit http://www.mulesoft.org/schema/mule/mule-apikit/current/mule-apikit.xsd http://www.mulesoft.org/schema/mule/http http://www.mulesoft.org/schema/mule/http/current/mule-http.xsd http://www.mulesoft.org/schema/mule/ee/core http://www.mulesoft.org/schema/mule/ee/core/current/mule-ee.xsd http://www.mulesoft.org/schema/mule/db http://www.mulesoft.org/schema/mule/db/current/mule-db.xsd">
    <apikit:config name="search-products-demo-config" api="resource::bc191955-0c23-499c-8429-5392d1c3c041:search-products-demo:1.0.1:raml:zip:search-products-demo.raml" outboundHeadersMapName="outboundHeaders" httpStatusVarName="httpStatus" />
    <db:config name="Database_Config" doc:name="Database Config" doc:id="16b67dda-b66e-4327-9c25-071f022f8b17">
        <db:my-sql-connection host="${secure::db_host}" port="${secure::db_port}" user="${secure::db_user}" password="${secure::db_password}" database="${secure::db_database}" />
    </db:config>
    <flow name="search-products-demo-main">
        <http:listener config-ref="search-products-demo-httpListenerConfig" path="/v1/*">
            <http:response statusCode="#[vars.httpStatus default 200]">
                <http:headers><![CDATA[#[vars.outboundHeaders default {}]]]></http:headers>
            </http:response>
            <http:error-response statusCode="#[vars.httpStatus default 500]">
                <http:body><![CDATA[#[payload]]]></http:body>
                <http:headers><![CDATA[#[vars.outboundHeaders default {}]]]></http:headers>
            </http:error-response>
        </http:listener>
        <set-variable value="#[payload]" doc:name="Set Original Payload" doc:id="08131364-c610-47a1-9a92-de70c6c8b702" variableName="originalPayload" />
        <apikit:router config-ref="search-products-demo-config" />
    </flow>

    <flow name="post:\search-products:application\json:search-products-demo-config">
        <logger level="INFO" message="post:\search-products:application\json:search-products-demo-config ++ #[payload]" />
        <set-variable value='#[payload.sorting_attribute default "id"]' doc:name="Set Variable sortAttr" doc:id="21f415e6-50ea-4ac6-b23d-721b6d4aa3ec" variableName="sortAttr"/>
        <set-variable value='#[payload.sorting_direction default "ASC"]' doc:name="Set Variable sortDir" doc:id="db5896be-cc5c-4125-bfad-21d38875e113" variableName="sortDir"/>
        <set-variable value='#["SELECT * FROM db_demo.product_catalog
WHERE (1 = :emptyCategoryFilter OR category = :category)
AND (1 = :emptySubcategoryFilter OR subcategory = :subcategory)
AND (1 = :emptyLineFilter OR line = :line)
AND (1 = :emptySeasonFilter OR season = :season)
AND (1 = :emptySizeFilter OR size = :size)
ORDER BY " ++ vars.sortAttr ++ " " ++ vars.sortDir ++ ";"]' doc:name="Set Variable query" doc:id="55a2306d-38d2-45b2-a607-e7f82cee3c4a" variableName="query"/>
        <db:select doc:name="Select" doc:id="aa376842-cb2d-4d2c-9bbb-a6c0efba81bc" config-ref="Database_Config">
            <db:sql>#[vars.query]</db:sql>
            <db:input-parameters><![CDATA[#[{
    "category": payload.category,
    "emptyCategoryFilter": if (payload.category?) 0 else 1,
    "subcategory": payload.subcategory,
    "emptySubcategoryFilter": if (payload.subcategory?) 0 else 1,
    "line": payload.line,
    "emptyLineFilter": if (payload.line?) 0 else 1,
    "season": payload.season,
    "emptySeasonFilter": if (payload.season?) 0 else 1,
    "size": payload.size,
    "emptySizeFilter": if (payload.size?) 0 else 1,
    "sortingAttr": (payload.sorting_attribute),
    "sortingDir": (payload.sorting_direction)
}]]]></db:input-parameters>
        </db:select>
        <ee:transform doc:name="Transform Message" doc:id="7b5c1e1b-33f4-4035-82af-ce9eb474a3b9">
            <ee:message>
                <ee:set-payload><![CDATA[%dw 2.0
output application/json
---
{
    "result_count": sizeOf(payload),
    "products": payload
}]]></ee:set-payload>
            </ee:message>
        </ee:transform>
    </flow>
</mule>
  

API Database connection MuleSoft Sorting Build (game engine) Filter (software)

Opinions expressed by DZone contributors are their own.

Related

Trending