Code snippets for symfony 1.x

Navigation

Using SQL aggregate functions

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;
 
by Georg Sorst on 2007-12-18, tagged aggregate  count  criteria  manytomany  propel  sql 

Comments on this snippet

gravatar icon
#1 Ofir Picazo on 2008-01-29 at 06:44

I think it'd better to override the hydrate method in the subclass and create a new property in the object that matches the added AsColumn, rather than doing it everytime you need to add an AsColumn.

gravatar icon
#2 gaurang rajvir on 2008-02-26 at 02:32

Hello how can i add avtar????