Retrieving Records with Criteria

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;

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.

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.

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.

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/1_0/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:

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

Retrieving Records with Criteria

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.