TDBM 4

The PHP ORM that understands your data model

Created by TheCodingMachine / @david_negrier

What is an ORM?

An ORM is a library that maps your database records to your objects.

There are actually two kinds of ORMs

In some libraries, everything starts with the database model. This is sometimes called "ActiveRecord ORMs". TDBM is part of this category. It's maybe the most advanced ORM when it comes to understanding your data model and translating it to actual PHP classes.

There are actually two kinds of ORMs

In other libraries, everything starts with the PHP classes. Doctrine ORM is (somewhat) part of these tools.

TDBM philosophy

Your database model is meaningful. TDBM will do its best to understand it and to generate the object model that maps your database model as closely as possible.

A bit of vocabulary

• Bean: A bean is a PHP class that maps a table row. There is ~ one class per table. For instance, the UserBean class will map the users table. Aka entities in Doctrine ORM.
• DAO: A DAO is a PHP service class that contains method to access beans. There is ~ one DAO per table. For instance, the UserDao contains methods to access the users table. Aka repositories in Doctrine ORM.

A bit of vocabulary

• TDBMService: The main class of TDBM. It contains all the logic. In TDBM, you never use the TDBMService directly. Instead, you use DAOs.
• DAOFactory: A simple utility class that refers all DAOs. Use with care.

A bit of vocabulary

• DBALConnection: The TDBMService uses a Doctrine DBAL (Doctrine Database Abstraction Layer) to connect to the database. It makes TDBM available on all platforms supported by Doctrine (i.e. almost all databases out there).

Generating DAOs and Beans

Generating DAOs and Beans

Alternatively, if you are not using Mouf, you can use the TDBMService::generateAllDaosAndBeans() method.

Generating DAOs and Beans

In TDBM 4.1, I plan to add a Symfony 2 console command too, and more integration with other frameworks.

Getting started!

Creating a bean

$john = new PersonBean('John Doe', new DateTime());

// Write values:
$john->setName('John Wayne');

// Read values:
$name = $john->getName();

$personDao->save($john);

You use the constructor to build your bean. All not nullable columns are parameters in the constructor.

TDBM will generate for you getters and setters.

SQL date columns are converted to PHP DateTime objects.

SQL enum columns are not supported yet (coming in TDBM 4.1).

Getting a bean by ID

// Let's get the bean
$person = $personDao->getById(1);

// Let's display the name
echo $person->getName();

Supports multi-column primary keys (pass an array of column=>values).

Getting all beans of a table

// Let's get the list of users
$userList = $userDao->findAll();

// Let's display the names
foreach ($userList as $userBean) {
    /* @var $userBean UserBean */
    echo $userBean->getName()."\n";
}

Filtering beans

class CountryDao extends CountryBaseDao
{
    /**
     * Returns the list of countries starting with $firstLetter
     *
     * @param string $firstLetter
     * @return CountryBean[]
     */
    public function getCountriesByLetter($firstLetter) {
        return $this->find(
            "label LIKE :label",
            [ "label" => $firstLetter.'%' ]);
    }
}

The CountryDao class can safely be edited. Never edit the CountryBaseDao as it is regenerated by TDBM on every DB model change.

Filtering beans

Important! The find method is protected. You should only call it from within DAOs. This way, SQL code is restricted into the DAO and does not leak into the outside world.

Filtering beans

VERY IMPORTANT! Never ever ever concatenate user input in the SQL string. Use parameters instead.

Understood?

NEVER CONCATENATE SQL STRINGS!

// BAD
$list = $this->find("name LIKE '".$firstLetter.'%"' );

// GOOD
$list = $this->find("label LIKE :label",
    [ "label" => $firstLetter.'%' ]);

Magic parameters

class ProductDao extends ProductBaseDao {

    public function getProductsByCategoryAndStore(
            CategoryBean $category = null,
            StoreBean $store = null) {

        return $this->find(
            "category_id = :category AND store_id = :store",
            [
                "category" => $category?$category->getId():null,
                "store" => $store?$store->getId():null
            ]);
    }
}

If the $category or $store is null, the category_id or store_id field will be completely discarded from the SQL.

Magic parameters

If a parameter is null, the filter field is rewritten to remove the part of the query that contains the null value.

"category_id = :category AND store_id = :store",
[
    "category" => 42,
    "store" => null
]

will result in the SQL filter

category_id = 42

See how the store_id has disappeared?

Getting only one record

If you are confident that your query will only ever return one record, then, you can use the findOne method instead of find.

class UserDao extends UserBaseDao {

    /**
     * Returns a user by login
     *
     * @param string $login
     * @return UserBean|null
     */
    public function getUserByLogin($login) {
        return $this->findOne("login = :login", [ "login" => $login ]);
    }
}

Indexes are your friend

TDBM detects indexes and generates DAO methods for you.

Here is a sample:

CREATE INDEX users_status_idx ON users (status);

The users table has an index on the status column.

Automatically, TDBM will generate a findByStatus method in the UserBaseDao class:

$users = $userDao->findByStatus('on');

Unique indexes are your friend

But wait, there is more! What about unique indexes?

CREATE UNIQUE INDEX users_login_idx ON users (login);

TDBM will generate a findOneByLogin method:

$user = $userDao->findOneByLogin('alice');

Please note how a unique index generates a findOneBy... method instead of a findBy... method.

Multi columns indexes are your friend

Finally, TDBM can also deal with multi-columns indexes, or indexes on foreign keys:

CREATE INDEX users_status_country_idx ON users (status, country_id);

This index on both the status and the country_id column will be turned into a findByLoginAndCountry method:

$country = $countryDao->getById(1);
$user = $userDao->findByLoginAndCountry('on', $country);

Notice how the parameter passed for the for foreign key is a bean and not an ID.

Navigating the object model

Many to one relationships

So what if I want to get the name of the country in which the first user is located?

// Let's get the user bean
$userBean = $userDao->getById(1);

// Let's get the country bean
$countryBean = $userBean->getCountry();

// Let's display the country name
echo $countryBean->getName();

Many to one relationships

Of course, there is also a setter:

$userBean->setCountry($countryBean);

Notice how you set an object rather than an ID.

One to many relationships

Ok. What, now, if I want to find a list of users from a particular country?

// Let's get the country bean
$countryBean = $countryDao->getById(1);

// Let's get the users from that country
$userBeans = $countryBean->getUsers();

Many to many relationships

TDBM can automatically detect pivot tables in your data model.

// Getter
$rolesBean = $userBean->getRoles();

// Adder
$userBean->addRole($roleBean);

// Remover
$userBean->removeRole($roleBean);

// Check existence
$hasRole = $userBean->hasRole($roleBean);

Many to many relationships

Pivot tables have no DAO and no Beans associated.

Many to many relationships are symmetrical. Therefore, you will find the same methods in the RoleBean class:

// Getter
$usersBean = $roleBean->getUsers();

// Adder
$userBean->addUser($roleBean);

// Remover
$userBean->removeUser($roleBean);

// Check existence
$hasUser = $roleBean->hasUser($roleBean);

Many to many relationships

Unlike in Doctrine, TDBM does not need to have a notion of owning and inverse side of a many to many relationship.

$roleBean = new RoleBean("Admin");
$rightBean = new RightBean("CAN_ACCESS_EVERYTHING");

$roleBean->addRight($rightBean);

foreach ($rightBean->getRoles() as $role) {
    echo $role->getName()."\n";
}
// Will display "Admin".

Works in TDBM only! Do not try the code above with a Doctrine entity... :)

Joins ans filters

Simple joins

TDBM writes joins for you!

class UserDao extends UserBaseDao {
    public function getUsersByCountryName($countryName) {
        // Behold the magic!
        return $this->find(
            "country.name LIKE :country",
            [ 'country' => $countryName.'%' ] );
    }
}

Here, we called the find method passing a filter on the name column of the country table.

Magic provided by MagicQuery. Works with any table of your data model (not only adjacent tables).

Filtering by ID/bean

Most of the time, of course, you will not pass the name of the country but the ID of the country. Actually, using TDBM you can just pass the object. Have a look!

class UserDao extends UserBaseDao {
    public function getUsersByCountry(CountryBean $countryBean) {
        return $this->find($countryBean);
    }
}

Complex joins

So now, what if I want to find what rights the user "Robert Marley" has?

