Multi-Hop Worked Examples

This example uses the School 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

Image Modified

Accessing Attributes

• On a View on the Courses table, you can access the Department name and Student name

Aggregating on Attributes

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.

Image Added

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 

   Insert excerpt
   _attributes_icon _attributes_icon nopanel true

   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
      
      Image Added
   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
         
         Image Added
5. Optionally, set a Default Sort Order on the view
6. Display the data in the View by hovering over

   Insert excerpt
   _more_options _more_options nopanel true

   and clicking 

   Insert excerpt
   _view_show_icononly _view_show_icononly nopanel true

   Display
   Image Added

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.

Image Added

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 

   Insert excerpt
   _attributes_icon _attributes_icon nopanel true

   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
         
         Image Added
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

   Insert excerpt
   _more_options _more_options nopanel true

   and clicking 

   Insert excerpt
   _view_show_icononly _view_show_icononly nopanel true

   Display

Example 3: Quantity of Males vs Females on Each Course

This example uses aggregation. We want to see the number of males and females on 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 Viewview
   1. Name: CourseByGender
   2. Save the View
3. In the View Attributes section, click the 

   Insert excerpt
   _attributes_icon _attributes_icon nopanel true

   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 access 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 contains 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

   Insert excerpt
   _more_options _more_options nopanel true

   and clicking 

   Insert excerpt
   _view_show_icononly _view_show_icononly nopanel true

   DisplayImage Removed

Example 2: Students on Each Course

Example 4: Quantity of Courses in Each Department

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

Image Added

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 

   Insert excerpt
   _attributes_icon _attributes_icon nopanel true

   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
Image Added
5. Optionally, set a Default Sort Order on the view
6. Display the data in the View by hovering over

   Insert excerpt
   _more_options _more_options nopanel true

   and clicking 

   Insert excerpt
   _view_show_icononly _view_show_icononly nopanel true

   Display