root/doc/tags/RELEASE_1_1_0_BETA4/book/08-Inside-the-Model-Layer.txt

Chapter 8 - Inside The Model Layer
==================================

Much of the discussion so far has been devoted to building pages, and processing requests and responses. But the business logic of a web application relies mostly on its data model. Symfony's default model component is based on an object/relational mapping layer known as the Propel project ([http://propel.phpdb.org/](http://propel.phpdb.org/)). In a symfony application, you access data stored in a database and modify it through objects; you never address the database explicitly. This maintains a high level of abstraction and portability.

This chapter explains how to create an object data model, and the way to access and modify the data in Propel. It also demonstrates the integration of Propel in Symfony.

Why Use an ORM and an Abstraction Layer?
----------------------------------------

Databases are relational. PHP 5 and symfony are object-oriented. In order to most effectively access the database in an object-oriented context, an interface translating the object logic to the relational logic is required. As explained in Chapter 1, this interface is called an object-relational mapping (ORM), and it is made up of objects that give access to data and keep business rules within themselves.

The main benefit of an ORM is reusability, allowing the methods of a data object to be called from various parts of the application, even from different applications. The ORM layer also encapsulates the data logic--for instance, the calculation of a forum user rating based on how many contributions were made and how popular these contributions are. When a page needs to display such a user rating, it simply calls a method of the data model, without worrying about the details of the calculation. If the calculation changes afterwards, you will just need to modify the rating method in the model, leaving the rest of the application unchanged.

Using objects instead of records, and classes instead of tables, has another benefit: They allow you to add new accessors to your objects that don't necessarily match a column in a table. For instance, if you have a table called `client` with two fields named `first_name` and `last_name`, you might like to be able to require just a `Name`. In an object-oriented world, it is as easy as adding a new accessor method to the `Client` class, as in Listing 8-1. From the application point of view, there is no difference between the `FirstName`, `LastName`, and `Name` attributes of the `Client` class. Only the class itself can determine which attributes correspond to a database column.

Listing 8-1 - Accessors Mask the Actual Table Structure in a Model Class

    [php]
    public function getName()
    {
      return $this->getFirstName().' '.$this->getLastName();
    }

All the repeated data-access functions and the business logic of the data itself can be kept in such objects. Suppose you have a `ShoppingCart` class in which you keep `Items` (which are objects). To get the full amount of the shopping cart for the checkout, write a custom method to encapsulate the actual calculation, as shown in Listing 8-2.

Listing 8-2 - Accessors Mask the Data Logic

    [php]
    public function getTotal()
    {
      $total = 0;
      foreach ($this->getItems() as $item)
      {
        $total += $item->getPrice() * $item->getQuantity();
      }

      return $total;
    }

There is another important point to consider when building data-access procedures: Database vendors use different SQL syntax variants. Switching to another database management system (DBMS) forces you to rewrite part of the SQL queries that were designed for the previous one. If you build your queries using a database-independent syntax, and leave the actual SQL translation to a third-party component, you can switch database systems without pain. This is the goal of the database abstraction layer. It forces you to use a specific syntax for queries, and does the dirty job of conforming to the DBMS particulars and optimizing the SQL code.

The main benefit of an abstraction layer is portability, because it makes switching to another database possible, even in the middle of a project. Suppose that you need to write a quick prototype for an application, but the client hasn't decided yet which database system would best suit his needs. You can start building your application with SQLite, for instance, and switch to MySQL, PostgreSQL, or Oracle when the client is ready to decide. Just change one line in a configuration file, and it works.

Symfony uses Propel as the ORM, and Propel uses Creole for database abstraction. These two third-party components, both developed by the Propel team, are seamlessly integrated into symfony, and you can consider them as part of the framework. Their syntax and conventions, described in this chapter, were adapted so that they differ from the symfony ones as little as possible.

>**NOTE**
>In a symfony project, all the applications share the same model. That's the whole point of the project level: regrouping applications that rely on common business rules. This is the reason that the model is independent from the applications and the model files are stored in a `lib/model/` directory at the root of the project.

Symfony's Database Schema
-------------------------

In order to create the data object model that symfony will use, you need to translate whatever relational model your database has to an object data model. The ORM needs a description of the relational model to do the mapping, and this is called a schema. In a schema, you define the tables, their relations, and the characteristics of their columns.

Symfony's syntax for schemas uses the YAML format. The `schema.yml` files must be located in the `myproject/config/` directory.

>**NOTE**
>Symfony also understands the Propel native XML schema format, as described in the "Beyond the schema.yml: The schema.xml" section later in this chapter.

### Schema Example

How do you translate a database structure into a schema? An example is the best way to understand it. Imagine that you have a blog database with two tables: `blog_article` and `blog_comment`, with the structure shown in Figure 8-1.

Figure 8-1 - A blog database table structure

![A blog database table structure](/images/book/F0801.png "A blog database table structure")

The related `schema.yml` file should look like Listing 8-3.

Listing 8-3 - Sample `schema.yml`

    propel:
      blog_article:
        _attributes: { phpName: Article }
        id:
        title:       varchar(255)
        content:     longvarchar
        created_at:
      blog_comment:
        _attributes: { phpName: Comment }
        id:
        article_id:
        author:      varchar(255)
        content:     longvarchar
        created_at:

Notice that the name of the database itself (`blog`) doesn't appear in the `schema.yml` file. Instead, the database is described under a connection name (`propel` in this example). This is because the actual connection settings can depend on the environment in which your application runs. For instance, when you run your application in the development environment, you will access a development database (maybe `blog_dev`), but with the same schema as the production database. The connection settings will be specified in the `databases.yml` file, described in the "Database Connections" section later in this chapter. The schema doesn't contain any detailed connection to settings, only a connection name, to maintain database abstraction.

### Basic Schema Syntax

In a `schema.yml` file, the first key represents a connection name. It can contain several tables, each having a set of columns. According to the YAML syntax, the keys end with a colon, and the structure is shown through indentation (one or more spaces, but no tabulations).

A table can have special attributes, including the `phpName` (the name of the class that will be generated). If you don't mention a phpName for a table, symfony creates it based on the camelCase version of the table name.

