Code snippets for symfony 1.x

Navigation

Snippets by user Georg Sorst

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 
(2 comments)

Conditional object actions for the admin generator

The list view of the admin generator currently always displays all defined object actions for each object. There is no way to display an object action for an object only if some condition on this object is met.

In order to extend the admin generator with this functionality only a small enhancement is required. You can either apply this change per module or create a new admin generator theme as described in the Symfony book.

The templates/_list_td_actions.php has to be extended to look roughly like this, depending on whether you already have your own modifications in there:

<?php if ($this->getParameterValue('list.object_actions')): ?>
<td>
<ul class="sf_admin_td_actions">
<?php foreach ($this->getParameterValue('list.object_actions') as $actionName => $params): ?>
  <?php if ( isset( $params['condition'] ) ): ?>
    [?php if ( <?php echo ( isset( $params['condition']['invert'] ) && $params['condition']['invert'] ? '!' : '') . '$' . $this->getSingularName(  ) . '->' . $params['condition']['function'] ?>( <?php echo ( isset( $params['condition']['params'] ) ? $params['condition']['params'] : '' ) ?> ) ):  ?]
  <?php endif; ?>
      <?php echo $this->addCredentialCondition($this->getLinkToAction($actionName, $params, true), $params) ?>
  <?php if ( isset( $params['condition'] ) ): ?>
    [?php endif; ?]
  <?php endif; ?>
<?php endforeach; ?>
</ul>
</td>
<?php endif; ?>
 

With this enhancement you can now use conditions for your actions in the generator.yml. The syntax should be pretty self-explanatory. An example would look like this:

object_actions:
  subscribe:    { name: Notify when changed, action: subscribe, icon: pencil_add.png }
    condition:
      function: isUserSubscribed
      params:   "$sf_user, 'test'"
      invert:   true
 

As you can see each object action now also takes a condition parameter, which again takes a number of parameter.

Enjoy!

by Georg Sorst on 2007-12-07, tagged actions  admin  condition  conditional  generator  object 
(10 comments)