Relations

It’s possible to set up relations between tables. We currently support 3 relation types.

• hasOne
• hasMany
• manyToMany

Relations are defined in the relations key of a table.

hasOne relations

A table with a hasOne relation means that this table has a field(s) that refers to the identifier(s) of another table.

Example:

kontrl.table({
  
  // Other settings

  name: "comments",
  relations: [
    // avatar is the name of the relation
    // avatar_id is the field on this table that refers to a post ID
    // posts is the table with which we're making a relation
    kontrl.hasOne("post").fields("post_id").with("posts")
  ]
});

kontrl.table({
  
  // Other settings

  name: "comments",
  identifier: ["id"]
});

This sets up a relation between users and files where a user has an avatar which is a file.

If you want to refer to another field that is not the “id” field. You can also give that field or those fields as a parameter to the with function.

kontrl.table({
  
  // Other settings

  relations: [
    kontrl.hasOne("post").fields("post_slug").with("posts", ["slug"])
  ]
});

In this case avatar_id will refer to the url field of the files table.

hasMany relations

A table with a hasMany relation means that this table has many other fields which refer to this table.

Example:

kontrl.table({
  
  // Other settings

  name: "posts",
  relations: [
    kontrl.hasMany("comments").with("comments", "post_id")
  ]
});

kontrl.table({
  
  // Other settings

  name: "comments"
});

The above example will give posts a relation comments which will be a list of all comments that have comment.post_id = post.id

If you use another key than “id” or you use a composite key, you can give those key/keys as parameters to the with & fields function.

kontrl.table({

  // Other settings

  relations: [
    kontrl.hasMany("comments").fields(["slug"]).with("comments", ["post_slug"])
  ]
});

Many to many relations

A many relation sits between 3 tables.

1. The table with the relation
2. The join table (containg a reference to both tables)
3. The related table

All 3 tables will have to be defined in Kontrl but the join table can be hidden. (For more information check the tables section).

Example:

kontrl.table({
  
  // Other settings

  name: "users",
  // the first roles is the relation name
  // the seconds "roles" is the table with which we are joining
  // users_roles is the join table
  // user_id is the field on the join table that refers to this table
  // role_id is the field on the join table that refers to the related table (aka to the roles table)
  relations: [
    kontrl.manyToMany("roles").with("roles").through("users_roles", "user_id", "role_id")
  ]
});

// The users_roles table has a user_id and a role_id field.
kontrl.table({
  
  // Other settings

  name: "users_roles"
});

kontrl.table({
  
  // Other settings

  name: "roles"
});

The above code sets up users to have a field roles which has all roles that are defined in the users_roles table.

user_id and role_id can also be arrays with composite keys in the above example.