>**TIP**
>The camelCase convention removes underscores from words, and capitalizes the first letter of inner words. The default camelCase versions of `blog_article` and `blog_comment` are `BlogArticle` and `BlogComment`. The name of this convention comes from the appearance of capitals inside a long word, suggestive of the humps of a camel.

A table contains columns. The column value can be defined in three different ways:

  * If you define nothing, symfony will guess the best attributes according to the column name and a few conventions that will be described in the "Empty Columns" section later in this chapter. For instance, the `id` column in Listing 8-3 doesn't need to be defined. Symfony will make it an auto-incremented integer, primary key of the table. The article_id in the `blog_comment` table will be understood as a foreign key to the `blog_article` table (columns ending with `_id` are considered to be foreign keys, and the related table is automatically determined according to the first part of the column name). Columns called `created_at` are automatically set to the `timestamp` type. For all these columns, you don't need to specify any type. This is one of the reasons why `schema.yml` is so easy to write.
  * If you define only one attribute, it is the column type. Symfony understands the usual column types: `boolean`, `integer`, `float`, `date`, `varchar(size)`, `longvarchar` (converted, for instance, to `text` in MySQL), and so on. For text content over 256 characters, you need to use the `longvarchar` type, which has no size (but cannot exceed 65KB in MySQL). Note that the `date` and `timestamp` types have the usual limitations of Unix dates and cannot be set to a date prior to 1970-01-01. As you may need to set older dates (for instance, for dates of birth), a format of dates "before Unix" can be used with bu_date and `bu_timestamp`.
  * If you need to define other column attributes (like default value, required, and so on), you should write the column attributes as a set of `key: value`. This extended schema syntax is described later in the chapter.

Columns can also have a `phpName` attribute, which is the capitalized version of the name (`Id`, `Title`, `Content`, and so on) and doesn't need overriding in most cases.

Tables can also contain explicit foreign keys and indexes, as well as a few database-specific structure definitions. Refer to the "Extended Schema Syntax" section later in this chapter to learn more.

Model Classes
-------------

The schema is used to build the model classes of the ORM layer. To save execution time, these classes are generated with a command-line task called `propel-build-model`.

    > php symfony propel:build-model

>**TIP**
>After building your model, you must remember to clear symfony's internal cache with `php symfony cc` so symfony can find your newly created models.

Typing this command will launch the analysis of the schema and the generation of base data model classes in the `lib/model/om/` directory of your project:

  * `BaseArticle.php`
  * `BaseArticlePeer.php`
  * `BaseComment.php`
  * `BaseCommentPeer.php`

In addition, the actual data model classes will be created in `lib/model/`:

  * `Article.php`
  * `ArticlePeer.php`
  * `Comment.php`
  * `CommentPeer.php`

You defined only two tables, and you end up with eight files. There is nothing wrong, but it deserves some explanation.

### Base and Custom Classes

Why keep two versions of the data object model in two different directories?

You will probably need to add custom methods and properties to the model objects (think about the `getName()` method in Listing 8-1). But as your project develops, you will also add tables or columns. Whenever you change the `schema.yml` file, you need to regenerate the object model classes by making a new call to propel-build-model. If your custom methods were written in the classes actually generated, they would be erased after each generation.

The `Base` classes kept in the `lib/model/om/` directory are the ones directly generated from the schema. You should never modify them, since every new build of the model will completely erase these files.

On the other hand, the custom object classes, kept in the `lib/model/` directory, actually inherit from the `Base` ones. When the `propel-build-model` task is called on an existing model, these classes are not modified. So this is where you can add custom methods.

Listing 8-4 presents an example of a custom model class as created by the first call to the `propel-build-model` task.

Listing 8-4 - Sample Model Class File, in `lib/model/Article.php`

    [php]
    <?php

    class Article extends BaseArticle
    {
    }

It inherits all the methods of the `BaseArticle` class, but a modification in the schema will not affect it.

The mechanism of custom classes extending base classes allows you to start coding, even without knowing the final relational model of your database. The related file structure makes the model both customizable and evolutionary.

### Object and Peer Classes

`Article` and `Comment` are object classes that represent a record in the database. They give access to the columns of a record and to related records. This means that you will be able to know the title of an article by calling a method of an Article object, as in the example shown in Listing 8-5.

Listing 8-5 - Getters for Record Columns Are Available in the Object Class

    [php]
    $article = new Article();
    ...
    $title = $article->getTitle();

`ArticlePeer` and `CommentPeer` are peer classes; that is, classes that contain static methods to operate on the tables. They provide a way to retrieve records from the tables. Their methods usually return an object or a collection of objects of the related object class, as shown in Listing 8-6.

Listing 8-6 - Static Methods to Retrieve Records Are Available in the Peer Class

    [php]
    $articles = ArticlePeer::retrieveByPks(array(123, 124, 125));
    // $articles is an array of objects of class Article

>**NOTE**
>From a data model point of view, there cannot be any peer object. That's why the methods of the peer classes are called with a `::` (for static method call), instead of the usual `->` (for instance method call).

So combining object and peer classes in a base and a custom version results in four classes generated per table described in the schema. In fact, there is a fifth class created in the `lib/model/map/` directory, which contains metadata information about the table that is needed for the runtime environment. But as you will probably never change this class, you can forget about it.

Accessing Data
--------------

In symfony, your data is accessed through objects. If you are used to the relational model and using SQL to retrieve and alter your data, the object model methods will likely look complicated. But once you've tasted the power of object orientation for data access, you will probably like it a lot.

But first, let's make sure we share the same vocabulary. Relational and object data model use similar concepts, but they each have their own nomenclature:

Relational    | Object-Oriented
------------- | ---------------
Table         | Class
Row, record   | Object
Field, column | Property

### Retrieving the Column Value

When symfony builds the model, it creates one base object class for each of the tables defined in the `schema.yml`. Each of these classes comes with default constructors, accessors, and mutators based on the column definitions: The `new`, `getXXX()`, and `setXXX()` methods help to create objects and give access to the object properties, as shown in Listing 8-7.

Listing 8-7 - Generated Object Class Methods

    [php]
    $article = new Article();
    $article->setTitle('My first article');
    $article->setContent('This is my very first article.\n Hope you enjoy it!');

    $title   = $article->getTitle();
    $content = $article->getContent();

>**NOTE**
>The generated object class is called `Article`, which is the `phpName` given to the `blog_article` table. If the `phpName` were not defined in the schema, the class would have been called `BlogArticle`. The accessors and mutators use a camelCase variant of the column names, so the `getTitle()` method retrieves the value of the `title` column.

To set several fields at one time, you can use the `fromArray()` method, also generated for each object class, as shown in Listing 8-8.

Listing 8-8 - The `fromArray()` Method Is a Multiple Setter

    [php]
    $article->fromArray(array(
      'title'   => 'My first article',
      'content' => 'This is my very first article.\n Hope you enjoy it!',
    ));

### Retrieving Related Records

The `article_id` column in the `blog_comment` table implicitly defines a foreign key to the `blog_article` table. Each comment is related to one article, and one article can have many comments. The generated classes contain five methods translating this relationship in an object-oriented way, as follows:

  * `$comment->getArticle()`: To get the related `Article` object
  * `$comment->getArticleId()`: To get the ID of the related `Article` object
  * `$comment->setArticle($article)`: To define the related `Article` object
  * `$comment->setArticleId($id)`: To define the related `Article` object from an ID
  * `$article->getComments()`: To get the related `Comment` objects

The `getArticleId()` and `setArticleId()` methods show that you can consider the article_id column as a regular column and set the relationships by hand, but they are not very interesting. The benefit of the object-oriented approach is much more apparent in the three other methods. Listing 8-9 shows how to use the generated setters.

Listing 8-9 - Foreign Keys Are Translated into a Special Setter

    [php]
    $comment = new Comment();
    $comment->setAuthor('Steve');
    $comment->setContent('Gee, dude, you rock: best article ever!');

    // Attach this comment to the previous $article object
    $comment->setArticle($article);

    // Alternative syntax
    // Only makes sense if the object is already saved in the database
    $comment->setArticleId($article->getId());

Listing 8-10 shows how to use the generated getters. It also demonstrates how to chain method calls on model objects.

Listing 8-10 - Foreign Keys Are Translated into Special Getters

    [php]
    // Many to one relationship
    echo $comment->getArticle()->getTitle();
     => My first article
    echo $comment->getArticle()->getContent();
     => This is my very first article.
        Hope you enjoy it!

    // One to many relationship
    $comments = $article->getComments();

The `getArticle()` method returns an object of class `Article`, which benefits from the `getTitle()` accessor. This is much better than doing the join yourself, which may take a few lines of code (starting from the `$comment->getArticleId()` call).

The `$comments` variable in Listing 8-10 contains an array of objects of class `Comment`. You can display the first one with `$comments[0]` or iterate through the collection with `foreach ($comments as $comment)`.

>**NOTE**
>Objects from the model are defined with a singular name by convention, and you can now understand why. The foreign key defined in the `blog_comment` table causes the creation of a `getComments()` method, named by adding an `s` to the `Comment` object name. If you gave the model object a plural name, the generation would lead to a method named `getCommentss()`, which doesn't make sense.

### Saving and Deleting Data

By calling the `new` constructor, you created a new object, but not an actual record in the `blog_article` table. Modifying the object has no effect on the database either. In order to save the data into the database, you need to call the `save()` method of the object.

    [php]
    $article->save();

The ORM is smart enough to detect relationships between objects, so saving the `$article` object also saves the related `$comment` object. It also knows if the saved object has an existing counterpart in the database, so the call to `save()` is sometimes translated in SQL by an `INSERT`, and sometimes by an `UPDATE`. The primary key is automatically set by the `save()` method, so after saving, you can retrieve the new primary key with `$article->getId()`.

>**TIP**
>You can check if an object is new by calling isNew(). And if you wonder if an object has been modified and deserves saving, call its `isModified()` method.

If you read comments to your articles, you might change your mind about the interest of publishing on the Internet. And if you don't appreciate the irony of article reviewers, you can easily delete the comments with the `delete()` method, as shown in Listing 8-11.

Listing 8-11 - Delete Records from the Database with the `delete()`Method on the Related Object

    [php]
    foreach ($article->getComments() as $comment)
    {
      $comment->delete();
    }

>**TIP**
>Even after calling the `delete()` method, an object remains available until the end of the request. To determine if an object is deleted in the database, call the `isDeleted()` method.

### Retrieving Records by Primary Key

If you know the primary key of a particular record, use the `retrieveByPk()` class method of the peer class to get the related object.

    [php]
    $article = ArticlePeer::retrieveByPk(7);

The `schema.yml` file defines the `id` field as the primary key of the `blog_article` table, so this statement will actually return the article that has `id` 7. As you used the primary key, you know that only one record will be returned; the `$article` variable contains an object of class `Article`.

In some cases, a primary key may consist of more than one column. In those cases, the `retrieveByPK()` method accepts multiple parameters, one for each primary key column.

You can also select multiple objects based on their primary keys, by calling the generated `retrieveByPKs()` method, which expects an array of primary keys as a parameter.

### Retrieving Records with Criteria

When you want to retrieve more than one record, you need to call the `doSelect()` method of the peer class corresponding to the objects you want to retrieve. For instance, to retrieve objects of class `Article`, call `ArticlePeer::doSelect()`.

The first parameter of the `doSelect()` method is an object of class `Criteria`, which is a simple query definition class defined without SQL for the sake of database abstraction.

An empty `Criteria` returns all the objects of the class. For instance, the code shown in Listing 8-12 retrieves all the articles.

Listing 8-12 - Retrieving Records by Criteria with `doSelect()`--Empty Criteria

    [php]
    $c = new Criteria();
    $articles = ArticlePeer::doSelect($c);

    // Will result in the following SQL query
    SELECT blog_article.ID, blog_article.TITLE, blog_article.CONTENT,
           blog_article.CREATED_AT
    FROM   blog_article;

