The filter tab allows you to filter which records are returned when you run the Query All Records function.
There are two methods that you can use on the filter tab: By Joins and By Custom Query.
Joins let you perform conditionals on other table properties that reference your primary table.
There are 3 options for joins:
An inner join is where you are able to combine two tables based on if they have two of the same properties.
For example: if there are two tables: Tutorial table and Video table and both have an owner_id column, I could get the data from both tables where they share the same owner_id value.
The left join returns a complete set of rows from the left table with the matching rows if available from the right table. If there is no match, the right side will have null values.
The right join or right outer join is a reversed version of the left join. It produces a result set that contains all rows from the right table with matching rows from the left table. If there is no match, the left side will contain null values.
By Custom Query
There is the option to build a custom query, which allows you to define rules on how you want the records queried. The first step is to configure what the API searches for. You can configure your search based on the different columns of your database table. The left side of the configuration is the column you would like to use. The middle part is the operator where you can select: equals, does not equal, greater than, greater than or equal, less than, less than or equal. The right side is the value that you want to compare the left side to.
Here is an example of a configured search:
In this example, we are searching the Tutorial database for all of the records where the views count is greater than or equal to 5.
We can make more complex queries using additional conditionals. Clicking on the blue plus button adds more options like "And", "Or", "And()", "Or()".
"And": adds to the search where it must match all of the conditions.
"Or": adds to the search where it must match either of the conditions.
"And()": adds to the search where it must match all of the conditions as well as allowing the user to add an additional set of conditions that are wrapped within the parenthesis.
An example of this would be: and( the id < 100 and the id > 30) combined with our other example we would have: Where the number of views is > 5 and (30 < tutorial id < 100).
"Or()": adds to the search where it must match either of the conditions as well as allowing the user to add an additional set of conditions that are wrapped within the parenthesis.
Additional filtering can be added to the left or right side depending on the Data Type.
Joins to Search Arrays
The joins option also allows you to search the table you’re querying by array or object array. Joining the array of the current table allows you to perform searches using that array’s values.
As an example, imagine you have a table by the name of goal_info. Inside exists a column by the name of statistics, and this column is an array of objects with each object having the property pass_percentage.
Now you want to find all of the records that hold an object with a pass_percentage greater than 40%.
To accomplish this, click on the BY JOINS pencil in the top right-hand corner of the filter panel. In the Table dropdown, at the bottom, select this table’s statistics column.
Be sure to save, and then navigate towards the BY CUSTOM QUERY, and click on the pencil. As an argument, you’ll now see the individual properties of the object schema. Selecting the pass_percentage adds it as an argument. Now, we can finish this statement by evaluating this argument to match our 40% criteria.