Hello techs,

I hope you are doing good 🙂  Recently, I have been working on a Symfony 2 project and in Symfony 2, the data layer is managed by using Doctrine ORM. So today, I planned to post an article on how to correctly map an existing data table using Doctrine 2.x object relationship mapping (ORM) techniques.  Later, (in next post) I will be showing how to map a relationship such as one-to-one, one-to-many, many-to-one, unidirectional and bidirectional using the same Doctrine ORM techniques.

Before continuing the Doctrine mapping procedure, we need a pre built table. This table can be any database format. For the sake of easiness I will be using a very simple MySQL data table.  The name of the table is ‘user’ and table looks like this,

So now, we have a basic table to begin the mapping process. When mapping you have to understand how Doctrine works in terms of it’s own data type syntaxes, mapping file type (PHP, YAML) and so on. For an instance, data type ‘int’ will be mapped as ‘integer’ and ‘char’ will be mapped as ‘string’ in Doctrine 2.  You can get more information on these things if you follow this link or I have extracted the important bit from the url for you 🙂

• string: Type that maps an SQL VARCHAR to a PHP string.
• integer: Type that maps an SQL INT to a PHP integer.
• smallint: Type that maps a database SMALLINT to a PHP integer.
• bigint: Type that maps a database BIGINT to a PHP string.
• boolean: Type that maps an SQL boolean to a PHP boolean.
• decimal: Type that maps an SQL DECIMAL to a PHP double.
• date: Type that maps an SQL DATETIME to a PHP DateTime object.
• time: Type that maps an SQL TIME to a PHP DateTime object.
• datetime: Type that maps an SQL DATETIME/TIMESTAMP to a PHP DateTime object.
• text: Type that maps an SQL CLOB to a PHP string.
• object: Type that maps a SQL CLOB to a PHP object using serialize() and unserialize()
• array: Type that maps a SQL CLOB to a PHP object using serialize() and unserialize()
• float: Type that maps a SQL Float (Double Precision) to a PHP double. IMPORTANT: Works only with locale settings that use decimal points as separator.

(Above information is extracted from :- http://docs.doctrine-project.org/en/2.0.x/reference/basic-mapping.html)

The relevant Doctrine 2 mapping file in YAML format for the above MySQL table will be as follows. (Hint for Symfony 2 readers, make sure you name the file as ‘user.orm.yml’ in your Symfony 2 project. Normally Doctrine files are saved under ‘your_symfony2_project/src/Test/UserBundle/Resources/config/doctrine’ location)

Key things to remember in here,

• Be sure to follow this order. Table declaration part at very beginning. Then the field (column) declaration. In here Primary key (more explanation to follow) should needs to be the first and then the rest of the fields. Finally, the relationship declaration, which is my next post.
• Make sure you keep alignments intact. Putting a wrong alignment will definitely cause problems in YAML files. 
• Space before and after the colon (:), such as ‘attribute<space>:<space>value’ .
• Primary key field is at the top of the field list. So make sure you always start with the primary key. In the above example you will see that ‘id’ has started in the top. (For the ones who are confused by seeing two ‘id’ names in the above example, first ‘id’ is the Doctrine 2 way of saying that this is the primary key and the second ‘id’ is the name of your column)
• For fields which are NOT NULL, you don’t want to specifically mention in Doctrine 2. Because default is NOT NULL in Doctrine 2. However if you want to have any field NULL, as in the ‘last_name’ column in the above table, mention it as ‘nullable : true’ . This is same for the ‘UNIQUE’ keys too. Just put ‘unique : true’ only for the columns which are unique.
• Field lengths are optional. If you didn’t mention length when it maps, let’s say to a MySQL data table, the default value will be taken automatically. Such as length 11 for INT’s (integer in Doctrine 2)
• Make sure you map data types correctly, as Doctrine 2 syntaxes are different from MySQL or any other database language and it’s case-sensitive. Refer to this link :- http://docs.doctrine-project.org/en/2.0.x/reference/basic-mapping.html
• Relationships comes after declaring all columns.

I hope you enjoyed this post. You can download two files (MySQL and Doctrine) by clicking on this link. Please feel free to post your comment, suggestion, basically anything in the comments section below. Cheers!! 🙂