Intro
You already know how to create nested database queries in the database query builder in order to access nested data in a single database query.
In this tutorial we will show you a more advanced examples, using 3 levels of nested data and database joins. We want to display courses, the students enrolled in each course and also all the courses each of the student is enrolled in.
This means the final result will be a 3 level nested query:
> Course Name
> Student
> All the courses student is enrolled in
Database Setup
Our database tables are quite simple.
Courses table - containing course id, name and description columns:
Students table - containing student id, name and email columns:
And a multi reference, called enrollments created under the courses table. It references course id with student id:
API Action
Let’s create a database query and see what results are returned.
Open the Server Connect panel and then add new API Action:
We call it courses_list:
Add a new step inside it:
We add a Database Query:
And open the query builder:
In the query builder we select the courses table:
And add all the columns, including the enrollments sub table:
Click OK:
Save the API Action and let’s preview the results in the browser:
This basic query returns the courses and shows the nested data from the enrollments sub table. The sub table returns the course id and the ids of the students enrolled in this course:
That is the expected result. We’d like to also show the name, email and courses for each of the students in this same query.
Go back to the query builder.
Double click the sub table name:
This opens a query builder for the sub table. In order to show the students info (name, email and courses) we need to create a table join with the students table. Open the table dropdown:
and select the students table:
This creates a table join automatically:
Select the columns you want to show per each of the students. In our case - name, email and course enrollments:
Click OK to close the sub query builder:
And click OK:
Save the API Action and let’s preview the results in the browser again. You can see that now under each course we have more info listed about each of the students - name, email and the ids of all the courses this student is enrolled in:
The final step would be to display the names of the courses under each student instead of just their ids. So let’s go back to the query builder. Double click the course_enrollments sub table:
And then double click the students sub table:
Here in this sub level we need to create a join with the courses table, so that we get the names for each of the courses. Open the table dropdown:
Select the courses table:
And add the course name column:
Click OK, save your API Action and let’s preview the results in the browser again.
Here are our final results. You can see each course (red), the enrollments/students per each course (blue) and the courses each of the students is enrolled in (green):
With the structure returned this way in a single query it makes it really easy to use the data on your pages. This way you can have unlimited depth of nested queries.
Last updated: