8.4. 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:

8.4.1. 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();

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!',
));

8.4.2. 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

$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).

8.4.3. 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().

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();
}

8.4.4. 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.

8.4.5. 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;

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.

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

Table 8-1 - SQL and Criteria Object Syntax

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.

8.4.6. 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

$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.

8.4.7. 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');

public function executeShowPopularArticlesForTag()
{
    $tag = TagPeer::retrieveByName($this->getRequestParameter('tag'));
    $this->foward404Unless($tag);
    $this->articles = $tag->getPopularArticles(10);
}