Doctrine: How to define relationships

By emiviada in Doctrine, PHP 0 Comment

In this post we will review how to define Doctrine relationships, from one-to-one to many-to-many relationships.

One-to-One

To explain this relationship we do it with an example. We suppose that we have two entities (User and Email); when we define the Email entity we should add a ‘user_id‘ property (which is the foreign key) due to the User entity is the “owner” of the relationship, this means the User has an Email. We always need to identify the “owner” side and add the foreign key to the entity which belongs to the “owner” entity. When we use this relationship, Doctrine uses a hasOne method. Let’s see how to define it with yaml:

1
2
3
4
5
6
7
8
9
Email:
  columns:
    user_id: integer
    address: string(150)
  relations:
    User:
      local: user_id
      foreign: id
      foreignType: one

One-to-Many and Many-to-One

Now, let’s suppose we have the same User entity which can have many phone numbers (for that we have a Phonenumber entity). We should follow the same convention than in the one-to-one relationship and define the foreign key in the Phonenumber entity, which belongs to the User entity. When we define this kind of relationship Doctrine uses the hasOne method for the Phonenumber entity and the hasMany method for the User entity. Let’s see how to define it with yaml:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
User:
  // Some properties definitions here
  relations:
    Phonenumbers:
      type: many
      class: Phonenumber
      local: id
      foreign: user_id

Phonenumber:
  columns:
    user_id: integer
    phonenumber: string(50)
  relations:
    User:
      local: user_id
      foreign: id

Many-to-Many

And last but not least, when we need to define a many-to-many we have the need to add an extra table :-S, called JOIN table. Following with our User entity, let’s suppose we have a new entity called Group, and we know that a User can belong to a many Groups and that a Group can have a lot of Users. In this case, if we remove an User we should not remove the Group, but yes the relationship. When we define this kind of relationship Doctrine uses the hasMany method in both entities. Let’s see how to define it with yaml:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
User:
  // Some properties definition here
  relations:
    Groups:
      class: Group
      local: user_id
      foreign: group_id
      refClass: UserGroup

Group:
  tableName: groups
  columns:
    name: string(30)
  relations:
    Users:
      class: User
      local: group_id
      foreign: user_id
      refClass: UserGroup

UserGroup:
  columns:
    user_id:
      type: integer
      primary: true
    group_id:
      type: integer
      primary: true

Well, I hope this post will be helpful to understand and know how to define Doctrine relationships.

Ref: Doctrine documentation

 

Symfony and Doctrine behaviors

By emiviada in Doctrine, PHP, Symfony 1.x 0 Comment

This post is about symfony and doctrine behaviors which are useful for daily uses.

These behaviors allow us to speed up the application development and in this post we will find short descriptions about most common behaviors.

Basically, a behavior will give us some relationships, algorithms and other features between our business entities. Those behaviors are deeply configurable (they should be at least), that configurations include: activate/deactivate behavior features, rename some additional schema attributes, etc.
We can use many Doctrine’s behaviors in our projects, such as core behaviors, extension behaviors, symfony’s plugins, or well, we can build our own behaviors.

Core Behaviors

Doctrine already comes with many behaviors (core behaviors), those are:

  • # Versionable – adds a new table to store our diferent versions of the entity to keep our entities versionable.
  • # Timestampable – this probably is the most popular behavior, adds two new columns (created_at and updated_at) to automatically store the dates when the entity is created or updated respectively.
  • # Sluggable – adds a new column (slug) which is unique and could be use by sfDoctrineRoute to refer the entity.
  • # I18n – adds a new table to provide Internationalization (I18n) to our model, this behavior is esential when we develop multi-language apps.
  • # NestedSet – adds a few columns (root_id, lft, rgt and level) to our entity to develop a hierarchy data structure (tree structure).
  • # Searchable – choses the columns of our entity which we want to index and adds a new table, speeding up the search engine development.
  • # Geographical – adds the longitude and latitude columns storing specific geographical coordinates, and provides us a getDistance() method to calculate the distance between 2 geographical entities.
  • # SoftDelete – adds a new deleted_at column which defines if a record has been marked as deleted (and when). It is an useful behavior when we need important consistency data.

Extension Behaviors

Also, we can use the Doctrine exntesions:

  • # Blameable – adds an additional level to audit our entities, it allows us to follow who has created or updated an entity.
  • # EventLoggable – saves a log of every Doctrine’s event (pre/post Delete/Insert/…) used by a record.
  • # Localizable – gives us the functionality to convert measure units (for instance, kilometers to miles).
  • # Locatable – gives us the functionality to use Google Maps to automatically fill our entity with longitude and latitude information from Google.
  • # Sortable – gives us the functionality to sort our entities.
  • # Taggable – adds abilities to tagging, it creates two tables using m:n relationships and provides easy tags administration.

Plugin Behaviors

Finally, there are some symfony’s plugins which gives us more behaviors:

I hope this post is useful.

Symfony: Executing queries after load fixtures

By emiviada in Symfony 1.x 1 Comment

In this post we will see how to use symfony’s events to execute SQL queries after loading fixtures.

Sometimes we have the need to execute some SQL queries after we have loaded our fixtures, for instance, we need to do something specific for our RDBMS (Relational Database Management System) which is not supported by Doctrine nor Propel. Fortunately, symfony 1.4 has an excellent events system which allows us to connect the framework’s core and execute our own code when certain actions take place.

The below code snippet is an example of how we can execute some SQL queries manually after of doctrine:data-load:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<?php
class ProjectConfiguration extends sfProjectConfiguration
{
    public function setup()
    {
        // ...
        $this->dispatcher->connect('command.post_command', array(
            $this, 'listenToCommandPostCommandEvent'
        ));
    }

    public function listenToCommandPostCommandEvent(sfEvent $event)
    {
        $invoker = $event->getSubject();
        if($invoker instanceof sfDoctrineDataLoadTask)
        {
            $conn = Doctrine_Manager::connection();
            $conn->exec(// ...);
        }
    }
}

Symfony 1.4 have a lot of events we can use to customize features. You can learn more about these events in the Symfony’s documentation.

Execute native SQL with Doctrine

By emiviada in Databases, Doctrine 1 Comment

We can execute raw SQL queries with Doctrine ORM.

If you already used this awesome library you already know all the features Doctrine supplies us at the time to interact with our database. The DQL (Doctrine Query Language) language is a very powerful and one of the most main feature of Doctrine, but, sometimes we have to face really advanced queries and with DQL it could be a little tricky to do it (in version 2 it will be improved).

If you need to do advanced and complicated queries (or not), Doctrine gives you the ability to execute native/raw SQL:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<?php

// we build the query
$query = "select id from tabla";

// gets the connection singleton
$con = Doctrine_Manager::getInstance()->connection();

// executes the query
$st = $con->execute($query);

// retrieves the results
$rs = $st->fetchAll();

// Or if you want the associative results
$rs = $st->fetchAssoc($query);

That’s it.

Use utf8 in Symfony with Doctrine

By emiviada in Doctrine 4 Comments

Here we will see how to configure Doctrine to use utf8 in Symfony.

So, we will set the charset and the collation of our database to use utf8, which most of the time is very useful, for instance, when we want to use a database with support to internationalization (i18N).

To do that we have three possibilities to see:

First, we can edit the config/ProjectConfiguration.class.php file:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<?php
class ProjectConfiguration extends sfProjectConfiguration
{
    public function setup()
    {
        $this->enablePlugins('sfDoctrinePlugin');
    }

