External Filtering: How to Dynamically Assign Columns in the Custom Query

Welcome to another Xano tutorial! Today, we'll explore a powerful feature called "external filtering" that allows you to dynamically assign columns and operators in your custom queries. This comes in handy when you need to filter your data based on user input or changing conditions. Let's dive in!

Setting Up Dynamic Inputs

First, let's create some inputs that will represent the different parts of our filter:

1. Create an input called left for the column name
2. Create an input called operator for the operator (e.g., =, >, <, etc.)
3. Create an input called right for the value to filter by

Creating the Filter Variable

Next, we'll create a variable that will hold our dynamic filter expression. Follow these steps:

1. Head over to the External Filtering documentation and copy the JSON model provided
2. In your Xano project, create a new variable called filter
3. Paste the JSON model as the value of the filter variable
4. Map the left, operator, and right inputs to their respective placeholders in the JSON model

Your filter variable should now look something like this:

{
 "filters": [
 {
 "left": "{{left}}",
 "op": "{{operator}}",
 "right": "{{right}}"
 }
 ]
}

Using the Filter in Your Query

Now that we have our dynamic filter set up, we can use it in our custom query:

1. In your query function, navigate to the "External" tab
2. Locate the "Search" field and reference your filter variable there

That's it! You can now test your dynamic filter by providing different values for the left, operator, and right inputs. For example, if you set left to "userId", operator to "=", and right to "2", your query will only return records where the userId column is equal to 2.

Adding Multiple Expressions

But what if you need to filter based on multiple expressions? No problem! Here's how you can do it:

1. Create a new variable called filter2 with the following value:

{
 "external": {
 "expression": [
 {
 "left": "{{left}}",
 "op": "{{operator}}",
 "right": "{{right}}"
 },
 {
 "left": "{{left2}}",
 "op": "{{op2}}",
 "right": "{{right2}}"
 }
 ]
 }
}

2. Add additional inputs for left2, op2, and right2
3. In your query function, update the "Search" field to reference both filter and filter2:

{{filter}}.addition.{{filter2.external}}

Now, you can filter based on multiple expressions by providing values for all the input fields.

Using OR Instead of AND

By default, multiple expressions are combined using the AND operator. However, you can change this to OR by adding the following line to your filter2 variable:

"or": true

This way, your query will return records that match either of the provided expressions.

That's it! You now know how to dynamically assign columns and operators in your custom queries using external filtering. This powerful feature opens up a world of possibilities for building flexible and user-friendly applications. Keep exploring and happy coding!