>**SIDEBAR**
>Hydrating
>
>The call to `::doSelect()` is actually much more powerful than a simple SQL query. First, the SQL is optimized for the DBMS you choose. Second, any value passed to the `Criteria` is escaped before being integrated into the SQL code, which prevents SQL injection risks. Third, the method returns an array of objects, rather than a result set. The ORM automatically creates and populates objects based on the database result set. This process is called hydrating.

For a more complex object selection, you need an equivalent of the WHERE, ORDER BY, GROUP BY, and other SQL statements. The `Criteria` object has methods and parameters for all these conditions. For example, to get all comments written by Steve, ordered by date, build a `Criteria` as shown in Listing 8-13.

Listing 8-13 - Retrieving Records by Criteria with `doSelect()`--Criteria with Conditions

    [php]
    $c = new Criteria();
    $c->add(CommentPeer::AUTHOR, 'Steve');
    $c->addAscendingOrderByColumn(CommentPeer::CREATED_AT);
    $comments = CommentPeer::doSelect($c);

    // Will result in the following SQL query
    SELECT blog_comment.ARTICLE_ID, blog_comment.AUTHOR, blog_comment.CONTENT,
           blog_comment.CREATED_AT
    FROM   blog_comment
    WHERE  blog_comment.author = 'Steve'
    ORDER BY blog_comment.CREATED_AT ASC;

The class constants passed as parameters to the add() methods refer to the property names. They are named after the capitalized version of the column names. For instance, to address the `content` column of the `blog_article` table, use the `ArticlePeer::CONTENT` class constant.

>**NOTE**
>Why use `CommentPeer::AUTHOR` instead of `blog_comment.AUTHOR`, which is the way it will be output in the SQL query anyway? Suppose that you need to change the name of the author field to `contributor` in the database. If you used `blog_comment.AUTHOR`, you would have to change it in every call to the model. On the other hand, by using `CommentPeer::AUTHOR`, you simply need to change the column name in the `schema.yml` file, keep `phpName` as `AUTHOR`, and rebuild the model.

Table 8-1 compares the SQL syntax with the `Criteria` object syntax.

Table 8-1 - SQL and Criteria Object Syntax

SQL                                                          | Criteria
------------------------------------------------------------ | -----------------------------------------------
`WHERE column = value`                                       | `->add(column, value);`
`WHERE column <> value`                                      | `->add(column, value, Criteria::NOT_EQUAL);`
**Other Comparison Operators**                               | 
`> , <`                                                      | `Criteria::GREATER_THAN, Criteria::LESS_THAN`
`>=, <=`                                                     | `Criteria::GREATER_EQUAL, Criteria::LESS_EQUAL`
`IS NULL, IS NOT NULL`                                       | `Criteria::ISNULL, Criteria::ISNOTNULL`
`LIKE, ILIKE`                                                | `Criteria::LIKE, Criteria::ILIKE`
`IN, NOT IN`                                                 | `Criteria::IN, Criteria::NOT_IN`
**Other SQL Keywords**                                       |
`ORDER BY column ASC`                                        | `->addAscendingOrderByColumn(column);`
`ORDER BY column DESC`                                       | `->addDescendingOrderByColumn(column);`
`LIMIT limit`                                                | `->setLimit(limit)`
`OFFSET offset`                                              | `->setOffset(offset) `
`FROM table1, table2 WHERE table1.col1 = table2.col2`        | `->addJoin(col1, col2)`
`FROM table1 LEFT JOIN table2 ON table1.col1 = table2.col2`  | `->addJoin(col1, col2, Criteria::LEFT_JOIN)`
`FROM table1 RIGHT JOIN table2 ON table1.col1 = table2.col2` | `->addJoin(col1, col2, Criteria::RIGHT_JOIN)`

>**TIP**
>The best way to discover and understand which methods are available in generated classes is to look at the `Base` files in the `lib/model/om/` folder after generation. The method names are pretty explicit, but if you need more comments on them, set the `propel.builder.addComments` parameter to `true` in the `config/propel.ini` file and rebuild the model.

Listing 8-14 shows another example of `Criteria` with multiple conditions. It retrieves all the comments by Steve on articles containing the word "enjoy," ordered by date.

Listing 8-14 - Another Example of Retrieving Records by Criteria with `doSelect()`--Criteria with Conditions

    [php]
    $c = new Criteria();
    $c->add(CommentPeer::AUTHOR, 'Steve');
    $c->addJoin(CommentPeer::ARTICLE_ID, ArticlePeer::ID);
    $c->add(ArticlePeer::CONTENT, '%enjoy%', Criteria::LIKE);
    $c->addAscendingOrderByColumn(CommentPeer::CREATED_AT);
    $comments = CommentPeer::doSelect($c);

    // Will result in the following SQL query
    SELECT blog_comment.ID, blog_comment.ARTICLE_ID, blog_comment.AUTHOR,
           blog_comment.CONTENT, blog_comment.CREATED_AT
    FROM   blog_comment, blog_article
    WHERE  blog_comment.AUTHOR = 'Steve'
           AND blog_article.CONTENT LIKE '%enjoy%'
           AND blog_comment.ARTICLE_ID = blog_article.ID
    ORDER BY blog_comment.CREATED_AT ASC

Just as SQL is a simple language that allows you to build very complex queries, the Criteria object can handle conditions with any level of complexity. But since many developers think first in SQL before translating a condition into object-oriented logic, the `Criteria` object may be difficult to comprehend at first. The best way to understand it is to learn from examples and sample applications. The symfony project website, for instance, is full of `Criteria` building examples that will enlighten you in many ways.

In addition to the `doSelect()` method, every peer class has a `doCount()` method, which simply counts the number of records satisfying the criteria passed as a parameter and returns the count as an integer. As there is no object to return, the hydrating process doesn't occur in this case, and the `doCount()` method is faster than `doSelect()`.

The peer classes also provide `doDelete()`, `doInsert()`, and `doUpdate()` methods, which all expect a `Criteria` as a parameter. These methods allow you to issue `DELETE`, `INSERT`, and `UPDATE` queries to your database. Check the generated peer classes in your model for more details on these Propel methods.

Finally, if you just want the first object returned, replace `doSelect()` with a `doSelectOne()` call. This may be the case when you know that a `Criteria` will return only one result, and the advantage is that this method returns an object rather than an array of objects.

