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/). 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
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
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
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 theblog_comment
table will be understood as a foreign key to theblog_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 calledcreated_at
are automatically set to thetimestamp
type. For all these columns, you don't need to specify any type. This is one of the reasons whyschema.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, totext
in MySQL), and so on. For text content over 256 characters, you need to use thelongvarchar
type, which has no size (but cannot exceed 65KB in MySQL). Note that thedate
andtimestamp
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 withbu_date
andbu_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
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
$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
// $articles is an array of objects of class Article $articles = ArticlePeer::retrieveByPks(array(123, 124, 125));
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
$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
$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 relatedArticle
object$comment->getArticleId()
: To get the ID of the relatedArticle
object$comment->setArticle($article)
: To define the relatedArticle
object$comment->setArticleId($id)
: To define the relatedArticle
object from an ID$article->getComments()
: To get the relatedComment
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
$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
// 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.
$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
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.
$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
$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
$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
$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 /cookbook/1_1/en/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:
- Get a database connection.
- Build a query string.
- Create a statement out of it.
- 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
$connection = Propel::getConnection(); $query = 'SELECT MAX(?) AS max FROM ?'; $statement = $connection->prepareStatement($query); $statement->setString(1, ArticlePeer::CREATED_AT); $statement->setString(2, ArticlePeer::TABLE_NAME); $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
$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:
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:
public function executeShowPopularArticlesForTag($request) { $tag = TagPeer::retrieveByName($request->getParameter('tag')); $this->forward404Unless($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 credentials, and the type of database. These connection settings can be configured by passing a data source name (DSN) to the configure:database
task:
> php symfony configure:database "mysql://login:passwd@localhost/blog"
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 by using the env
option:
> php symfony --env=prod configure:database "mysql://login:passwd@localhost/blog"
This configuration can also be overridden per application. 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:
> php symfony --app=frontend configure:database "mysql://login:passwd@localhost/blog"
For each environment, you can define many connections. Each connection refers to a schema being labeled with the same name. The default connection name used is propel
and it refers to the propel
schema in Listing 8-3. The name
option allows you to create another connection:
> php symfony --name=main configure:database "mysql://login:passwd@localhost/blog"
You can also enter these connection settings manually in the databases.yml
file located in the config/
directory. Listing 8-17 shows an example of such a file and Listing 8-18 shows the same example with the extended notation.
Listing 8-17 - Shorthand Database Connection Settings
all: propel: class: sfPropelDatabase param: dsn: mysql://login:passwd@localhost/blog
Listing 8-18 - 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 permitted values of the phptype
parameter are the ones of the database systems supported by Creole:
mysql
mssql
pgsql
sqlite
oracle
hostspec
, database
, username
, and password
are the usual database connection settings.
To override the configuration per application, you need to edit an application-specific file, such as apps/frontend/config/databases.yml
.
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
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
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
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
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
:
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.org/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 areboolean
,tinyint
,smallint
,integer
,bigint
,double
,float
,real
,decimal
,char
,varchar(size)
,longvarchar
,date
,time
,timestamp
,bu_date
,bu_timestamp
,blob
, andclob
.required
: Boolean. Set it totrue
if you want the column to be required.size
: The size or length of the field for types that support itscale
: Number of decimal places for use with decimal data type (size must also be specified)default
: Default value.primaryKey
: Boolean. Set it totrue
for primary keys.autoIncrement
: Boolean. Set it totrue
for columns of typeinteger
that need to take an auto-incremented value.sequence
: Sequence name for databases using sequences forautoIncrement
columns (for example, PostgreSQL and Oracle).index
: Boolean. Set it totrue
if you want a simple index or tounique
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 viaforeignTable
.onDelete
: Determines the action to trigger when a record in a related table is deleted. When set tosetnull
, the foreign key column is set tonull
. When set tocascade
, 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 aforeignTable
and aforeignReference
.isCulture
: Boolean. Set it totrue
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. For columns that require a size, because they are text columns, the size of the index is specified the same way as the length of the column using parentheses. 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(10), 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 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).
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, thanks to introspection (the capability of databases to determine the structure of the tables on which they are operating). This can be particularly useful when you do reverse-engineering, or if you prefer working on the database before working on the object model.
In order to do this, you need to make sure that the project propel.ini
file points to the correct database and contains all connection settings, and then call the propel:build-schema
command:
> php symfony propel:build-schema
A brand-new schema.yml
file built from your database structure is generated in the config/
directory. You can build your model based on this schema.
The schema-generation command is quite powerful and can add a lot of database-dependent information to your schema. As the YAML format doesn't handle this kind of vendor information, you need to generate an XML schema to take advantage of it. You can do this simply by adding an xml
argument to the build-schema
task:
> php symfony propel:build-schema --xml
Instead of generating a schema.yml
file, this will create a schema.xml
file fully compatible with Propel, containing all the vendor information. But be aware that generated XML schemas tend to be quite verbose and difficult to read.
sidebar
The propel.ini
Configuration
The propel:build-sql
and propel:build-schema
tasks don't use the connection settings defined in the databases.yml
file. Rather, these tasks use the connection settings in another file, called propel.ini
and stored in the project config/
directory:
propel.database.createUrl = mysql://login:passwd@localhost propel.database.url = mysql://login:passwd@localhost/blog
This file contains other settings used to configure the Propel generator to make generated model classes compatible with symfony. Most settings are internal and of no interest to the user, apart from a few:
// Base classes are autoloaded in symfony // Set this to true to use include_once statements instead // (Small negative impact on performance) propel.builder.addIncludes = false // Generated classes are not commented by default // Set this to true to add comments to Base classes // (Small negative impact on performance) propel.builder.addComments = false // Behaviors are not handled by default // Set this to true to be able to handle them propel.builder.AddBehaviors = false
After you make a modification to the propel.ini
settings, don't forget to rebuild the model so the changes will take effect.
Summary
Symfony uses Propel as the ORM and Creole as the database abstraction layer. It means that you must first describe the relational schema of your database in YAML before generating the object model classes. Then, at runtime, use the methods of the object and peer classes to retrieve information about a record or a set of records. You can override them and extend the model easily by adding methods to the custom classes. The connection settings are defined in a databases.yml
file, which can support more than one connection. And the command line contains special tasks to avoid duplicate structure definition.
The model layer is the most complex of the symfony framework. One reason for this complexity is that data manipulation is an intricate matter. The related security issues are crucial for a website and should not be ignored. Another reason is that symfony is more suited for middle- to large-scale applications in an enterprise context. In such applications, the automations provided by the symfony model really represent a gain of time, worth the investment in learning its internals.
So don't hesitate to spend some time testing the model objects and methods to fully understand them. The solidity and scalability of your applications will be a great reward.
This work is licensed under the GFDL license.