Made withOctober CMSTutorial

Making Websites With October CMS - Part 10 - Relations

W

Watch and Learn

Creator on the October CMS channel

Watch on YouTube ↗

42K viewsAug 12, 201617:25 runtime

Learn to implement many-to-many relationships in October CMS by building a practical movie genre system. This guide walks you through creating a 'Genre' model, setting up backend forms and lists using the Builder plugin, and establishing the connection to your 'Movie' model…

Chapters

#octobercms#tutorial#cmscrm

Implementing Many-to-Many Relationships

This guide shows how to create a many-to-many relationship in October CMS using the example of connecting Movies to Genres.

1. Create the 'Genre' Model

Using the Builder plugin, a new model for genres is created with its own database table.

Database Table: movies_genres
Columns:
- id (Primary Key, Auto-incrementing Integer)
- genre_title (String)
- slug (String, Indexed)

Tip
Use the slug preset in the Builder's form configuration to automatically generate a URL-friendly slug from the genre_title field.

Backend forms and lists are then configured for the Genre model to allow for easy management in the admin panel.

2. Create the Pivot Table

A pivot table acts as an intermediary, linking records from two other tables. It only contains foreign keys.

Table Name: movies_genres (A common convention is to combine the two table names alphabetically).
Columns:
- movie_id (Integer)
- genre_id (Integer)

Important
A pivot table does not have its own id column or a corresponding Model class. It is used exclusively by the relationship definition.

3. Define the `belongsToMany` Relationship

The connection is defined in the Movie model's PHP file (plugins/author/plugin/models/Movie.php).

// In the Movie model class
public $belongsToMany = [
    'genres' => [
        'Watchlearn\Movies\Models\Genre', // The related model class
        'table' => 'watchlearn_movies_movies_genres', // The pivot table name
        'order' => 'genre_title' // Optional: order for the related items
    ]
];

4. Configure the Backend Form

Add a relation widget to the Movie model's form (fields.yaml) to create a user interface for selecting genres.

Field Name: genres (must match the key in the $belongsToMany array)
Label: Genres
Type: relation
Name From: genre_title (The column from the related table to display in the list)

5. Display Related Data on the Frontend

In the frontend Twig template, you can loop through the relationship to display the data. The related objects are available as a collection on the primary model object.

<h3>Genres</h3>
<ul>
    {% for genre in movie.genres %}
        <li>{{ genre.genre_title }}</li>
    {% endfor %}
</ul>

This demonstrates the complete workflow for setting up, managing, and displaying a many-to-many relationship in October CMS.

Introduction to Many-to-Many Relations

In the previous episode, we had a little taste of relations in October CMS. So we attached some images to our movie model. In this episode, we're going to tackle that a little bit further and create movie genres. So you can look at movie genres like something like categories or tags. Just for movies, it makes more sense to create something called genres, but it's basically the same thing. So it's a categorization of a movie.

So in this episode, we are going to tackle that. We are going to tackle the many-to-many relationship in October CMS and how you can make those. You will see it is not very straightforward like you're maybe used to with WordPress or with Drupal or something like that, but it gives you much more control over what you're doing.

Creating the Genre Model and Database Table

So first of all, let's create our average genres model. So we are going to go to Builder and then choose Movies. And in Movies, we are going to add another database, and that database is of course going to have an ID. It's going to be integer, it's going to be auto-incremented, and it's also our primary key for this model.

So we add another column, and that column is going to be genre_title. It is going to be a string, and that's about it. Let's add another column. This one is going to be called slug. So don't forget to make slugs at the beginning so that you don't have to... it is also going to be a string... so that you don't have to do the same thing like we did for the movies because we didn't add the slug right away. So you will have to write another title. Okay, so that's it. This is our database for genres, and we're going to call that table movies_genres. Save it, save and apply.

Building Backend Forms and Lists for Genres

Okay. Now we have to create a model for it. So we click on Models and add a new model right here. So as you can see, this is our movie, and we add a new model, and we're going to call it Genre, and the table is going to be movies_genres. Okay.

Now we have, just like for the movies, we have forms and lists. And now I'm going to add another form, and that form is going to have a title. So the label is going to be Title, and the field name for that is genre_title. And I'm going to have it left-aligned, and I think that's it.

Next thing we're going to do, we're going to create another field for our slug. It's going to be right-aligned. And like we did for the slug for the movie, we go to Advanced, preset, choose the field to be genre_title, and the type is going to be slug. Save this. Okay, now we go to our lists, add another list, so that we have a list of our genres in our backend. So the first field is going to be, of course, Title, type is text. And another column... actually, we don't even need another column. So we are just going to sort it by title. Let it be searchable and sortable. Save this.

And now we can go to Backend Menu, and we are going to add a sub item right here. And that item is going to have this tag icon. The label is going to be Genres. And I think that's it. We're going to add a URL later. Save this.

Now we go to our controllers, and we add a new controller for our genre. It's going to use the Genre model, and the active menu is going to be Genres. We are going to edit List Control Behavior, Controller, and Reorder and click okay. And I think that should be it. Save this. I'm going to refresh this page. Okay, and now I'm going to go to Backend menu, click on Genres, and try to add a URL here. As you can see it autocompletes it and says watchlearn/movies/genres. Click on this, save it.

