Query All Records - Filter, Search, Sort, Aggregate, Joins & More!

The Query All Records function is arguably the most powerful and flexible tool in Xano. It allows you to query all records from a Xano database table while giving you extensive customization options. In this guide, we'll explore how to leverage the various features of this function to shape your data as per your requirements.

By Custom Query: Filtering and Searching Records

The "By Custom Query" section enables you to filter or search through your Xano database table using conditional statements. Here's how you can use it:

1. Add Conditions: Click on the "By Custom Query" section and start adding `WHERE` conditional statements. You can chain these conditions together using `AND`, `OR`, and `GROUP` operators.
2. Select Fields: On the left side of the condition, select the field from your database table that you want to filter on.
3. Choose Operators: Use the appropriate operator (`=`, `!=`, `>`, `<`, `>=`, `<=`, `LIKE`, `NOT LIKE`, `IN`, `NOT IN`, `REGEX`, `CONTAINS`, `OVERLAPS`) based on your filtering criteria.
4. Use Dynamic Values: Make your queries dynamic by using inputs or variables from your function stack instead of hard-coded values.
5. Apply Filters: Transform the data you're filtering on by using the available filters within the "By Custom Query" section.
6. Ignore Empty Values: If you want to return all records when a search input is empty, check the "Ignore Empty Values" box.

By mastering the "By Custom Query" feature, you can create robust and flexible searches tailored to your application's needs.

Customizing the Output

The "Output" tab offers several options to customize the response from the Query All Records function:

1. Customize Response Fields: Use the "Customize" button to select which fields you want to include in the response.
2. Add Related Data with Add-Ons: Decorate your response with related data from other tables using the "Add-Ons" feature. This allows you to extend the response with additional information based on foreign key relationships.
3. Change Return Type: Instead of returning a list of records (default), you can change the return type to:

• `Number of Items`: Returns the count of records matching your query.
• `Existence`: Returns a boolean value indicating if any record matches your query.
• `Count`: Returns the count of records matching your query.
• `Single`: Returns a single object instead of a list, useful when you only need one record.
• `Aggregates`: Groups records by specified fields and performs aggregations like `COUNT`, `SUM`, `MAX`, `MIN`, etc.

1. Sort and Page Results: Add sorting and paging options to control how the records are ordered and divided into pages.
2. Use Evals: Create new fields in the response by transforming or combining existing fields using the "Evals" section.

By customizing the output, you can tailor the response to meet your application's specific needs, making it easier to work with the data on the frontend or in other parts of your application.

Joining Tables and Filtering on Related Data

The Query All Records function allows you to join multiple tables and filter records based on data from related tables. Here's how you can achieve this:

1. Add Joins: In the "By Joins" section, select the table(s) you want to join with your main table.
2. Set Join Conditions: Specify the conditions for the join, typically matching the foreign key field from the main table with the primary key field from the related table.
3. Filter on Joined Data: Once the join is set up, you can filter records using fields from the joined table(s) in the "By Custom Query" section.
4. Bind Object Arrays: If your database schema includes object arrays, you'll need to bind them in the "By Joins" section before you can filter on their nested fields.

Joining tables and filtering on related data allows you to create complex queries and retrieve records based on relationships between different datasets.

External Overrides for Front-End Integration

Xano provides the ability to override certain aspects of the Query All Records function from your front-end application. This can be useful when you want to dynamically control filtering, sorting, or paging based on user interactions or other front-end logic.

1. Enable External Overrides: In the "External" tab, select the aspects you want to enable for external overrides (e.g., filtering, sorting, paging).
2. Map Input Values: Create input variables in your Xano function and map them to the corresponding external configuration fields.
3. Pass Values from Front-End: From your front-end application, pass the desired values for filtering, sorting, or paging as input parameters when calling the Xano API endpoint.

By leveraging external overrides, you can build more dynamic and interactive applications where the back-end query is influenced by front-end actions or user input.

The Query All Records function in Xano provides a wealth of features and customization options, empowering you to retrieve and shape your data according to your specific requirements. Whether you're a no-code enthusiast, citizen developer, traditional developer, or part of a startup/small business, mastering this function will streamline your backend development process and help you build powerful applications with ease.