Nested list view grouped by multiple levels

App Building
1

I'm trying to display a list of course titles, and for each course title the name of each person who has taught that course, then for each person, a list of details for each specific time they have taught that course. I've built a nested list view using Query JSON with SQL, based on the solution here: Turning query/table into nested list view by category. My current query looks like this:

select course_name, array(facilitators) AS facilitator, array(@{file_number: file_number, start_date: start_date, location: location}) as details from {{table4.displayedData}} group by course_name

The problem comes in when I try to group by facilitator as well (by adding ", facilitator" after course_name in the group by clause. What I want to see is:

Course 1
-- Person 1
---- Instance 1
---- Instance 2
-- Person 2
---- Instance 3

Instead I'm getting:

Course 1
-- Person 1
---- Instance 1
-- Person 2
---- Instance 3
-- Person 1
---- Instance 2

My guess is that it has something to do with facilitator being an alias for an array, but I'm stuck on where to go from here. I'd be grateful for any advice. Thanks!

2

I've been playing around with this for a few days and still haven't found a solution, but this is closer to what I am trying to do (if only it would return any rows):

SELECT course_name, array(@{SELECT facilitators, array(@{file_number: file_number, start_date: start_date, location: location}) as details FROM {{report_fac_by_course.data}} GROUP BY facilitators}) as facilitator FROM {{report_fac_by_course.data}} GROUP BY course_name;

So, I'm trying to create an array within another array within a Group By query, which I will then display in a list view with 3 levels. If it helps, the query the data is coming from is this:

SELECT courses.course_name,
persons.person_id,
CONCAT (persons.first_name," ",persons.last_name) AS 'facilitators',
schools.file_number,
schools.start_date,
CONCAT(cities.city_name,", ",provinces.province) AS 'location'
FROM schools
LEFT JOIN courses ON schools.course_id = courses.course_id
LEFT JOIN facilitators_schools ON schools.school_id = facilitators_schools.school_id
LEFT JOIN facilitators ON facilitators_schools.facilitator_id = facilitators.facilitator_id
LEFT JOIN persons ON facilitators.person_id = persons.person_id
LEFT JOIN cities ON schools.location = cities.city_id
LEFT JOIN provinces ON cities.province = provinces.prov_id
WHERE EXTRACT(YEAR from schools.start_date) = {{fac_by_course_year_search.value}}
ORDER BY course_name, facilitators, SUBSTRING(schools.file_number,4,2), SUBSTRING(schools.file_number,1,3);

3

Hey @LindenKel!

This is really interesting :thinking: It looks like you may be able to do the grouping in steps, let me know if something like this works:

SELECT course_name, array(@{facilitator: facilitators, details: details}) as facilitators 
FROM (
  SELECT course_name, facilitators, array(@{file_number: file_number, start_date: start_date, location: location}) as details
  FROM {{report_fac_by_course.data}}
  GROUP BY course_name, facilitators
) GROUP BY course_name;

I've included an updated version of the app in the thread you linked that adds some additional grouping. For those who are curious you might also try using lodash to do some nested grouping if you're looking to use JS:

const sportGroups = _.groupBy(sampleData.data, "sport")
const teacherGroups = _.mapValues(sportGroups, (group) =>
  _.groupBy(group, "facilitator")
)

return Object.entries(teacherGroups).map(([sport, facilitators]) => ({
  sport,
  facilitators: Object.entries(facilitators).map(([facilitator, signups]) => ({
    facilitator,
    signups,
  })),
}))

nested-20listview-20transformation (1).json

4

Thanks so much @Kabirdas, the solution works! I did have to put the data from the original (mySQL) query into a table and replace {{report_fac_by_course.data}} from the solution with {{table.displayedData}}, because otherwise I was getting 'undefined' for my results, but I suspect that has something to do with the way the original query is filtered.

5

Ah you might want to try using {{ formatDataAsArray(report_fac_by_course.data) }}. Since it's a SQL query it returns data as an object of arrays instead of an array of objects (which AlaSQL expects). The helper functions are particularly useful for converting between the two!

1 Like
6

Thanks, that did it. The final query is:

SELECT course_name, array(@{facilitator: facilitators, details: details}) as facilitators
FROM (
SELECT course_name, facilitators, array(@{file_number: file_number, start_date: start_date, location: location}) as details
FROM {{formatDataAsArray(report_fac_by_course.data)}}
GROUP BY course_name, facilitators
) GROUP BY course_name;

1 Like