And now when we go to our Movies, now we have this Genres right here. And if we create a genre, actually, we can now create genres. So I can add something like Action. Okay, now we have these three genres.

Understanding and Creating a Pivot Table

But the problem right now is if we go to movies and let's say we go to Inception, we don't have a way to display those genres right here. So to do that, we are going to have to create something called a pivot table. So let's say the pivot table is a middle ground between our movies table and our genres table. So in the pivot table, it might say something like, let's say Inception is a movie with the ID of one and it's an action movie. So the action movie... action genre is also has an ID of one. So in that table, we would just have those two IDs. So the movie one, and the ID of the movie would be one, and the ID of the genre would also be one, or maybe three if it's comedy, or maybe two if it's drama, or whatever.

So the pivot table serves as a link between those two models, and we are going to create that table right now. And later on, I'm going to show you how it looks in the actual table once we get our relations and once we get our links between those two models going, so that may be more clear to you. Also, if you want to know more about relationships in October CMS, they are pretty much the same as you would get with the Laravel framework. You have this whole relationship page in the documentation right here, and you can check out everything you want to know about them right here.

So in the Builder, we just add a new table. We're going to call that table movies_genres. Okay, and we add just two columns to it. As you will see, we don't add an ID column, just two columns. So the first column is going to be called movie_id. It's going to be an integer and the primary key. The second column is going to be genre_id, integer and primary key. And that's it. We save it. Now you created your pivot table. As you will see, we don't need a model or controller or anything for that table. That table just serves as a link between movies and genres.

Next thing we need to do, we're going to go to our models. We are going to go to Movies, Forms, and now we are going to add another form field, and it's going to be a widget of type Relation. So we click on it, and the field name is going to be genres. The label is going to be Genres also. And since we want to display the titles of our genres, we will just set the name column in the Relation sub-tab to be genre_title. And that should be it. We just save this.

And now if we go to Movies, and we go to Inception, you could see pretty much the same thing as, pretty much the same error like we had in the previous episode when it said that our images were undefined. So the Movie model doesn't contain a definition for genres.

Defining the `belongsToMany` Relationship in the Model

So we have to go to our code editor, to Movies, Movie... actually plugins/watchlearn/movies/models/Movie.php, and we had these relations right here. So these are the ones for the images, attachOne and attachMany. And now, since we are using a many-to-many relationship—so a movie can have many categories and movies can belong to many categories—so we have a many-to-many relationship right here. So we are going to have to use something similar to this.

So what I'm going to do now is I'm going to set that relation. So a movie can belong to many genres. A movie can be action, comedy, or sci-fi, drama, or whatever. So now we have to... we said movie belongs to many genres. And now we have to set those information so that October knows what we are talking about.

First of all, we have to set the model that this movie is connected to, so the Genre model, and the Genre model is Watchlearn\Movies\Models\Genre. Next, we need to set the table that the October CMS can look up for that link, so our pivot table. So the name of the pivot table is in our Builder. We can just copy this. And we have to set an order that these genres are going to be displayed by. So I'm going to set it to be by genre_title. Of course, I forgot to set the table right here. So table and then order. We save this, and now if we go to Movies... something went wrong. Okay, I forgot to put a semicolon right here. Okay, now if you go to Inception, you now have these genres right here. So Action, Comedy, and Drama. Let's say it's Action and Drama and save this.

So now I'm going to go to our movie-single.htm and display those genres. Okay, so we are now on our Inception movie. And what we're going to do, we are going to create a title: Genres. And if we do something like record.genres, we will see pretty much the same thing as we did with our images. So as you can see, it says ID 1, genre_title action, slug action, and then you got this pivot.

So this is our pivot table. So what you need to do is do pretty much the same thing as you would do for the images. So we would have to create a for each loop to get our genres, so the title of our genres. So we set the variable to be genre in record.genres. And now we just... we have the access to that variable, and we can display its title. Put a <br> tag at the end. Save it, and as you can see, we get Action and Drama. Okay, so this is the way you would display that.

Exploring the Pivot Table Data in the Database

Next thing, I just want to show you that pivot table in our actual database. So if you take a look at our database, you can see that we have this watchlearn_movies_movies_genres. So this is our pivot table. So if we go to browse the data and choose that table right here, you can see that we have this movie_id and a genre_id. So the movie ID is 1, it has a genre of 3, so is it the genre ID of 3? I think Drama. And the genre ID of 1 is Action.

So if we go to our Movies and go to Fight Club and set it to be Action and Comedy, for example, save it, go to our database and refresh it, you can see that now we have the movie ID of 2, which is Fight Club, and it has a genre ID of 2 also. So this is the basic principle of how the pivot table works. It just connects the movie with the genre that that movie is using. Okay.

Conclusion and Next Steps

So this is it for this video. In the next video, we are going to create a reverse many-to-many relationship for our genres. So when someone clicks on a genre, for example, let's say Action, it's going to take them to a page where it will display all the movies that are in that genre. So for example, if you go to genre Action, it will display Inception or it will display Fight Club if that's an action movie. Okay.

Please remember, everything we did here will be available for you on GitHub, and the link will be in the description below. If you like this video, please like it. If you like the channel or the content I put out, please subscribe to the channel. Also, you can ask me questions on Facebook or on Twitter or in the comments below or on my site. Thank you guys for watching, and I will see you in the next video.