    public function configureDoctrine(Doctrine_Manager $manager)
    {
        $manager->setCollate('utf8_unicode_ci');
        $manager->setCharset('utf8');
    }
}

The second one is to set the configuration in the config/databases.yml file:

1
2
3
4
5
6
7
8
9
10
11
all:
  doctrine:
    class: sfDoctrineDatabase
    param:
      dsn:      mysql:host=localhost;dbname=myDatabase
      username: myUser
      password: mySecret
      attributes:
        default_table_type:    InnoDB
        default_table_collate: utf8_unicode_ci
        default_table_charset: utf8

And the last one is to set the options in our schema (config/doctrine/schema.yml):

1
2
3
4
options:
  collate: utf8_unicode_ci
  charset: utf8
  type: InnoDB

I hope this is helpful for you.

Doctrine vs Propel

By emiviada in PHP 2 Comments

UPDATE: Here is a great comparison for these ORM’s for the 2.x versions. Thanks to @Pati for sharing the link.

A quick comparison for the most 2 well known and best ORM‘s for PHP.

Here we will try to make a quick comparison about the most known ORM for PHP. To help developers to take the right decision when we need to decide what is the best ORM which could best adapt to our projects.

These 2 more used ORM are as the post title mentioned Doctrine and Propel.

Features

Both ORMs have many similar basic features, they support all the usual operations for CRUD (Create, Retrieve, Update and Delete), from create a new record to update existing ones. Also, both can generate the PHP classes for you, Propel based on XML while Doctrine based on YAML, And another cool feature they have is that both support different database engines (like Mysql, Oracle, MSSQL, etc).

Both support data validation and model relationships. Additionally, they support simple inheritance, although in Doctrine is called concrete inheritance. Doctrine supports 2 types of inheritance: Simple, where all the classes have the same columns, and the Aggregation inheritance, where we store an additional value in the table which allows us the automatic instantiate of the right model type when we make a query.

Well, we saw they almost share the same features so far, but the following are features only Doctrine has.

Behaviors: Doctrine supports many “behaviors” for its models, for instance, a Timestampable model will automatically create two columns: created_at and updated_at, where we can store the creation and update dates.

Searching: Doctrine has a fulltext search engine.

Plus, Doctrine supports data fixtures and migrations, caching, events, pagination, commands line interface and so on, and we can say in advanced features Doctrine has an advantage compared with Propel.

Usability

Documentation

One of the most important thing is documentation of course. Without a good documentation is hard to use any library. Until the last year (2009) the Propel documentation was one of the major problem for them, and it’s true they are improving it there still are work to do. In the other hand, the Doctrine documentation is great and the community is continuously improving it. So, about documentation concern, Doctrine is clearly the winner.

Libraries Use

The first task we have to do with both ORMs is to build the model classes. Doctrine allows us to just write a simple YAML file, or just PHP code if you prefer it. The Propel proposal is to write a XML to define our model classes, and in my personal opinion I prefer to deal with YAML instead of XML, so point for Doctrine :-).

Database operations

The basic operations with CRUD are very similar in both ORMs, however, there is a big different when we need to do more advanced queries.

Propel uses a Criteria/Peer proposal:

1
2
3
4
5
6
<?php
$c = new Criteria();
$c->add(UserPeer::ID, 10);

//SELECT all "User" models which have 10 as their ID and join all foreign tables.
$users = UserPeer::doSelectJoinFoobar($c);

Doctrine proposal is to use Doctrine_Query and a simple customized SQL named DQL (Doctrine Query Languaje):

1
2
3
4
5
6
<?php
$items = Doctrine_Query::create()
    ->from('User u')
    ->leftJoin('u.Foobar')
    ->where('u.id = ?', 10)
    ->execute();

To set values in our model classes these ORMs utilizes different methods: Doctrine uses magic methods, while Propel generates setters and getters. This feature gives to Propel the advantage of autocompletion in most IDEs.

As conclusion I can say that both ORM are great, but I prefer Doctrine. And, of course, you can put the ‘My opinion’ label to this post.

And in this link you can find a comparison that Symfony made between these 2 ORMs.