Query All Records
Last updated
Last updated
Query All Records is a powerful function. In its default state, it retrieves all the records from the specified database table. However, it also gives you full control over how that data is queried with things like joins, filtering, returns types, customized output, and more.
Query All Records will be often used if you need to display a list of data somewhere. It can also be used, in combination with filtering, to perform things like searches. Additionally, the Query All Records function is extremely useful when needing to filter or find records in your database table to do other manipulation on.
There are 4 tabs of the Query all records functions:
Filter allows you to pick and choose which records are retrieved when you run this API. You can use the BY JOINS option to perform conditionals on other table properties. There are 3 options for the joins: Inner: 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 my Tutorial table and my video table both have an owner_id column, I could get the data from both tables where they share the same owner_id value. Left: 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. Right: 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. 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.
Evals are a method of adding additional fields from the joined table in the final response. In the below example, we have joined our User table with our Products table, and want to include the created_at field in our response when querying the User table. In cases where you are unable to use an addon, or would like something more lightweight--for example, if you only have a few fields to add to the response or continue to work with in the function stack, use an eval.
The Output tab controls the response of the Query All Records function. Customize allows you to tailor your output to make it exactly what you want it to be. This helps limit the amount of data and apply sorting.
Please note that sorting is disabled by default on Query All Records.
Addons can be added to the output to make API calls more powerful by joining an additional database table's information to the Response. These help to make retrieving data easier with one call instead of multiple.
External is the section where you can enable external override for sorting, filtering, and paging/pagination of your data. External override means you can enable your front-end to override how your data is filtered, sorted, and paged.
