I haven't found a way to use subqueries as alias with criteria in the book.

Here is a small piece of code that works very well:

the subquery is in an alias defined by Criteria::addAsColumn($alias, $expr)

it can be used to order the result, or in case you have to deal with foreign keys, etc.

$c = new Criteria();
 
$c->addAsColumn('brandname', '(SELECT brand.name FROM brand WHERE brand.id=brand_id)');
 
$c->addAscendingOrderByColumn($c->getColumnForAs('brandname'));
 
$this->products = ProductPeer::doSelect($c);
 

Product has a brand_id that references the id key of the brand table that contains the 'name' field which is used to order the result.

Patrice Blanchardie

I had some trouble finding information on how to use SQL aggregate functions like GROUP BY, COUNT and HAVING with Propel, so here is some info about that.

Suppose you have a system with a many-to-many relation between articles and authors, this example shows how to find out how many articles each author has worked on.

$c = new Criteria();
 
// optionally look only for certain authors whose IDs are in $results
$c->add(AuthorPeer::ID, $results, Criteria::IN);
// JOIN them with the article IDs
$c->addJoin(ArticleAuthorPeer::AUTHOR_ID, AuthorPeer::ID);
// list each author only once and count the number of articles they have worked on
$c->addGroupByColumn(AuthorPeer::ID);
$c->addAsColumn('numArticles', 'COUNT('.AuthorPeer::ID.')');
 
// optionally retrieve only those authors that have a certain number of articles (like 'numArticles=2' or 'numArticles>2')
// the first argument does not really matter since this is a custom criteria
// according to the SQL standard this cannot be done with a WHERE clause
$c->addHaving($c->getNewCriterion(AuthorPeer::ID, 'numArticles=2', Criteria::CUSTOM));
 
// order by the number of articles
$c->addDescendingOrderByColumn('numArticles');
 
// get a ResultSet and iterate over it
$rs = AuthorPeer::doSelectRS($c);
$counts = array();
$results = array();
while ($rs->next())
{
  $author = new Author();
  // hydrate the object and store how many columns it has
  $lastColumn = $author->hydrate($rs);
  $results[] = $author;
  // then retrieve the COUNT from the first column not belonging to the object
  $counts[] = $rs->getInt($lastColumn);
}
$this->results = $results;
$this->counts = $counts;
 

Let say you want to write a sub-select, and have a Propel object returned.

As an example, lets generate the following SQL:

SELECT
    orders.ID,
    orders.TYPE,
    orders.STATUS
FROM
    orders
WHERE
    orders.STATUS IN (
        SELECT
              STATUS.NAME
        FROM
              STATUS
        WHERE
             STATUS.ORDER_TYPE = 'purchase'
    )

Propel does not natively handle this sort of SQL. There are however two ways to create this SQL or to get this data set and have Propel objects returned.

Overview of the different methods

1. Use of Criteria::CUSTOM
   • PRO: Allows the use of an actual sub-select
   • CON: Code is not as readable
   • See http://www.symfony-project.com/snippets/snippet/53 for more information on Criteria::CUSTOM
2. Rewrite the SQL using JOINs
   • PRO: Very readable code
   • CON: Not using a sub-select
   • CON: You may be using a sub-select for SQL performance, so this may be slower

Way 1: Use of Criteria::CUSTOM

$c = new Criteria();
$subSelect = "orders.STATUS IN (
    SELECT
          status.NAME
    FROM
          status
    WHERE
         status.ORDER_TYPE = 'purchase'
    )";
$c->add(StatusPeer.STATUS, $subSelect, Criteria::CUSTOM);
$orders = StatusPeer::doSelect($c);

Way 2: Rewrite the SQL using JOINs

Example of the rewritten SQL:

SELECT
    orders.ID,
    orders.TYPE,
    orders.STATUS
FROM
    orders,
    STATUS
WHERE
    orders.STATUS = STATUS.NAME
    AND STATUS.ORDER_TYPE = 'purchase'

This can be written as follows in your action:

$c = new Criteria();
$c->addJoin (OrderPeer::STATUS, StatusPeer::NAME);
$c->add(StatusPeer.ORDER_TYPE, 'purchase');
$orders = StatusPeer::doSelect($c);