Record relations with custom fields on Skygear Cloud Database


#1

Is it possible to create a relationship between records that includes a custom field? Or is it necessary manually create and manage a relationship table for this case?

For example, I want to create a relation between a User and a Team. However, I want to be have a “role” that describes this relationship.

I could imagine something like this (assuming we have a user record ‘someUser’)’:

const team = new Team({
  name: 'My team'
});
const member = new skygear.Reference(someUser, {role: 'guest'});
team.member = member;
skygear.publicDB.save(team);

Looking at the docs this is clearly not possible:

Is there another way?


#2

Hi @daniel ,

This is what I picture your expected result:

Here’s a suggested way implementing the relationship.

1. Create Teams

First, let’s create a Team first. You can do so with code below, you can add more teams later.

let Team = skygear.Record.extend('team');
var skygearTeam = new Team({
    name: 'Skygear team'
    });

skygear.publicDB.save(skygearTeam);

2. Create Roles

Then we can define some roles.
Note: skygear has a default table called _role, so I advice you to use another record name to avoid confusion. Let say jobrole .

let Role = skygear.Record.extend('role');

var managerRole = new Role({
  role: 'Manager',
  canSeeReport: true,
  canFirePeople: true
});

var clerkRole = new Role({
  role: 'Clerk',
  canSeeReport: false,
  canFirePeople: false
});

skygear.publicDB.save([managerRole, clerkRole]);

3. Assign Members in the Team, with a role

After we have initial Teams and Roles, we can assign members to a team.
We will be using a TeamMember relation to map the relationship. We make user, team and role records we’ve saved as references in the TeamMember Table.

let Team = skygear.Record.extend('team');
let TeamMember = skygear.Record.extend('TeamMember');
let User = skygear.Record.extend('user');
let Role = skygear.Record.extend('role');


let member = skygear.auth.currentUser; // for easy illustration, you can use any user record
let skygearTeam = new Team({id: 'team/c4fea758-23f0-4d75-9185-fdea6ab65c60'}); // the previous saved team record
let role = new Role({id: 'role/56f1afca-01e2-4a6b-aef9-fc4406f4c648'}); // the previous saved manager role record

var teamMember = new TeamMember({
  team: new skygear.Reference(skygearTeam),
  member: new skygear.Reference(member),
  role:  new skygear.Reference(role)
});

skygear.publicDB.save(teamMember).then((result) => {
  console.log(result);
}).catch((error) => {
  console.log(error);
});

4. Retrieving all users in a team

Afterall, it’s easy to query TeamMember on the condition where team attribute matches the team id given.

Note that I’ve also added transientInclude on the query, so the member and team detail is also returned in a single query.

let TeamMember = skygear.Record.extend('TeamMember');
let User = skygear.Record.extend('user');
let Role = skygear.Record.extend('role');


let query = new skygear.Query(TeamMember);
query.equalTo('team', givenTeamId);
query.transientInclude('member');
query.transientInclude('team');

skygear.publicDB.query(query).then((r) => {
    console.log(r);
}).catch((error)=>{
  console.log("ERROR");
})

Discussion

For easier implementation: If you just want to save the role without the settings, you can just mark it using string value like “Manager”, “Clerk”, “Admin” etc.

That’s how you can map the User in Team relation. Please try and let me know whether it works for you :wink:


#3

Hi David,

Thanks for the thorough response - this worked perfectly :slight_smile:

One additional concern I had is that, in the case that I wanted to edit or delete a Team record, I’d like the corresponding entry in TeamMember to be automatically updated or deleted.

This was solved by manually changing the foreign key constraints on the TeamMember table to include “ON UPDATE CASCADE” and “ON DELETE CASCADE”.

Cheers,
Daniel


#4

Hi @daniel,

A more proper way (or a more Skygear way) is using database hook.
We can use afterDelete hook in cloud function for this case. (I guess updating a team won’t affect the TeamMemeber record, since it’s a reference)

It looks like this:

skygearCloud.afterDelete('team', function(record, original, pool, options) {
    // Remove all TeamMember where the given team matches
}, {
    async: false
});

Cheers,
David


#5

Good to know, thanks for the tip!