Understanding Relationships — It isn’t complicated
Directus has several interfaces that allows for data from multiple tables to be connected. In this article, we’ll take a look at the most common ones, what they do, and how to set them up.
Relationships?
When creating a database, it’s often a good idea to separate different types of entities into different tables. For example: storing customers separate from orders. But we also need to have a connection between these two tables — otherwise, we wouldn’t know which customer placed a given order. These connections are called relationships, and they also need to be stored in the database.
There are multiple types of relationships in a relational database, which we’ll go over in a little more detail.
One-to-Many (o2m) & Many-to-One (m2o)
Connecting one item to multiple others — or the other way around — is one of the most used types of relationships in a relational DB. This relationship is used when an item of table A has a relationship to multiple items of table B, but an item of B is connected to only one item of A. For example:
- An author (A) can write multiple articles (B), but each article (B) is written by a single author (A)
- A customer (A) has made multiple orders (B), but each order (B) is placed by just one customer (A)
The difference
There aren’t any differences in the actual implementation of a one-to-many versus a many-to-one relationship. In both instances, you create a field on table B, which holds the unique Primary Key (PK) of an item of table A. The difference in name is about the perspective you’re using. For example:
- An author (A) has written multiple articles (B) (one-to-many)
- Articles (B) are written by a single author (A) (many-to-one)
In both of these examples, you’ll create a field in table B which holds the PK of table A.
Setup in Directus
Directus offers both of these relationships as standalone interfaces. Again, the only difference is the perspective. Do you want to select an author (A) while editing an article (B)? You’ll add a many-to-one interface to the articles (B) table. Would you rather select the articles (B) that an author (A) wrote? You’ll add an one-to-many interface to the authors (A) table.
You can even do both!
There are many more relationships that might be found in this blog example. Next to the articles-authors relationship, you could think of implementing an articles-comments relationship where each comment has its own comment-user relationship or even a user liked article system, which utilizes a many-to-many relationship:
Many-to-Many (m2m)
In some cases, you’ll want to connect multiple items of table A to multiple items of table B, and vice versa. For example — when you are building a system that allows users to like an article — a user can like multiple articles and an article can be liked by multiple users.
Since we can only store a single value per column in either of our main tables, we need to add in another table (a Junction Table) which stores the user’s PK and the article’s PK per row. What we end up with is a table which stores the entity likes. Each like is nothing more than a connection between users and articles:
articles
- idusers
- idlikes
- user_id
- article_id
Example in Directus
Let’s stick to the blog example for this demo. Articles are written by a single author, but authors are also users so they can comment. Therefore we’ll need to add a users table:
articles
- id
- user_id (the author)
- title
- contentusers
- id
- name
I’d like users to be able to leave comments on articles, so we add in a comments table as well. Each comment can only be posted on a single article, and — just like articles — is written by a single user.
articles
- id
- user_id (the author of the article)
- title
- contentusers
- id
- namecomments
- id
- user_id (the author of the comment)
- article_id
- content
Last but not least, I’d like the users to be able to vote for articles as well. Since each user can vote for multiple articles and each article can be voted on by multiple users, we need to add in another junction table:
articles
- id
- user_id (the author of the article)
- title
- contentusers
- id
- namecomments
- id
- user_id (the author of the comment)
- article_id
- contentlikes
- id
- user_id
- article_id
In a real-world scenario, you might want to use the existing
directus_userstable instead of rolling your own. But for simplicities sake in this example, I’m going to use my own.
Let’s create these tables and basic columns in Directus first. Note that I’m not adding in the relational columns and interfaces just yet.
articles
- id (Default)
- title text_input
- content wysiwyg_fullusers
- id (Default)
- name text_inputcomments
- id (Default)
- content wysiwyg_fulllikes (hidden)
- id (Default)
- user_id numeric
- article_id numeric
Since we’re not going to be modifying the likes junction table directly, I chose to hide it from sight in the admin panel.
Hiding a table can be done by toggling the
Hide Tableoption on the table’s setting page. Hiding an interface can be done by toggling the visible-icon (the eye) before the column name on the same settings page.
In this example, there are a couple relationships which I’d like to manage:
- Each article is written by a single user (many-to-one);
- Each comment is written by a single user (many-to-one);
- Each article has multiple comments (one-to-many);
- Each user can like multiple articles, and each article can be liked by multiple users (many-to-many).
Many-to-One (m2o)
Many-to-one relationships are the easiest to setup. To add a m2o relationship, you simply add a new column using the many-to-one interface.
You can name this column whatever you like.
It is common in relational database design to name this field the name of the related table followed by the name of the primary key column, f.e.: articles_id. However, I’ve decided to go with author. Fight me.
There are two required options you need to set for this interface to function properly:
Visible Column: The name of the column you’d like to fetch for use in the interface. In this case: name
Visible Column Template: A Twig template string which controls how the visible columns are actually shown in the interface. In this case, I’ll go with {{name}}, which just prints the name. You could also go for something more elaborate like {{name}} — {{age}} ({{id}}) as long as you remember to include these columns in the Visible Column option.
If we create a new article now, we see (next to the title and content interfaces) a third ‘Author’ interface. This interface is a dropdown which allows us to pick from the different authors in the users table:
We do the exact same process again for the comment-author relationship.
One-to-Many (o2m)
One-to-many relationships are a little harder to set up, as the table you add the interface to doesn’t actually save any values itself. For example, in our article-comments relationship, we want to select the comments from the article edit page. However, the article record doesn’t save any data about comments itself. It’s the comments which have a column that saves the article’s primary key value. Managing this by hand is a lot of work. Luckily, Directus has a one-to-many interface as well, which makes managing this relationship a breeze.
To set it up, we first need to add a column to the related-table (comments in this case) which will store the PK of the primary table (articles). I’ve called this column article_id and set it to be hidden from the comment edit view.
Next up, we add a one-to-many interface column to the articles table. I’ve called this field comments, as that makes the most sense in my mind. Since this interface doesn’t actually save any data to this table, it doesn’t really matter from a database-design perspective. However, this field will be returned from the API, so choose a name that fits your project best.
In the relationship setup field, make sure to pick the column we just created to store the primary key in:
The one-to-many relationship also requires you to set the visible columns, just like the many-to-one one.
Once you’ve set this up, the interface (with the default settings at least) will render a list of all the comments which are posted to this article. It also allows you to choose existing comments to link to this article, or add a completely new comment right from within this article!
Many-to-Many
The many-to-many relationship sounds like the most difficult to setup, since we have to keep track of the values of not one, but two other tables. However, since we’ve already setup the likes junction table, adding the interface is quite easy.
I’d like to be able to select articles which the user has voted on from the user edit view. To achieve that, we add a many-to-many interface to the user table. Again, just like the one-to-many relationship, this interface doesn’t actually save any values to the users table, as it stores everything in the likes junction table. For this reason we refer to this column as an “alias”—since it’s not actually a column in the database.
Again, just like the one-to-many relationship, we configure the actual saved values in the relationship settings panel:
Also, ‘again, just like the one-to-many relationship’, we need to set the Visible Columns and provide a template to render them. I’m going with id,title and ({{id}}) — {{title}} respectively.
When you’ve set all this up, it looks and acts just like a one-to-many interface. Many-to-many allows you to create new related items or choose existing items… which is pretty magical since it’s working with data from three different tables at once!
What “It’s complicated..” relationships are you going to start? We’d love to know in the comments below!
Your friends at Directus
🐰
