Customer Corner - Generate CSV when your Query Contains Addons

Hey there! In this guide, we'll walk through how to generate a CSV file from a query that involves add-ons in Xano. While it may seem a bit tricky at first, we'll break it down into easy-to-follow steps.

Step 1: Set up Your Query

First, let's take a look at the query we'll be working with. We're querying the merchants table and using two add-ons: deal of merchant and ledger of deal of user.

The structure looks like this:

• merchants table
• deal of merchant add-on (related to deals table)
• ledger of deal of user add-on (related to ledger table and users table)

So, we have a merchants table with a related deals table, which in turn has a related ledger table (also connected to the users table).

Step 2: Understand the Output

When we run the query, the output looks something like this:

[
 {
 "id": 1,
 "name": "Merchant A",
 "description": "A sample merchant",
 "deal of merchant": [
 {
 "id": 1,
 "amount": 10.99,
 "ledger of deal of user": [
 {
 "id": 1,
 "userId": 1,
 "dealId": 1
 }
 ]
 }
 ]
 },
 // more results...
]

As you can see, we have a list of merchants, each with a list of related deals. And for each deal, we have a list of associated ledger entries, which include the user ID and deal ID.

Step 3: Get the Keys Separately

To generate the CSV, we need to get the keys separately for each level of the data. We'll do this by creating separate variables for the keys from:

1. The base merchants query
2. The deal of merchant add-on
3. The ledger of deal of user add-on

const baseKeys = getKeys(data);
const dealOfMerchantKeys = getKeys(data, 'deal of merchant');
const ledgerOfDealOfUserKeys = getKeys(data, 'deal of merchant', 'ledger of deal of user');

Step 4: Merge the Keys

Next, we'll merge these separate sets of keys into a single variable:

const keys = [...baseKeys, ...dealOfMerchantKeys, ...ledgerOfDealOfUserKeys];

Step 5: Remove Add-on Names from Keys

Now, we need to remove the keys that match the names of our add-ons. This is necessary because we're dealing with lists, and if we enable the "merge to parent's data" option, we'll end up with keys that have the number 0 (e.g., deal of merchant.0).

const keysWithoutAddons = keys.filter(key => !['deal of merchant', 'ledger of deal of user'].includes(key));

Step 6: Flatten the Data

For each result in our query, we'll use the flattenedFilter function to flatten the multi-level data into a single level:

const rows = data.map(row => {
 const flattenedRow = flattenedFilter(row);
 return [...keysWithoutAddons.map(key => flattenedRow[key] || '')];
});

Step 7: Generate the CSV

Finally, we can generate the CSV file using the setCSVHeaders and toCSV functions:

setCSVHeaders(keysWithoutAddons);
const csvData = toCSV(rows);

And there you have it! You can now download the csvData variable as a CSV file containing the data from your query, including the add-ons.

While it may seem a bit more involved than a simple query, this approach allows you to work with complex data structures and generate CSV files easily in Xano.

If you have any further questions or need additional clarification, feel free to reach out! Happy coding!