Multi-Hop Worked Examples

In this example, we will explore how data can be accessed across different tables even where the tables do not have a direct relationship. This is called multi-hop and PhixFlow supports multi-hop relationships to a distance of 4 hops. In the below examples we will use the School Data from the Learning Centre, containing tables for Students, Attendees, Courses, Teachers, Departments and Classrooms.

ERD Setup

Example 1: Show Course Details

We want to see the details for each course, including its classroom, students and teacher. This involves 2 hops between the Courses and Students tables.

1. On the ERD, click on the Courses table header to open the table configuration
2. In the Properties, in the Views section, create a new view
   1. Name: CourseDetails
   2. Save the View
3. In the View Attributes section, click the  icon
4. In the Available Attributes window:
   1. From the Courses table, drag across the CourseID and Name attributes into the View Attributes section of the View
   2. Under Related Tables, select the Classrooms table 
      1. Drag across the ClassroomID and Name attributes into the View Attributes section of the View
   3. Under Related Tables, click back onto the Courses table, then onto the Attendees table (this allows us to hop to the Attendee's related tables), then onto the Students table
      1. Drag across the StudentID and Name attribute into the View Attributes section of the View
   4. Click back onto the Courses table using the link above the attribute selector
      
      
   5. Then click onto the Teachers table under Related Tables
      
      1. Drag across the TeacherID and Name attribute into the View Attributes section of the View
         
         
5. Optionally, set a Default Sort Order on the view
6. Display the data in the View by hovering over  More Options and clicking  Display
   

Example 2: Show Courses Attended By Female Students

We want to see all courses attended by female students. This involves 2 hops between the Courses and Students tables.

1. On the ERD, click on the Courses table header to open the table configuration
2. In the Properties, in the Views section, create a new view
   1. Name: StudentsOnCourses
   2. Save the View
3. In the View Attributes section, click the  icon
4. In the Available Attributes window:
   1. From the Courses table, drag across the CourseID and Name attributes into the View Attributes section of the View
   2. Under Related Tables, select the Attendees table - this allows us to hop to the Attendee's related tables
      1. Drag across the StudentID attribute into the View Attributes section of the View
   3. Under Related Tables, select the Students table
      1. Drag across the Name and Gender attribute into the View Attributes section of the View
         
         
5. In the Data Retrieval Options on the View, create a new Default Filter
   1. Filter: create a new filter
      1. Name: FemalesOnly
      2. Filter Details: Gender equals Female
6. Optionally, set a Default Sort Order on the view
7. Display the data in the View by hovering over  More Options and clicking  Display

Example 3: Quantity of Males vs Females on Each Course

This example uses aggregation. We want to see the number of male and female students taking each course. This involves 2 hops between the Courses and Students data.

1. On the ERD, click on the Course table header to open the table configuration
2. In the Properties, in the Views section, create a new view
   1. Name: CourseByGender
   2. Save the View
3. In the View Attributes section, click the  icon
4. In the Available Attributes window:
   1. From the Course table, drag across the CourseID and Name attributes into the View Attributes section of the View
   2. Under Related Tables, select the Attendees table - this allows us to hop to the Attendee's related tables
   3. Under Related Tables, select the Students table
   4. Drag across the Gender attribute twice into the View Attributes section of the View
      
      
   5. Double click on one of the Schools_StudentsGender attributes 
      
      1. On the View Attributes properties:
         1. Name: Males
         2. Aggregate Function: choose Count
         3. Filter: create a new filter
            1. Name: ByMales
            2. Filter Details: Gender equals Male
   6. Repeat these steps for the other Schools_StudentsGender attribute but this time for Females
5. Optionally, set a Default Sort Order on the view
6. Display the data in the View by hovering over  More Options and clicking  Display

Example 4: Quantity of Courses in Each Department

This example uses aggregation. We want to see how many courses are in each department.

1. On the ERD, click on the Department table header to open the table configuration
2. In the Properties, in the Views section, create a new view
   1. Name: CoursesCount
   2. Save the View
3. In the View Attributes section, click the  icon
4. In the Available Attributes window:
   1. From the Department table, drag across the Name attribute into the View Attributes section of the View
   2. To access the Courses table, under Related Tables, select either the Teachers table or Classrooms table
   3. Under Related Tables, select the Courses table
   4. Drag across the CourseID attribute into the View Attributes section of the View
   5. Double click on the CourseID attribute 
      
      1. On the View Attributes properties:
         1. Name: CourseCount
         2. Aggregate Function: choose Count

5. Optionally, set a Default Sort Order on the view
6. Display the data in the View by hovering over  More Options and clicking  Display