Querying fields on link table of Many-To-Many relationships (SQL, GraphQL)

[jacksorjacksor]

Hi there!

Following @JerryNixon 's Relationship Advice from Data API builder, we've been able to create a Many:Many relationship just fine (thanks Jerry!)

One question we had - if we have fields on the joining table (in this example, "dbo.books_authors" - maybe something like a "book_rating" saying how much the author liked that book), how would we go about setting up these relationships in DAB?

In our specific instance we've got Employees and Roles. Employees can have multiple Roles, Roles can be had by multiple Employees. What we also want is to have a RoleLevel (i.e. "DabUser" Level 5), and so have put the RoleLevel on the join table (in our case called "EmployeeRoles"). What would be the best way to generate these relationships, so that we could query what Roles and RoleLevels an Employee had, and what Employees have a specific Role and RoleLevel?

Would we need to create:

• Employee->EmployeeRole
• EmployeeRole->Role
• Role->EmployeeRole
• EmployeeRole->Employee

Would be interested to know the recommended approaches!

Thanks for the great work,

Rich

[severussundar]

Hi @jacksorjacksor,

In order to query the fields, present in the joining table, the following setup has to be configured.

1. The joining table should be configured as an entity in the config file.
   Note: Typically, the joining table need not be exposed as an entity in the config file when creating a many:many relationship. Just specifying the linking.object field would suffice. But, creating the relationship this way, will not enable querying the fields in the joining table.
2. The following relationships need to be created.

a) Employee entity: Relationship with EmployeeRole with cardinality as many
b) EmployeeRole entity: Relationship with Employee with cardinality as one
c) Role entity: Relationship with EmployeeRole with cardinality as many
d) EmployeeRole entity: Relationship with Role with cardinality as one

1. Querying the roles, role levels (and other info about the role) of all employees

{
   employees{
      ...
      employee_roles{
            ....
            role{
               ...
            }
      } 
  }   
}

2. Querying the employees and employee info belonging to each role

{
    roles{
        ...
       role_employees{
          ...
          employee{
              ...
          }
       } 
    }
}

Note: employee_roles in the above example query, is the relationship name configured between
Employee and EmployeeRole entity. Likewise, role_employees is the relationship name configured between
Role and EmployeeRole entity

[jacksorjacksor]

Great, thank you @severussundar