class RoleDao extends RoleBaseDao {
    public function getRolesForUser(UserBean $user) {
        // Shazam!
        return $this->find($user);
    }
}

TDBM finds the shortest path between 2 tables and performs the join.

Simplified filter syntax

Syntactic sugar: if your filter is only made of "=" and "AND" statements, you can use the shortcut "array" syntax in your queries. Here, we filter a products table by category_id and status:

class ProductDao extends ProductBaseDao {
    public function getByCategoryAndStatus(
                int $category_id, int $status)
    {
        return $this->find([
            'category_id' => $category_id,
            'status' => $status,
        ]);
    }
}

Ordering

You can get your results in a specific order using the third parameter of the find method:

class UserDao extends UserBaseDao {
    public function getUsersByAlphabeticalOrder() {
        return $this->find(null, [], 'name ASC');
    }
}

About result sets

Paginating data with limits and offsets

The result of a find or findAll method is an iteratable object. This object can be paginated (it implements the Porpaginas interface).

If you want to limit the number of records returned, or start at a given offset, you can use the take method.

$users = $userDao->findAll();

$page = $users->take(0, 10);

// Iterate only the first 10 records
foreach ($page as $user) {
    // Do stuff...
}

Paginating data with limits and offsets

There are a number of very useful methods on result sets and pages:

$users = $userDao->findAll();
echo "Total count: ".$users->count();

$page = $users->take(0, 10);

echo "Page count: ".$page->count();
echo "Total count: ".$page->totalCount();

// Cast to a simple array
$pageAsArray = $page->toArray();

Utility methods on result sets

You can get the first item of a result set with the first method:

$users = $userDao->findAll();

$firstUser = $users->first();

Utility methods on result sets

You can directly apply a function to all elements of a result set:

$users = $userDao->findAll();

$userNames = $users->map(function(UserBean $user) {
    return $user->getName();
});
// $userNames is an array of names!

Array versus cursor result sets

By default, result sets can be accessed as arrays:

$users = $userDao->findAll();

echo $users[0]->getName;

For very large datasets, this would fill memory. Instead, use the cursor mode (fifth parameter of find method):

class UserDao extends UserBaseDao {
    public function getUsersInCursorMode()
    {
        return $this->find(null, [], null, [],
            TDBMService::MODE_CURSOR);
    }
}

Fetching multiple tables in one query

Sometimes, you want all users and you know you will need to fetch all countries associated. In SQL, this can be done in one query.

$users = $userDao->findAll();

foreach ($users as $user) {
    echo $user->getCountry()->getName();
}

By default, each time you call the getCountry() method, TDBM will perform a query in database if the country has not been fetched yet. It can be costly.

Fetching multiple tables in one query

Good news! You can ask TDBM to fetch the countries table while fetching the users (in the same query). This is the 4th parameter of the find method:

class UserDao extends UserBaseDao {
    public function getUsersOptimized()
    {
        return $this->find(null, [], null, [ 'country' ]);
    }
}

Inheritance

Inheritance

In a database, inheritance can be represented this way:

TDBM will generate an inheritance hierarchy between the beans of these tables:

UserBean > UserBaseBean > ContactBean > ContactBaseBean > PersonBean > PersonBaseBean > AbstractTDBMObject

Inheritance

This means you can do things like:

// The email column belongs to the contact table
// not the user.
// But this is ok!
$userBean->setEmail('foo@bar.com');

Also, you only save the bean once and all three tables are changed.

JSON serialization

JSON serialization

Serialization works this way:

• Simple columns => simple copy.
• Date columns => cast to ISO 8601 format
• If a column is a foreign key, it is not serialized in the JSON object (you will not see the ID of the foreign key in the object). Instead, the whole object pointed at is embedded in the JSON serialization. This is however not recursive (inner objects of the embedded object are not embedded).
• Finally, many to many relationships are embedded too.

JSON serialization sample

{
    "id": 4,
    "name": "Bill Shakespeare",
    "createdAt": "2015-10-24T13:57:13+00:00",
    "email": "bill@shakespeare.com",
    "country": {
        "id": "2",
        "label": "UK"
    },
    "roles": [
        {
            "id": 2,
            "name": "Writers"
        }
    ]
}

Questions?