Creating a schema.yml with m:m

Chapter 2: Creating your schema.yml with m:m

Behind this complex name, you’ll find in reality a post from my blog. Even if it’s interesting to understand 1-1, 1-m, m-1, the m:m are the most difficult to write for a beginner, mainly when you’re already worrying about the forms.

So, many-to-many relationships are a challenge, especially when you do what you shouldn’t do (unless you don’t have a choice), in other words, create your database and generating the file called schema.yml from there. So, play the game, do your schema without copying/pasting.

What’s the project?

It will be a quick explanation: we’ll realize a sort of videogames database, like Neoseeker or Mobygames. No matter what will happen for the user: we’ll define the different user groups after (like differences between approvers and administrators), the connection will pass by a login in the frontend, etc. etc.

We’ll be only do that in English: I’m not at ease with I18N, so, well, the explanation will come later.

The central entity will be the game, defined like that:

  • a name (the English name)
  • a release date (I do know that there are several release dates depending where you’re living on or on which platforms the game will be but here, for easiness, it will be the oldest one existing. Just be aware that it will be one of my challenges for the next weeks: improving that)
  • a publisher (like for the release date, it will be only one)
  • a developer (id.)
  • a background
  • several genres
  • several platforms

Obviously, you’re already smelling the m:m relationship: a genre can regroup several games like a platform is composed of a library of games.

And we also need to define the genre and the platform entities: they’ll have a name, with a acronym for the platform (ex: Playstation 3 = PS3).

So, these are our future columns for our tables, excluding the ids. Doctrine is creating by itself a column id if it’s not indicated in the schema. You can write them even if it’s just for that:

id:
  type: int(11)
  autoincrement: true

Pay attention to your keys when you’re declaring them: Doctrine is generating the primary key id as a bigint(20), so, keep that syntax when you have foreign keys in the « middle » table like I love to say (the table where the link between two tables in m:m is stored). Don’t declare them as int(4) else you’ll get a error message, even if it’s not disturbing your application. But with some commands, you’re taking the risk to do all step by step.

When we’re using MySQL Workbench, here is the database model:

Modèle de la base de données

Génération the schema.yml

I need to remind you that YAML is working with spaces in front of each element, done with the SPACE bar and not with the TAB one. If you write like that, you’re doomed:

id:
type: int(11)
primary: yes

You’ll find the file in config/doctrine.

Now, we can begin to build it by doing the table Game, as it’s the central entity:

Game:
  columns:
    name_game:
      type: string(255)
      notnull: true
    developer:
      type: string(100)
      default: Unknown
    publisher:
      type: string(100)
      default: Unknown
    background:
      type: string()
    release_date:
      type: date(25)

You don’t have to precise the relationship between it and another table if it’s defined in the latter. In other words, Doctrine can make the link without having the two tables with the relations part.

Notice that I’m giving a default value to Publisher and Developer and that my background will be rendered by the framework as a textarea.

But I’m not putting created_at or updated_at like in Jobeet, as we’re not needing them for now. Else, the syntax would be:

Game:
    actAs: { Timestampable: ~ }
   colums:
     ....

Now we can create our table Genre:

Genre:
  columns:
    name_genre:
      type: string(50)
      notnull: true

Seen like that, it’s still easy. But here comes the part where you’re defining the relationship:

Genre:
  columns:
    name_genre:
      type: string(50)
      notnull: true
  relations:
    Games:
      foreignAlias: Genres
      class: Game
      refClass: GameGenre
      local: genre_id
      foreign: game_id

So, what’s important?

First, I’m giving the name of the relation (related to the other table in this case): Games. After, I give an optional alias for the relationship (related here to the current table). Class is representing the name of the table (or the class if you prefer) which is tied to Genre (Game).

But as we already know, m:m relationships are better with a « middle » table where the links are stored (just do it in MySQL Workbench when defining relationships and you’ll that table being generated). So, we need to specify it in refClass. Now, Doctrine will know that if something is changing or in Game or in Genre, it will reflected in GameGenre (but we need to give a parameter in GameGenre).

Local is refering to the current object (here genre) but with the name of the id in GameGenre, so that he knows that id in Genre equals to genre_id in GameGenre. Foreign is doing the same except that it concerns the other table.

Now, it’s time to define the linking table. If it’s not in your schema, Doctrine will not know where to store the data related to that.

GameGenre:
  columns:
    game_id:
      type: bigint(20)
      primary: true
    genre_id:
      type: bigint(20)
      primary: true
  relations:
    Genre:
      onDelete: cascade
      local: genre_id
      foreign: id
      foreignAlias: GameGenres
    Game:
      onDelete: cascade
      local: game_id
      foreign: id
      foreignAlias: GameGenres

It’s based on the same explanation I gave above. There is three differences:

  • onDelete: cascade is a parameter allowing to delete the rows concerning a genre or a game being also removed.
  • The name of the tables is the relation name.
  • Local is still representing the primary key from the selected table but Foreign takes here the name of the primary key in the table being concerned about it. It’s here that Doctrine will associated the ids when you’re giving a specific name.

Once you did that and you understood that, you can do Platform without problem:

Game:
  columns:
    name_game:
      type: string(255)
      notnull: true
    developer:
      type: string(100)
      default: Inconnu
    publisher:
      type: string(100)
      default: Inconnu
    background:
      type: string()
    release_date:
      type: date(25)

Genre:
  columns:
    name_genre:
      type: string(50)
      notnull: true
  relations:
    Games:
      foreignAlias: Genres
      class: Game
      refClass: GameGenre
      local: genre_id
      foreign: game_id

GameGenre:
  columns:
    game_id:
      type: bigint(20)
      primary: true
    genre_id:
      type: bigint(20)
      primary: true
  relations:
    Genre:
      onDelete: cascade
      local: genre_id
      foreign: id
      foreignAlias: GameGenres
    Game:
      onDelete: cascade
      local: game_id
      foreign: id
      foreignAlias: GameGenres

Platform:
  columns:
    name_platform:
      type: string(50)
      notnull: true
    acronyme:
      type: string(10)
      notnull: true
  relations:
    Games:
      foreignAlias: Platforms
      class: Game
      refClass: GamePlatform
      local: platform_id
      foreign: game_id

GamePlatform:
  columns:
    game_id:
      type: bigint(20)
      primary: true
    platform_id:
      type: bigint(20)
      primary: true
  relations:
    Platform:
      onDelete: cascade
      local: platform_id
      foreign: id
      foreignAlias: GamePlatforms
    Game:
      onDelete: cascade
      local: game_id
      foreign: id
      foreignAlias: GamePlatforms

With that schema, you can generate files and forms that will work and that will insert the data where it should be without you to make your own queries or your own code.

If you want more:
Doctrine 1.2
Saving m:m relationships

  1. Poster un commentaire

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s

%d blogueurs aiment cette page :