>**TIP**
>When a `doSelect()` query returns a large number of results, you might want to display only a subset of it in your response. Symfony provides a pager class called sfPropelPager, which automates the pagination of results. Check the pager documentation at [http://www.symfony-project.org/cookbook/trunk/pager](http://www.symfony-project.org/cookbook/trunk/pager) for more information and usage examples.

### Using Raw SQL Queries

Sometimes, you don't want to retrieve objects, but want to get only synthetic results calculated by the database. For instance, to get the latest creation date of all articles, it doesn't make sense to retrieve all the articles and to loop on the array. You will prefer to ask the database to return only the result, because it will skip the object hydrating process.

On the other hand, you don't want to call the PHP commands for database management directly, because then you would lose the benefit of database abstraction. This means that you need to bypass the ORM (Propel) but not the database abstraction (Creole).

Querying the database with Creole requires that you do the following:

  1. Get a database connection.
  2. Build a query string.
  3. Create a statement out of it.
  4. Iterate on the result set that results from the statement execution.

If this looks like gibberish to you, the code in Listing 8-15 will probably be more explicit.

Listing 8-15 - Custom SQL Query with Creole

    [php]
    $connection = Propel::getConnection();
    $query = 'SELECT MAX(%s) AS max FROM %s';
    $query = sprintf($query, ArticlePeer::CREATED_AT, ArticlePeer::TABLE_NAME);
    $statement = $connection->prepareStatement($query);
    $resultset = $statement->executeQuery();
    $resultset->next();
    $max = $resultset->getInt('max');

Just like Propel selections, Creole queries are tricky when you first start using them. Once again, examples from existing applications and tutorials will show you the right way.

>**CAUTION**
>If you are tempted to bypass this process and access the database directly, you risk losing the security and abstraction provided by Creole. Doing it the Creole way is longer, but it forces you to use good practices that guarantee the performance, portability, and security of your application. This is especially true for queries that contain parameters coming from a untrusted source (such as an Internet user). Creole does all the necessary escaping and secures your database. Accessing the database directly puts you at risk of SQL-injection attacks.

### Using Special Date Columns

Usually, when a table has a column called `created_at`, it is used to store a timestamp of the date when the record was created. The same applies to updated_at columns, which are to be updated each time the record itself is updated, to the value of the current time.

The good news is that symfony will recognize the names of these columns and handle their updates for you. You don't need to manually set the `created_at` and `updated_at` columns; they will automatically be updated, as shown in Listing 8-16. The same applies for columns named `created_on` and `updated_on`.

Listing 8-16 - `created_at` and `updated_at` Columns Are Dealt with Automatically

    [php]
    $comment = new Comment();
    $comment->setAuthor('Steve');
    $comment->save();

    // Show the creation date
    echo $comment->getCreatedAt();
      => [date of the database INSERT operation]

Additionally, the getters for date columns accept a date format as an argument:

    [php]
    echo $comment->getCreatedAt('Y-m-d');

>**SIDEBAR**
>Refactoring to the Data layer
>
>When developing a symfony project, you often start by writing the domain logic code in the actions. But the database queries and model manipulation should not be stored in the controller layer. So all the logic related to the data should be moved to the model layer. Whenever you need to do the same request in more than one place in your actions, think about transferring the related code to the model. It helps to keep the actions short and readable.
>
>For example, imagine the code needed in a blog to retrieve the ten most popular articles for a given tag (passed as request parameter). This code should not be in an action, but in the model. In fact, if you need to display this list in a template, the action should simply look like this:
>
>     [php]
>     public function executeShowPopularArticlesForTag()
>     {
>       $tag = TagPeer::retrieveByName($this->getRequestParameter('tag'));
>       $this->foward404Unless($tag);
>       $this->articles = $tag->getPopularArticles(10);
>     }
>
>The action creates an object of class `Tag` from the request parameter. Then all the code needed to query the database is located in a `getPopularArticles()` method of this class. It makes the action more readable, and the model code can easily be reused in another action.
>
>Moving code to a more appropriate location is one of the techniques of refactoring. If you do it often, your code will be easy to maintain and to understand by other developers. A good rule of thumb about when to do refactoring to the data layer is that the code of an action should rarely contain more than ten lines of PHP code.

Database Connections
--------------------

The data model is independent from the database used, but you will definitely use a database. The minimum information required by symfony to send requests to the project database is the name, the access codes, and the type of database. These connection settings should be entered in the `databases.yml` file located in the `config/` directory. Listing 8-17 shows an example of such a file.

Listing 8-17 - Sample Database Connection Settings, in `myproject/config/databases.yml`

    prod:
      propel:
        param:
          hostspec:           mydataserver
          username:           myusername
          password:           xxxxxxxxxx

    all:
      propel:
        class:                sfPropelDatabase
        param:
          phptype:            mysql     # Database vendor
          hostspec:           localhost
          database:           blog
          username:           login
          password:           passwd
          port:               80
          encoding:           utf8      # Default charset for table creation
          persistent:         true      # Use persistent connections

The connection settings are environment-dependent. You can define distinct settings for the `prod`, `dev`, and `test` environments, or any other environment in your application. This configuration can also be overridden per application, by setting different values in an application-specific file, such as in `apps/frontend/config/databases.yml`. For instance, you can use this approach to have different security policies for a front-end and a back-end application, and define several database users with different privileges in your database to handle this.

For each environment, you can define many connections. Each connection refers to a schema being labeled with the same name. In the example in Listing 8-17, the propel connection refers to the `propel` schema in Listing 8-3.

The permitted values of the `phptype` parameter are the ones of the database systems supported by Creole:

  * `mysql`
  * `sqlserver`
  * `pgsql`
  * `sqlite`
  * `oracle`

`hostspec`, `database`, `username`, and `password` are the usual database connection settings. They can also be written in a shorter way as a data source name (DSN). Listing 8-18 is equivalent to the `all:` section of Listing 8-17.

Listing 8-18 - Shorthand Database Connection Settings

    all:
      propel:
        class:          sfPropelDatabase
        param:
          dsn:          mysql://login:passwd@localhost/blog

If you use a SQLite database, the `hostspec` parameter must be set to the path of the database file. For instance, if you keep your blog database in `data/blog.db`, the `databases.yml` file will look like Listing 8-19.

Listing 8-19 - Database Connection Settings for SQLite Use a File Path As Host

        all:
          propel:
            class:          sfPropelDatabase
            param:
              phptype:  sqlite
              database: %SF_DATA_DIR%/blog.db

Extending the Model
-------------------

The generated model methods are great but often not sufficient. As soon as you implement your own business logic, you need to extend it, either by adding new methods or by overriding existing ones.

### Adding New Methods

You can add new methods to the empty model classes generated in the `lib/model/` directory. Use `$this` to call methods of the current object, and use `self::` to call static methods of the current class. Remember that the custom classes inherit methods from the `Base` classes located in the `lib/model/om/` directory.

For instance, for the `Article` object generated based on Listing 8-3, you can add a magic `__toString()` method so that echoing an object of class `Article` displays its title, as shown in Listing 8-20.

Listing 8-20 - Customizing the Model, in `lib/model/Article.php`

    [php]
    <?php

    class Article extends BaseArticle
    {
      public function __toString()
      {
        return $this->getTitle();  // getTitle() is inherited from BaseArticle
      }
    }

You can also extend the peer classes--for instance, to add a method to retrieve all articles ordered by creation date, as shown in Listing 8-21.

Listing 8-21 - Customizing the Model, in `lib/model/ArticlePeer.php`

    [php]
    <?php

    class ArticlePeer extends BaseArticlePeer
    {
      public static function getAllOrderedByDate()
      {
        $c = new Criteria();
        $c->addAscendingOrderByColumn(self::CREATED_AT);
        return self::doSelect($c);

      }
    }

The new methods are available in the same way as the generated ones, as shown in Listing 8-22.

Listing 8-22 - Using Custom Model Methods Is Like Using the Generated Methods

    [php]
    foreach (ArticlePeer::getAllOrderedByDate() as $article)
    {
      echo $article;      // Will call the magic __toString() method
    }

### Overriding Existing Methods

If some of the generated methods in the `Base` classes don't fit your requirements, you can still override them in the custom classes. Just make sure that you use the same method signature (that is, the same number of arguments).

For instance, the `$article->getComments()` method returns an array of `Comment` objects, in no particular order. If you want to have the results ordered by creation date, with the latest comment coming first, then override the `getComments()` method, as shown in Listing 8-23. Be aware that the original `getComments()` method (found in `lib/model/om/BaseArticle.php`) expects a criteria value and a connection value as parameters, so your function must do the same.

Listing 8-23 - Overriding Existing Model Methods, in `lib/model/Article.php`

    [php]
    public function getComments($criteria = null, $con = null)
    {
      if (is_null($criteria))
      {
        $criteria = new Criteria();
      }
      else
      {
        // Objects are passed by reference in PHP5, so to avoid modifying the original, you must clone it
        $criteria = clone $criteria;
      }
      $criteria->addDescendingOrderByColumn(CommentPeer::CREATED_AT);

      return parent::getComments($criteria, $con);
    }

The custom method eventually calls the one of the parent Base class, and that's good practice. However, you can completely bypass it and return the result you want.

### Using Model Behaviors

Some model modifications are generic and can be reused. For instance, methods to make a model object sortable and an optimistic lock to prevent conflicts between concurrent object saving are generic extensions that can be added to many classes.

Symfony packages these extensions into behaviors. Behaviors are external classes that provide additional methods to model classes. The model classes already contain hooks, and symfony knows how to extend them by way of `sfMixer` (see Chapter 17 for details).

To enable behaviors in your model classes, you must modify one setting in the `config/propel.ini` file:

    propel.builder.AddBehaviors = true     // Default value is false

There is no behavior bundled by default in symfony, but they can be installed via plug-ins. Once a behavior plug-in is installed, you can assign the behavior to a class with a single line. For instance, if you install the sfPropelParanoidBehaviorPlugin in your application, you can extend an `Article` class with this behavior by adding the following at the end of the `Article.class.php`:

    [php]
    sfPropelBehavior::add('Article', array(
      'paranoid' => array('column' => 'deleted_at')
    ));

After rebuilding the model, deleted `Article` objects will remain in the database, invisible to the queries using the ORM, unless you temporarily disable the behavior with `sfPropelParanoidBehavior::disable()`.

**New in symfony 1.1**: Alternatively, you can also declare behaviors directly in the `schema.yml`, by listing them under the `_behaviors` key (see Listing 8-34 below).

Check the list of symfony plug-ins in the wiki to find behaviors ([http://trac.symfony-project.com/wiki/SymfonyPlugins#Behaviors](http://trac.symfony-project.com/wiki/SymfonyPlugins#Behaviors)). Each has its own documentation and installation guide.

Extended Schema Syntax
----------------------

A `schema.yml` file can be simple, as shown in Listing 8-3. But relational models are often complex. That's why the schema has an extensive syntax able to handle almost every case.

### Attributes

Connections and tables can have specific attributes, as shown in Listing 8-24. They are set under an `_attributes` key.

Listing 8-24 - Attributes for Connections and Tables

    propel:
      _attributes:   { noXsd: false, defaultIdMethod: none, package: lib.model }
      blog_article:
        _attributes: { phpName: Article }

You may want your schema to be validated before code generation takes place. To do that, deactivate the `noXSD` attribute for the connection. The connection also supports the `defaultIdMethod` attribute. If none is provided, then the database's native method of generating IDs will be used--for example, `autoincrement` for MySQL, or `sequences` for PostgreSQL. The other possible value is `none`.

The `package` attribute is like a namespace; it determines the path where the generated classes are stored. It defaults to `lib/model/`, but you can change it to organize your model in subpackages. For instance, if you don't want to mix the core business classes and the classes defining a database-stored statistics engine in the same directory, then define two schemas with `lib.model.business` and `lib.model.stats` packages.

You already saw the `phpName` table attribute, used to set the name of the generated class mapping the table.

Tables that contain localized content (that is, several versions of the content, in a related table, for internationalization) also take two additional attributes (see Chapter 13 for details), as shown in Listing 8-25.

Listing 8-25 - Attributes for i18n Tables

    propel:
      blog_article:
        _attributes: { isI18N: true, i18nTable: db_group_i18n }

>**SIDEBAR**
>Dealing with multiple schemas
>
>You can have more than one schema per application. Symfony will take into account every file ending with `schema.yml` or `schema.xml` in the `config/` folder. If your application has many tables, or if some tables don't share the same connection, you will find this approach very useful.
>
>Consider these two schemas:
>
>
>      // In config/business-schema.yml
>      propel:
>        blog_article:
>          _attributes: { phpName: Article }
>        id:
>        title: varchar(50)
>
>      // In config/stats-schema.yml
>      propel:
>        stats_hit:
>          _attributes: { phpName: Hit }
>        id:
>        resource: varchar(100)
>        created_at:
>
>
>Both schemas share the same connection (`propel`), and the `Article` and `Hit` classes will be generated under the same `lib/model/` directory. Everything happens as if you had written only one schema.
>
>You can also have different schemas use different connections (for instance, `propel` and `propel_bis`, to be defined in `databases.yml`) and organize the generated classes in subdirectories:
>
>
>      // In config/business-schema.yml
>      propel:
>        blog_article:
>          _attributes: { phpName: Article, package: lib.model.business }
>        id:
>        title: varchar(50)
>
>      // In config/stats-schema.yml
>      propel_bis:
>        stats_hit:
>          _attributes: { phpName: Hit, package: lib.model.stat }
>        id:
>        resource: varchar(100)
>        created_at:
>
>
>Many applications use more than one schema. In particular, some plug-ins have their own schema and package to avoid messing with your own classes (see Chapter 17 for details).

### Column Details

The basic syntax gives you two choices: let symfony deduce the column characteristics from its name (by giving an empty value) or define the type with one of the type keywords. Listing 8-26 demonstrates these choices.

Listing 8-26 - Basic Column Attributes

    propel:
      blog_article:
        id:                 # Let symfony do the work
        title: varchar(50)  # Specify the type yourself

But you can define much more for a column. If you do, you will need to define column settings as an associative array, as shown in Listing 8-27.

Listing 8-27 - Complex Column Attributes

    propel:
      blog_article:
        id:       { type: integer, required: true, primaryKey: true, autoIncrement: true }
        name:     { type: varchar(50), default: foobar, index: true }
        group_id: { type: integer, foreignTable: db_group, foreignReference: id, onDelete: cascade }

The column parameters are as follows:

  * `type`: Column type. The choices are `boolean`, `tinyint`, `smallint`, `integer`, `bigint`, `double`, `float`, `real`, `decimal`, `char`, `varchar(size)`, `longvarchar`, `date`, `time`, `timestamp`, `bu_date`, `bu_timestamp`, `blob`, and `clob`.
  * `required`: Boolean. Set it to `true` if you want the column to be required.
  * `default`: Default value.
  * `primaryKey`: Boolean. Set it to `true` for primary keys.
  * `autoIncrement`: Boolean. Set it to `true` for columns of type `integer` that need to take an auto-incremented value.
  * `sequence`: Sequence name for databases using sequences for `autoIncrement` columns (for example, PostgreSQL and Oracle).
  * `index`: Boolean. Set it to `true` if you want a simple index or to `unique` if you want a unique index to be created on the column.
  * `foreignTable`: A table name, used to create a foreign key to another table.
  * `foreignReference`: The name of the related column if a foreign key is defined via `foreignTable`.
  * `onDelete`: Determines the action to trigger when a record in a related table is deleted. When set to `setnull`, the foreign key column is set to `null`. When set to `cascade`, the record is deleted. If the database engine doesn't support the set behavior, the ORM emulates it. This is relevant only for columns bearing a `foreignTable` and a `foreignReference`.
  * `isCulture`: Boolean. Set it to `true` for culture columns in localized content tables (see Chapter 13).

### Foreign Keys

As an alternative to the `foreignTable` and `foreignReference` column attributes, you can add foreign keys under the `_foreignKeys:` key in a table. The schema in Listing 8-28 will create a foreign key on the `user_id` column, matching the `id` column in the `blog_user` table.

Listing 8-28 - Foreign Key Alternative Syntax

    propel:
      blog_article:
        id:
        title:   varchar(50)
        user_id: { type: integer }
        _foreignKeys:
          -
            foreignTable: blog_user
            onDelete:     cascade
            references:
              - { local: user_id, foreign: id }

The alternative syntax is useful for multiple-reference foreign keys and to give foreign keys a name, as shown in Listing 8-29.

Listing 8-29 - Foreign Key Alternative Syntax Applied to Multiple Reference Foreign Key

        _foreignKeys:
          my_foreign_key:
            foreignTable:  db_user
            onDelete:      cascade
            references:
              - { local: user_id, foreign: id }
              - { local: post_id, foreign: id }

### Indexes

As an alternative to the `index` column attribute, you can add indexes under the `_indexes:` key in a table. If you want to define unique indexes, you must use the `_uniques:` header instead. Listing 8-30 shows the alternative syntax for indexes.

Listing 8-30 - Indexes and Unique Indexes Alternative Syntax

    propel:
      blog_article:
        id:
        title:            varchar(50)
        created_at:
        _indexes:
          my_index:       [title, user_id]
        _uniques:
          my_other_index: [created_at]

The alternative syntax is useful only for indexes built on more than one column.

### Empty Columns

When meeting a column with no value, symfony will do some magic and add a value of its own. See Listing 8-31 for the details added to empty columns.

Listing 8-31 - Column Details Deduced from the Column Name

    // Empty columns named id are considered primary keys
    id:         { type: integer, required: true, primaryKey: true, autoIncrement: true }

    // Empty columns named XXX_id are considered foreign keys
    foobar_id:  { type: integer, foreignTable: db_foobar, foreignReference: id }

    // Empty columns named created_at, updated at, created_on and updated_on
    // are considered dates and automatically take the timestamp type
    created_at: { type: timestamp }
    updated_at: { type: timestamp }

For foreign keys, symfony will look for a table having the same `phpName` as the beginning of the column name, and if one is found, it will take this table name as the `foreignTable`.

### I18n Tables

Symfony supports content internationalization in related tables. This means that when you have content subject to internationalization, it is stored in two separate tables: one with the invariable columns and another with the internationalized columns.

In a `schema.yml` file, all that is implied when you name a table `foobar_i18n`. For instance, the schema shown in Listing 8-32 will be automatically completed with columns and table attributes to make the internationalized content mechanism work. Internally, symfony will understand it as if it were written like Listing 8-33. Chapter 13 will tell you more about i18n.

Listing 8-32 - Implied i18n Mechanism

    propel:
      db_group:
        id:
        created_at:

      db_group_i18n:
        name:        varchar(50)

Listing 8-33 - Explicit i18n Mechanism

    propel:
      db_group:
        _attributes: { isI18N: true, i18nTable: db_group_i18n }
        id:
        created_at:

      db_group_i18n:
        id:       { type: integer, required: true, primaryKey: true,foreignTable: db_group, foreignReference: id, onDelete: cascade }
        culture:  { isCulture: true, type: varchar(7), required: true,primaryKey: true }
        name:     varchar(50)

### Behaviors (new in symfony 1.1)

Behaviors are model modifiers provided by plug-ins that add new capabilities to your Propel classes. Chapter 17 explains more about behaviors. You can define behaviors right in the schema, by listing them for each table, together with their parameters, under the `_behaviors` key. Listing 8-34 gives an example by extending the `BlogArticle` class with the `paranoid` behavior.

Listing 8-34 - Behaviors Declaration

{{{
propel:
  blog_article:
    title:          varchar(50)
    _behaviors:
      paranoid:     { column: deleted_at }
}}}


### Beyond the schema.yml: The schema.xml

As a matter of fact, the schema.yml format is internal to symfony. When you call a propel- command, symfony actually translates this file into a `generated-schema.xml` file, which is the type of file expected by Propel to actually perform tasks on the model.

The `schema.xml` file contains the same information as its YAML equivalent. For example, Listing 8-3 is converted to the XML file shown in Listing 8-35.

Listing 8-35 - Sample `schema.xml`, Corresponding to Listing 8-3

    [xml]
    <?xml version="1.0" encoding="UTF-8"?>
     <database name="propel" defaultIdMethod="native" noXsd="true" package="lib.model">
        <table name="blog_article" phpName="Article">
          <column name="id" type="integer" required="true" primaryKey="true"autoIncrement="true" />
          <column name="title" type="varchar" size="255" />
          <column name="content" type="longvarchar" />
          <column name="created_at" type="timestamp" />
        </table>
        <table name="blog_comment" phpName="Comment">
          <column name="id" type="integer" required="true" primaryKey="true"autoIncrement="true" />
          <column name="article_id" type="integer" />
          <foreign-key foreignTable="blog_article">
            <reference local="article_id" foreign="id"/>
          </foreign-key>
          <column name="author" type="varchar" size="255" />
          <column name="content" type="longvarchar" />
          <column name="created_at" type="timestamp" />
        </table>
     </database>

The description of the `schema.xml` format can be found in the documentation and the "Getting Started" sections of the Propel project website ([http://propel.phpdb.org/docs/user_guide/chapters/appendices/AppendixB-SchemaReference.html](http://propel.phpdb.org/docs/user_guide/chapters/appendices/AppendixB-SchemaReference.html)).

The YAML format was designed to keep the schemas simple to read and write, but the trade-off is that the most complex schemas can't be described with a `schema.yml` file. On the other hand, the XML format allows for full schema description, whatever its complexity, and includes database vendor-specific settings, table inheritance, and so on.

Symfony actually understands schemas written in XML format. So if your schema is too complex for the YAML syntax, if you have an existing XML schema, or if you are already familiar with the Propel XML syntax, you don't have to switch to the symfony YAML syntax. Place your `schema.xml` in the project `config/` directory, build the model, and there you go.

>**SIDEBAR**
>Propel in symfony
>
>All the details given in this chapter are not specific to symfony, but rather to Propel. Propel is the preferred object/relational abstraction layer for symfony, but you can choose an alternative one. However, symfony works more seamlessly with Propel, for the following reasons:
>
>All the object data model classes and the `Criteria` class are autoloading classes. As soon as you use them, symfony will include the right files, and you don't need to manually add the file inclusion statements. In symfony, Propel doesn't need to be launched nor initialized. When an object uses Propel, the library initiates by itself. Some symfony helpers use Propel objects as parameters to achieve high-level tasks (such as pagination or filtering). Propel objects allow rapid prototyping and generation of a backend for your application (Chapter 14 provides more details). The schema is faster to write through the `schema.yml` file.
>
>And, as Propel is independent of the database used, so is symfony.

Don't Create the Model Twice
----------------------------

The trade-off of using an ORM is that you must define the data structure twice: once for the database, and once for the object model. Fortunately, symfony offers command-line tools to generate one based on the other, so you can avoid duplicate work.

### Building a SQL Database Structure Based on an Existing Schema

If you start your application by writing the `schema.yml` file, symfony can generate a SQL query that creates the tables directly from the YAML data model. To use the query, go to your root project directory and type this:

    > php symfony propel:build-sql

A `lib.model.schema.sql` file will be created in `myproject/data/sql/`. Note that the generated SQL code will be optimized for the database system defined in the `phptype` parameter of the `propel.ini` file.

You can use the schema.sql file directly to build the tables. For instance, in MySQL, type this:

    > mysqladmin -u root -p create blog
    > mysql -u root -p blog < data/sql/lib.model.schema.sql

The generated SQL is also helpful to rebuild the database in another environment, or to change to another DBMS. If the connection settings are properly defined in your `propel.ini`, you can even use the `php symfony propel:insert-sql` command to do this automatically.

>**TIP**
>The command line also offers a task to populate your database with data based on a text file. See Chapter 16 for more information about the `propel:data-load` task and the YAML fixture files.

### Generating a YAML Data Model from an Existing Database

Symfony can use the Creole database access layer to generate a `schema.yml` file from an existing database,