Turning query/table into nested list view by category

App Building
,
1

Hello!

I've been trying to make a nested list view for an app which takes two columns of a table, one which determines the first level (in this example sport) and the second one which determines the second level (in this example name):

+-------+--------+
| Name  | Sport  |
+-------+--------+
| Bob   | Golf   |
+-------+--------+
| Kate  | Tennis |
+-------+--------+
| Sarah | Golf   |
+-------+--------+
| Ali   | Golf   |
+-------+--------+
| Joe   | Tennis |
+-------+--------+

My goal is to generate something along these lines with a nested list:

Golf
->Ali
->Bob
->Sarah

Tennis
->Joe
->Kate

I am correct in thinking I'll first need to transform the data in some way before being able to use the data in the nested list? If so, what kind of methods should I be looking into? I've been searching far and wide but haven't been able to progress much yet.

If anyone could point me in the right direction I'd be very grateful! Thanks!

2

Hey @AM-BE!

There are a number of ways you can do this, here's one:

An array of objects that each have two properties: sport and names seems like a pretty convenient way to store nested data.

First, let's make sure to structure the original data as an array of objects, using the formatDataAsArray helper function if necessary.

Now, if you prefer to use SQL you can pass your data to a Query JSON with SQL query. That looks something like this:

select sport, array(name) as names from {{sampleData.data}} group by sport

Otherwise, if you rather use JavaScript we can something like:

const aggregatedNames = sampleData.data.reduce((aggregator, row) => {
  const nameArray = aggregator[row.sport] ?? [];
  aggregator[row.sport] = [...nameArray, row.name];
  return aggregator;
}, {})

return Object.entries(aggregatedNames).map(([sport, names]) => ({sport, names}));

Either way, the resulting object should take the following shape:

From there you can use the nested listview indexing syntax to grab the correct index for each level. Instead of using i you can use ri[x] :

  • ri[0] is the first level
  • ri[1] is the second level
  • ri[2] is the third level

For instance, if you want to list the name in a text component you'd do something like

{{transformedDataSql.data[ri[0]].names[ri[1]]}}

Attached is a sample app you can play around with!

Let me know if that helps or raises any further questions :slightly_smiling_face:
nested-20listview-20transformation (1).json

2 Likes
3

Thanks @Kabirdas for your explanation! Very clear!

4

@Kabirdas - Thank you this, this helped me to build my first nested lists.

One additional question, is it possible to have more than one item within the array? For example, I would like to have Ticket, task and description.

The closest I have got is to have task and description as part of a single string

SELECT Ticket, array(Task + ' - ' + Description) as Tasks from {{TicketstoArray.data}} group by Ticket

It would be good to have them as separate items to have more flexibility on the layout.

image
image749×447 8.09 KB

5

Yep! It's possible to have the array you're generating be an array of objects instead of just values. Using Query JSON with SQL that would look something like:

select sport, array(@{name: name, day: day}) as signups from {{sampleData.data}} group by sport

Then to access a property you'd use something like this:

{{transformedDataSql.data[ri[0]].signups[ri[1]].name}}

Here's an updated example to play around with that also includes the JavaScript version!
nested-20listview-20transformation.json

1 Like
6

Brilliant! Thanks @Kabirdas, that works perfectly. I'll need to do some research on the syntax to understand it fully, but really appreciate your help. :+1:

7

Good to hear it works! :slightly_smiling_face: Query JSON with SQL uses AlaSQL under the hood - that particular notation came from this part of their wiki!

1 Like