Relating to the post http://www.symfony-project.org/forum/index.php/m/32510/

I've created tools to generate SQL.

PS. Done with haste and for postgresSQL.

Can be edited to take in array of objects and form a whole SQL but I don't want to keep a large array of objects.

CREATED_AT, UPDATED_AT forced as NOW()

  /**
  * To generate sql values for insert statement
  * 
  * @param  $object           object      the object with relavant data
  * 
  * @return $valueSql         string     the object values string
  */
  public static function getObjectInsertValue($object)
  {
    if(!$object->isNew())
    {
        throw new Exception('Toolkit::getObjectInsertValue($object) Only usable with new object.');
      return '';
    }
 
    $className = get_class($object);
    $classPeer = get_class($object->getPeer());
    $tableName = "";
    eval('$tableName = '.$classPeer.'::TABLE_NAME;');
    //For postgres insert (postgres need define Primary Key)
    $nextId = 'nextval(\''.$tableName.'_seq\')';
 
    eval('$objectKeys = '.$classPeer.'::getFieldNames(BasePeer::TYPE_PHPNAME);');
    eval('$dataKeys = '.$classPeer.'::getFieldNames(BasePeer::TYPE_FIELDNAME);');
    $phpNameMap = array_combine($dataKeys, $objectKeys);
 
    //For checking primary key
    $object->setPrimaryKey(1);
    $object->resetModified();
    $object->setPrimaryKey(2);
 
    $valueArray = array();
    foreach($phpNameMap as $dataKey=>$objectKey)
    {
      $isPrimaryKey = false;
      $columnName = "";
      eval('$columnName = '.$classPeer.'::'.$dataKey.';');
      if($object->isColumnModified($columnName))
      {
        $isPrimaryKey = true;
      }
      if(!$isPrimaryKey)
      {
        if(strtoupper($dataKey)=='CREATED_AT' || strtoupper($dataKey)=='UPDATED_AT')
        {
          $valueArray[] = 'NOW()';
        }
        else
        {
          if(is_null($object->getByName($objectKey)))
          {
            $valueArray[] = 'NULL';
          }
          else
          {
            $data = $object->getByName($objectKey);
            $wrapper = "";
            if(is_string($data))
            {
                $wrapper = "'";
              $data = pg_escape_string($data);
            }
            $valueArray[] = $wrapper.$data.$wrapper;
          }
        }
      }
      else
      {
        //For postgres insert (postgres need define Primary Key)
        eval('$valueArray[] = "'.$nextId.'";');
      }
    }
 
    $valueStr = '('.implode(", ", $valueArray).')';
    return $valueStr;
  }
 
  /**
  * To generate sql insert into for insert statement
  * 
  * @param  $classPeer        string    the object class peer string
  * 
  * @return $intoSql          string    the object insert into string
  */
  public static function getObjectInsertInto($classPeer)
  {
    $classObject = substr($classPeer, 0, -strlen('Peer'));
    $object = null;
    eval('$object = new '.$classObject.'();');
    //For checking primary key
    $object->resetModified();
    $object->setPrimaryKey(1);
 
    eval('$objectKeys = '.$classPeer.'::getFieldNames(BasePeer::TYPE_PHPNAME);');
    eval('$dataKeys = '.$classPeer.'::getFieldNames(BasePeer::TYPE_FIELDNAME);');
    $phpNameMap = array_combine($dataKeys, $objectKeys);
 
    $intoArray = array();
    foreach($phpNameMap as $dataKey=>$objectKey)
    {
      $isPrimaryKey = false;
      $columnName = "";
      eval('$columnName = '.$classPeer.'::'.$dataKey.';');
      if($object->isColumnModified($columnName))
      {
        $isPrimaryKey = true;
      }
 
      //For postgres insert (postgres need define Primary Key)        
      //if(!$isPrimaryKey)
      {
        $intoArray[] = $dataKey;
      }
    }
 
    $intoStr = '('.implode(", ", $intoArray).')';
    return $intoStr;
  }
 

How to use.

$inserts = array();
while($creatingObjects)
{
  $object = new ObjectClass();
  $object->setTitle("title");
  $object->setSummary("summary");
  $inserts[] = Toolkit::getObjectInsertValue($object);
}
 
$sql = sprintf('INSERT INTO %s %s', 
    ObjectPeer::TABLE_NAME, Toolkit::getObjectInsertInto('ObjectPeer')) . 
    ' VALUES ' . implode(', ', $inserts);
 
$con = Propel::getConnection();
$stmt = $con->createStatement();
$rs = $stmt->executeQuery($sql, ResultSet::FETCHMODE_NUM);
 

I made a small more general modification to the edit_in_place update action, you can use that in any action

class myTools {
 
 
  /**
   * performs update on any single column for ajax actions
   *
   * @param     string  $peer
   * @param     integer $id
   * @param     string  $field
   * @return    object
   */
  public static function updateField($peer, $id, $field, $value) {
    if (!class_exists($peer)) {
        throw new InvalidArgumentException($peer.' does not exist');
    }
 
    $method = new ReflectionMethod($peer, 'retrieveByPk');
    $object = $method->invoke(NULL, $id);
 
    $object->setByName($field, $value, BasePeer::TYPE_FIELDNAME);
    $object->save();
    return $object;
  }
 

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

This was asked on the forum, so I thought I would place it here too.

Problem

Suppose you have a model that resembles the following:

  range:
    id:                                   
    start_date: {type: date}
    end_date: {type: date}

And you want to know the following: are there any records where either $date1 or $date2 is between start_date and end_date?

Consider that $date2 could be some fixed $offset from $date1, so that the question becomes: are there any records which partially cover the $offset period following $date1?

Using Criterion Objects

Since the BETWEEN construct is unavailable through Propel (at least not as an object or constant), we have to use the knowledge that:

if
  a >= x
and
  a <= y
then
  x <= a <= y

Which gives us:

$c = new Criteria();
 
$date1 = '2007-08-20';
$date2 = '2008-08-20';
 
// test against date1
$date1Criterion = $c->getNewCriterion(RangePeer::START_DATE, $date1, Criteria::LESS_EQUAL);
 
// test against date2
$date2Criterion = $c->getNewCriterion(RangePeer::START_DATE, $date2, Criteria::LESS_EQUAL);
 
// conjunction
$date1Criterion->addAnd($c->getNewCriterion(RangePeer::END_DATE, $date1, Criteria::GREATER_EQUAL));
$date2Criterion->addAnd($c->getNewCriterion(RangePeer::END_DATE, $date2, Criteria::GREATER_EQUAL));
 
// disjunction
$date1Criterion->addOr($date2Criterion);
$c->add($date1Criterion);

Using Criteria::CUSTOM

Or if you absolutely must have your BETWEEN, try this:

$c = new Criteria();
 
$date1 = '2007-08-20';
$date2 = '2008-08-20';
 
$c->add(RangePeer::START_DATE, "'{$date1}' between ".RangePeer::START_DATE." and ".RangePeer::END_DATE, Criteria::CUSTOM);
$c->addOr(RangePeer::START_DATE, "'{$date2}' between ".RangePeer::START_DATE." and ".RangePeer::END_DATE, Criteria::CUSTOM);

Note that the first arguments to Criteria::add() and Criteria::addOr() can be any column, but they must be the same column.

Note also that when providing the argument, {$dateN} appears between single quotes. It must appear quoted (and escaped) in order for this query to return correct (or any) results.

Propel can create Models with autoset created_at and Updated_at field, but dont include autoset for Created_by and Updated_by field, because this fields are most aplication dependent. We can set Propel to build models with autosetting Created_by and Updated_by fields (and any other fields) extending SfObjectBuilder. In the forum message 7535 Tamcy give us a good example. Here I post a SFObjectBuilder extension class (in this example I check if using SfGuardPlugin for user Class).

<?php
/* I recomend put this class out Symfony Folder.
*  If you put this class in some project folder
* you can use include_once 'symfony/addon/propel/builder/SfObjectBuilder.php'
* @author     Boris Duin 
 */
require_once 'sfObjectBuilder.php';
class SfObjectAdvBuilder extends SfObjectBuilder
{
  */Extend Method addSave
  protected function addSave(&$script)
  {
    $tmp = '';
    parent::addSave($tmp);
 
    $date_script = '';
 
    $user_updated = false;
    $user_created = false;
    foreach ($this->getTable()->getColumns() as $col)
    {
      $clo = strtolower($col->getName());
 
      if (!$user_created && $clo == 'created_by')
      {
        $user_created = true;
        $date_script .= "
    if (\$this->isNew() && !\$this->isColumnModified('created_by'))
    {
      \$user = sfContext::getInstance()->getUser();
      if (\$user instanceof sfGuardSecurityUser)
        \$this->setCreatedby(\$user->getUsername())
    }
";
      }
      else if (!$user_updated && $clo == 'updated_by')
      {
       $user_updated = true;
        $date_script .= "
    if (\$this->isModified() && !\$this->isColumnModified('updated_by'))
    {
      \$user = sfContext::getInstance()->getUser();
      if (\$user instanceof sfGuardSecurityUser)
        \$this->setUpdatedby(\$user->getUsername())
    }
";
      }
    }
 
    $tmp = preg_replace('/{/', '{'.$date_script, $tmp, 1);
    $script .= $tmp;
  }
}

Note: I write this class for synfony 0.9.x versión. Yesterday I was refactoring this class for 1.0.x version, but I leave this in my house, :(. To set propel to use this class you must edit config/propel.ini. Replace this line

propel.builder.object.class = symfony.addon.propel.builder.SfObjectBuilder

for

propel.builder.object.class = route.to.my.class.SfObjectAdvBuilder

Example: If you put this class in your Project/Lib folder you can set the line:

propel.builder.object.class = ${propel.output.dir}.lib.SfObjectAdvBuilder

Now, when you run symfony propel-build-model, propel will use this class and add autoset code for created_by and Updated_by field. Here an example of models class generated by propel (symfony 0.9.x):

public function save($con = null)
    {
    if ($this->isNew() && !$this->isColumnModified('created_by'))
    {
      $user = sfContext::getInstance()->getUser();
      if ($user instanceof sfGuardSecurityUser)
        $this->setCreatedby($user->getUsername());
    }
 
    if ($this->isNew() && !$this->isColumnModified('created_at'))
    {
      $this->setCreatedAt(time());
    }
 
    if ($this->isModified() && !$this->isColumnModified('updated_by'))
    {
      $user = sfContext::getInstance()->getUser();
      if ($user instanceof sfGuardSecurityUser)
        $this->setUpdatedby($user->getUsername());
    }
 
    if ($this->isModified() && !$this->isColumnModified('updated_at'))
    {
      $this->setUpdatedAt(time());
    }

I will investigate how to create a Symfony plugin using maybe Behavior like other Symfony-propel plugins

The problem is simple. I have a booking system for appartments (or whatever else). Reservations are stored in a Reservation table, and have a begin_date and an end_date column.

I want to find the existing reservations for an appartment between two dates.

The data I have is $appartment_id, $begin_date and $end_date. I want reservations starting or ending between the two dates, or starting before the $begin_date and ending after the $end_date. That's how it would be translated into a SQL WHERE:

reservation.APPARTMENT_ID = $appartment_id 
AND 
(((reservation.START_DATE > $begin_date AND reservation.START_DATE < $end_date) 
  OR 
  (reservation.END_DATE > $begin_date AND reservation.END_DATE < $end_date)) 
  OR 
  (reservation.END_DATE > $end_date AND reservation.START_DATE < $begin_date))

Of course, I'd prefer to use Propel for that. Is it tricky? Not that much.

class Appartment extends BaseAppartment {
 
  public function findReservations($begin_date, $end_date)
  {
    $c = new Criteria();
    $c->add(ReservationPeer::APPARTMENT_ID, $this->getId());
 
    // Find reservations beginning between the search period
    $criterion1 = $c->getNewCriterion(
      ReservationPeer::START_DATE, $begin_date, Criteria::GREATER_THAN
    )->addAnd($c->getNewCriterion(
      ReservationPeer::START_DATE, $end_date, Criteria::LESS_THAN
    ));
 
    // Find reservations ending between the search period
    $criterion2 = $c->getNewCriterion(
      ReservationPeer::END_DATE, $begin_date, Criteria::GREATER_THAN
    )->addAnd($c->getNewCriterion(
      ReservationPeer::END_DATE, $end_date, Criteria::LESS_THAN
    ));
 
    // Find reservations beginning before the search period and ending after
    $criterion3 = $c->getNewCriterion(
      ReservationPeer::END_DATE, $end_date, Criteria::GREATER_THAN
    )->addAnd($c->getNewCriterion(
      ReservationPeer::START_DATE, $begin_date, Criteria::LESS_THAN
    ));
 
    // Combine all that with a OR
    $c->add($criterion1->addOr($criterion2)->addOr($criterion3));
 
    return = ReservationPeer::doSelect($c);
  }
}

When you need to update several records in a row, you don't have to loop over the result of a doSelect() call and do a save() for each object (which would make way too many queries).

Instead, you can use the BasePeer method doUpdate() as follows:

BasePeer::doUpdate($select_criteria, $update_criteria, $connection);

For instance:

$con = Propel::getConnection();
 
// select from...
$c1 = new Criteria();
$c1->add(CommentPeer::POST_ID, $post_id);
 
// update set
$c2 = new Criteria();
$c2->add(CommentPeer::RATING, 5);
 
BasePeer::doUpdate($c1, $c2, $con);

If course, if you are in a Peer class, you will need to use the self object:

$con = Propel::getConnection();
 
// select from...
$c1 = new Criteria();
$c1->add(self::POST_ID, $post_id);
 
// update set
$c2 = new Criteria();
$c2->add(self::RATING, 5);
 
BasePeer::doUpdate($c1, $c2, $con);

This is helper is mostly inspired by Pagination navigation helper, but there are some differences:

• only list (ul/li)
• emphasize the actual page we are browsing
• stylish is done with pure css
• all parameters of the original request are kept in uri when paginating (article/list/category_id/5/page/3 instead of article/list/page/3)
• we only need 1 parameter (current uri is guessed)

PaginationHelper.php

function pagination($pager)
{
    $uri = sfRouting :: getInstance()->getCurrentInternalUri();
    $html = '';
 
    if ($pager->haveToPaginate())
    {
        $uri .= strstr($uri, '?') ? '&page=' : '?page=';
 
        if ($pager->getPage() != 1)
        {
            $html .= '<li>' . link_to('first', $uri . '1') . '</li>';
            $html .= '<li>' . link_to('previous', $uri . $pager->getPreviousPage()) . '</li>';
        }
 
        foreach ($pager->getLinks() as $page)
        {
            if ($page == $pager->getPage())
                $html .= '<li><strong>' . link_to($page, $uri . $page) . '</strong></li>';
            else
                $html .= '<li>' . link_to($page, $uri . $page) . '</li>';
        }
 
        if ($pager->getPage() != $pager->getLastPage())
        {
            $html .= '<li>' . link_to('next', $uri . $pager->getNextPage()) . '</li>';
            $html .= '<li>' . link_to('last', $uri . $pager->getLastPage()) . '</li>';
        }
 
        $html = '<ul class="pagination">' . $html . '</ul>';
    }
 
    return $html;
}

Minimal CSS

ul.pagination li {
    display: inline;
    list-style-type: none;
    padding-right: 1em;
}

In your template

<?php echo use_helper('Pagination') ?>
<?php echo pagination($pager) ?>

Since it can be annoying to have Propel ignore collation of tables in MySQL >= 4.1, you must force Propel to use UTF-8 collation by running the SET NAMES UTF8 SQL query.

In order to do that, you specify a filter that executes at every request. Specify the following code in filters.yml:

myUtf8ConnectionFilter:
  class: myUtf8ConnectionFilter
  activate: on

Create a new file called myUtf8ConnectionFilter.class.php in your application's lib folder and insert the following code:

<?php
class myUtf8ConnectionFilter extends sfFilter
{
  public function execute($filterChain)
  {
    $con = Propel::getConnection();
    if ($con){
       $con->executeQuery("set names utf8");
    } else {
      throw new Exception($e);
    }
    $filterChain->execute();
  }
}
?>

The code was copied from this website - I am not asserting any authorship.

Imagine that you have a table with the following columns:

MyTable
-------
id
col1
col2

If you want to retrieve the records having col1 greater than col2, the following doesn't work:

$c = new Criteria();
$c->add(MyTablePeer::COL1, MyTablePeer::COL2,  Criteria::GREATER_THAN);

Instead, you need to add a custom condition to your Criteria:

$c = new Criteria();
$c->add(MyTablePeer::COL1, MyTablePeer::COL1.'>='.MyTablePeer::COL2, Criteria::CUSTOM);

In a template displaying a paginated list, you need to show the pager navigation. Create a PaginationHelper.php in lib/helper:

<?php
 
function pager_navigation($pager, $uri)
{
  $navigation = '';
 
  if ($pager->haveToPaginate())
  {  
    $uri .= (preg_match('/\?/', $uri) ? '&' : '?').'page=';
 
    // First and previous page
    if ($pager->getPage() != 1)
    {
      $navigation .= link_to(image_tag('/sf/images/sf_admin/first.png', 'align=absmiddle'), $uri.'1');
      $navigation .= link_to(image_tag('/sf/images/sf_admin/previous.png', 'align=absmiddle'), $uri.$pager->getPreviousPage()).' ';
    }
 
    // Pages one by one
    $links = array();
    foreach ($pager->getLinks() as $page)
    {
      $links[] = link_to_unless($page == $pager->getPage(), $page, $uri.$page);
    }
    $navigation .= join('  ', $links);
 
    // Next and last page
    if ($pager->getPage() != $pager->getLastPage())
    {
      $navigation .= ' '.link_to(image_tag('/sf/images/sf_admin/next.png', 'align=absmiddle'), $uri.$pager->getNextPage());
      $navigation .= link_to(image_tag('/sf/images/sf_admin/last.png', 'align=absmiddle'), $uri.$pager->getLastPage());
    }
 
  }
 
  return $navigation;
}

In your templates, display the pagination links like that:

<?php echo use_helper('Pagination') ?>
<?php echo pager_navigation($mypager, '@myrule') ?>

if you using multiple database from Propel, It can be solve by writing two or more schema files.

PROJECT_DIR/config/databases.yml

all:
  database1:
    class:   sfPropelDatabase
    param:
      dsn:   pgsql://foo:bar@hostname/database1

  database2:
    class:   sfPropelDatabase
    param:
      dsn:   mysql://foo:bar@hostname/database2

PROJECT_DIR/config/database1.schema.xml

<?xml version="1.0" encoding="UTF-8"?>
<database name="database1" defaultIdMethod="native" noxsd="true">
　　<table name="foo" phpName="Foo">
    ....
　　</table>
</database>

PROJECT_DIR/config/database2.schema.xml

<?xml version="1.0" encoding="UTF-8"?>
<database name="database2" defaultIdMethod="native" noxsd="true">
　　<table name="bar" phpName="Bar">
    ....
　　</table>
</database>

Last, build model command.

$ symfony propel-build-model

Build complete.

Example, getting multiple databases connection handler.

$database1_connection_handler = Propel::getConnection(FooPeer::DATABASE_NAME);
$database2_connection_handler = Propel::getConnection(BarPeer::DATABASE_NAME);

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;
 

I needed to have many subclasses inherited from a main class.

To do that with propel, you need to have all your classes in one big table. I wanted to have separate tables.

Then I discovered the propel behaviors and it does that very well !

Let's see an example with a master class "element" and a subclass "subElement" :

• The tables of the subclasses just need to have an "element_id" foreign key column, to be linked with the "element" master class table :

  # schema.yml example
   
  propel:
  # master class
    element:
      _attributes: { phpName: Element }
      id:
      name:       varchar(255)
      content:     longvarchar
      created_at:
  # subclass with only the subclass properties and element_id foreign key column
    sub_element:
      _attributes: { phpName: SubElement }
      element_id:
      author:      varchar(255)
   

• Activate the behaviors in "project/config/propel.ini" (before building the model!):

  propel.builder.AddBehaviors = true
   

• Don't forget to rebuild the model, anytime you modify it :

  >symfony propel-build-model
   

• Create your behavior in "project/config/config.php" :

  // get propel behavior lib
  require_once($sf_symfony_lib_dir.'/addon/propel/sfPropelBehavior.class.php');
   
  // declare all the methods from of the master class "element", that you'll need in the subclasses
  sfPropelBehavior::registerMethods('ElementBehavior',
    array(
      array('ElementBehavior', 'setName'),
      array('ElementBehavior', 'getName'),
      array('ElementBehavior', 'setContent'),
      array('ElementBehavior', 'getContent'),
      array('ElementBehavior', 'setCreated'),
      array('ElementBehavior', 'getCreatedAt'),
    )
  );
   
  // Modify save() and delete() master class methods
  // SubElement->save() need to run Element->save()..., and so for SubElement->delete()
  sfPropelBehavior::registerHooks('ElementBehavior',
    array(
      ':save:pre' => array('ElementBehavior', 'preSave'),
      ':delete:pre' => array('ElementBehavior', 'preDelete'),
    )
  );
   

• Declare all these behavior methods in the master class model (here in "project/lib/model/Element.php") :

  // declare this class after the element class
  class ElementBehavior
  {       
   
       /*  Properties Setters */
      public function setName(BaseObject $object, $value) {
              if (!$object->getElement()) $object->setElement(new Element());
              $object->getElement()->setName($value);
      }
      public function setContent(BaseObject $object, $value) {
              if (!$object->getElement()) $object->setElement(new Element());
              $object->getElement()->setContent(value);
      }
      public function setCreatedAt(BaseObject $object, $value) {
              if (!$object->getElement()) $object->setElement(new Element());
              $object->getElement()->setCreatedAt($value);
      }
     /*  Properties Getters */
      public function getId(BaseObject $object) {
              if (!$object->getElement()) $object->setElement(new Element());
              return $object->getElement()->getId();
      }
      public function getName(BaseObject $object) {
              if (!$object->getElement()) $object->setElement(new Element());
              return $object->getElement()->getName();
      }
      public function getContent(BaseObject $object) {
              if (!$object->getElement()) $object->setElement(new Element());
              return $object->getElement()->getContent();
      }
      public function getCreatedAt(BaseObject $object) {
              if (!$object->getElement()) $object->setElement(new Element());
              return $object->getElement()->getCreatedAt();
      }  
   
        /* Element save method called just before SubElement save */
      public function preSave(BaseObject $object) {
              // if element not null...
              if ($object->getElement()) {
                      // save element in his table
                      $object->getElement()->save();
                      // if new element, capture id
                      if ($object->isNew()) $object->setElementId($object->getElement()->getId());
              }
      }
       /* Element delete method called just before SubElement delete */
      public function preDelete(BaseObject $object) {
              // if element not null and not new...
              if ($object->getElement() && !$object->getElement()->isNew()) {
                      // delete element in his table
                      $object->getElement()->delete();
              }
      }
  }       
   

• add the "ElemenBehavior" to all your subclasses (here in" project/lib/model/SubElement.php") :

     sfPropelBehavior::add('SubElement', array('ElementBehavior'));
   

• That's all ! You can test it with a code like this :

  // create subElement
  $test = new SubElement();
  // test on element methods
  $test->setName('test');
  $test->setContent('this is the big test !');
   
  // test also SubElement methods
  $test->setAuthor('mr john smith');
  // save in element table AND in sub_element table ! 
  $test->save();
   
  // capture subElement with id 1
  $test = SubElementPeer :: retrieveByPk(1);
  // delete element and subElement in the two tables
  $test->delete();
   
  // mix criteria for both class
  $c = new Criteria();
  $c->add(ElementPeer :: ID, 1);
  $c->add(SubElementPeer :: AUTHOR, 'mr john smith');
  // get a list of the subElements
  $subElements = SubElementPeer :: doSelectJoinElement(new Criteria());
   

• To do a "load_data", your fixtures file need to look like this:

  Element:
    element1:
      name: test
      content: this is the big test !
   
  SubElement:
    subElement1:
      element_id: element1
      author: mr john smith
   

• each time you add a column or a method to the element model, you need to declare it in the behavior to be handled in the subclasses.

  It's a way to go, may be there is a simplier way, but this works well for me...

Setting the datasource and adapter for the connection "$name" at runtime. Calling Propel::initialize() will load the necessary database adapters and clear any open connections.

<?php
$dsn = Creole::parseDSN('mysql://localhost/symfony');
 
$c = Propel::getConfiguration();
$c['datasources'][$name]['connection'] = $dsn;
$c['datasources'][$name]['adapter']    = $dsn['phptype'];
Propel::setConfiguration($c);
Propel::initialize();
 

$sql = 'select * from ( select * from book order by weight desc limit 5 ) as T order by popularity';
$connection = Propel::getConnection();
$statement = $connection->createStatement(  );
$result = $statement->executeQuery( $sql , ResultSet::FETCHMODE_NUM);
return BookPeer::populateObjects( $result );

Sometimes you need mutual exclusion in an action. Imagine you are decreassing a stock number:

stock = 10;
user a ->  $itemX->getstock()   [10]
user b ->  $itemX->getstock()   [10]
user a -> $itemX->setStock(10-buyedItems)  [10-10=0]
user b -> $itemX->setStock(10-buyedItems)  [10-6=4]
user a-> $itemX->save()
user b -> $itemX->save()

The result will be an stock of 4, when it should be impossible to process the second transaction because no more stock is available....

The solution is to use Mutual exvlusion, here is a class that implements semaphores

<?php
 
    class Mutex
    {
        private $id;
        private $sem_id;
        private $is_acquired = false;
        private $is_windows = false;
        private $filename = '';
        private $filepointer;
 
        function __construct()
        {
            if(substr(PHP_OS, 0, 3) == 'WIN')
                $this->is_windows = true;
        }
 
        public function init($id, $filename = '')
        {
            $this->id = $id;
 
            if($this->is_windows)
            {
                if(empty($filename)){
                    throw new sfException(sprintf('nxMutex: no filename specified'));
                    return false;
                }
                else
                    $this->filename = $filename;
            }
            else
            {
                if(!($this->sem_id = sem_get($this->id, 1))){
                    throw new sfException(sprintf('nxMutex: Error getting semaphore'));
                    return false;
                }
            }
 
            return true;
        }
 
        public function acquire()
        {
            if($this->is_windows)
            {
                if(($this->filepointer = @fopen($this->filename, "w+")) == false)
                {
                    throw new sfException(sprintf('nxMutex: error opening mutex file'));
                    return false;
                }
 
                if(flock($this->filepointer, LOCK_EX) == false)
                {
                    throw new sfException(sprintf('nxMutex: error locking mutex file'));
                    return false;
                }
            }
            else
            {
                if (! sem_acquire($this->sem_id)){
                    throw new sfException(sprintf('nxMutex: error acquiring semaphore'));
                    return false;
                }
            }
 
            $this->is_acquired = true;
            return true;
        }
 
        public function release()
        {
            if(!$this->is_acquired)
                return true;
 
            if($this->is_windows)
            {
                if(flock($this->filepointer, LOCK_UN) == false)
                {
                    throw new sfException(sprintf('nxMutex: error unlocking mutex file'));
                    return false;
                }
 
                fclose($this->filepointer);
            }
            else
            {
                if (! sem_release($this->sem_id)){
                    throw new sfException(sprintf('nxMutex: error releasing semaphore'));
                    return false;
                }
            }
 
            $this->is_acquired = false;
            return true;
        }
 
        public function getId()
        {
            return $this->sem_id;
        }
    }
 
?>

the usage would be something like

$mutex=new Mutex();
$mutex->init(1,'mutexStock.txt');
$mutex->acquire();
$object->setStockobject->getStock()-$qtty);
$this->save();
$mutex->release();

bare in mind that the mutual exclusion will be based on the name passed on init function, so for mutual exclusion in diferent object you should concat the id of the object on the id of the muttex to avoid performance loose.

<?php
// Assuming include_path := /usr/local/lib/php/symfony or similar
require_once 'addon/propel/builder/SfPeerBuilder.php';
 
class SfPeerAdvBuilder extends SfPeerBuilder
{
    // Must override because we are adding our own functions
    protected function addClassBody(&$script)
    {
        parent::addClassBody($script);
 
            $this->doGetBy($script);
            $this->doGetOneBy($script);
            $this->doGetPager($script);   
 
    }
 
 
    protected function doGetPager(&$script)
    {
 
        $temp = '
        public static function getPager($page, $max_items_per_page = null, $criteria = null)
        {                    
 
       if($criteria == null)
        $c = new Criteria();
 
            // Max items per page              
            if($max_items_per_page === null)
            {
                if(sfConfig::get(\'app_pager_default_max\'))
                    $max_items_per_page =  sfConfig::get(\'app_pager_default_max\');
                else
                    $max_items_per_page = 10;
            }  
 
            // Pager  
            $pager = new sfPropelPager(\''.$this->getObjectClassname().'\', $max_items_per_page);
            $pager->setCriteria($c);
            $pager->setPage($page);
            $pager->init();
 
            return $pager;  
         }      
        ';
 
         $script .= $temp;   
    }
 
    protected function doGetBy(&$script)
    {
 
        $temp = '
        /**
        * return any number of objects referenced by $searchFeild == $value
        */
        public static function getBy($searchField, $value)
        {    
            $searchField = strtolower($searchField);
            $basefieldNames = self::getFieldNames(BasePeer::TYPE_PHPNAME);
            $baseColumnNames = self::getFieldNames(BasePeer::TYPE_COLNAME);
 
            foreach($basefieldNames as $key => $baseField)
            {
                if($searchField == strtolower($baseField))
                {
                    $c = new Criteria();
                    $test = $baseColumnNames[$key];
                    $c->add($baseColumnNames[$key], $value);
                    return self::doSelect($c);                
                }
            }
 
            throw new sfException("Field name does not exist.");
        }
        ';
 
        $script .= $temp;
    }
 
     protected function doGetOneBy(&$script)
    {
 
        $temp = '
        /**
        * return one object referenced by $searchFeild == $value
        */
        public static function getOneBy($searchField, $value)
        {    
            $searchField = strtolower($searchField);
            $basefieldNames = self::getFieldNames(BasePeer::TYPE_PHPNAME);
            $baseColumnNames = self::getFieldNames(BasePeer::TYPE_COLNAME);
 
            foreach($basefieldNames as $key => $baseField)
            {
                if($searchField == strtolower($baseField))
                {
                    $c = new Criteria();
                    $c->add($baseColumnNames[$key], $value);
                    return self::doSelectOne($c);                
                }
            }
 
            throw new sfException("Field name does not exist.");
        } 
        ';
 
        $script .= $temp;
    }
}
?>

One thing I've often had to do is pull up a set of records using a non primary key field.

I thought it would be useful to include these functions in every Peer object.

Usage

GetBy - Retrive all records of a field with a certain value

GetOneBy - Retrive one object

GetPager - Retrive a pager of objects

GetBy and GetOneBy take as their first parameter the CamelCase PHP name for the field, the next parameter is the value to select by.

Example:

SomethingPeer::getBy('SomeField', $value);

All the paramters in getPager are optional.

$page defaults to one $max_items_on_page defaults to 10 or the value in app_pager_default_max $criteria defaults to a blank critera object

Example: SomethingPeer::getPager($max_items_on_page, $critera);

I was able to do this based on Tracey's example in: http://www.symfony-project.com/forum/index.php/m/7535/

Install

Modify your propel.ini

Replace propel.builder.peer.class = addon.propel.builder.SfPeerBuilder with propel.builder.peer.class = ${propel.output.dir}.lib.propel.SfPeerAdvBuilder

The last part, lib.propel.SfPeerAdvBuilder, is a reference to a class in a sub-directory in the current project /lib/propel/SfPeerAdvBuilder.php

Now re-build your model and the functions will be available in every Peer class.

if you won´t to add a foreignkey to sf_guard_user put this on top off you schema.yml file ;)

propel:
  _attributes :          { package: "plugins.sfGuardPlugin.lib.model" }
  sf_guard_user:
    _attributes:         { phpName: sfGuardUser }
    id:

now you can simple add a foreignkey

propel:
  tbl_user_profile:
    user_id:             { type: integer, primaryKey: true, foreignTable: sf_guard_user, foreignReference: id, onDelete: cascade }

or

propel:
  tbl_user_profile:
    sf_guard_user_id:

<?php
/**
 * Tools for Propel model classes.
 *
 * @author ksn135 <serg [at] kalachev [dot] ru>
 * @package symfony
 * @version 0.1
 * @static 
 */
class myPropelTools
{ 
  public static function isPropelModelClass($class_name)
  { 
    try {
      $r_cls = new ReflectionClass( $class_name );
      $r_cls_peer = new ReflectionClass( $class_name.'Peer' );
      $r_base_cls = new ReflectionClass( 'Base'.$class_name );
      $r_base_cls_peer = new ReflectionClass( 'Base'.$class_name.'Peer' );
    }
    catch( Exception $e )
    {
      return false;
    }
    return true;
  } 
 
  public static function invokePeerMethod( $class_name, $method ) {
    if( ! self::isPropelModelClass($class_name) ) throw new Exception( "Class $class_name is not valid Propel Model class" );
    $args = @array_slice(func_get_args(), 2);
    return call_user_func_array( array($class_name.'Peer', $method), $args );
  }
 
  public static function getFieldName( $class_name, $field_name, $output_type = BasePeer::TYPE_PHPNAME )
  {
    return self::invokePeerMethod( $class_name, 'translateFieldName',
      $field_name, BasePeer::TYPE_FIELDNAME, $output_type );
  }
 
  public static function getColumnName( $class_name, $field_name )
  {
    list( $table, $column ) = explode('.', self::getFieldName( $class_name, $field_name, BasePeer::TYPE_COLNAME), 2);
    return $column;
  }
 
  public static function setValue( &$object, $field_name, $value ) {
    return self::invokeMethodName( $object, 'set', $field_name, $value );
  }
 
  public static function getValue( &$object, $field_name ) {
    return self::invokeMethodName( $object, 'get', $field_name );
  }
 
// criteria
  public static function getCriteria( $class_name )
  {
    if( ! self::isPropelModelClass($class_name) ) throw new Exception( "Class $class_name is not valid Propel Model class" );
    return new Criteria(constant($class_name.'Peer::DATABASE_NAME'));
  }
 
  public static function criteriaAddSelectColumn( &$criteria, $class_name, $field_name )
  {
    if( ! self::isPropelModelClass($class_name) ) throw new Exception( "Class $class_name is not valid Propel Model class" );
    return $criteria->addSelectColumn( constant($class_name.'Peer::'. self::getColumnName( $class_name, $field_name ) ) );
  }
 
  public static function criteriaAdd( &$criteria, $class_name, $field_name, $value = null, $comparison = null)
  {
    if( ! self::isPropelModelClass($class_name) ) throw new Exception( "Class $class_name is not valid Propel Model class" );
    return $criteria->add( constant($class_name.'Peer::'. self::getColumnName( $class_name, $field_name ) ), $value, $comparison );
  }
 
  public static function getRowSet( $class_name, $select_fields = null, $conditon_fields = null, $method = 'doSelectRS' )
  {
    $c = self::getCriteria( $class_name );
    if( $select_fields )
    {
      $fields = (is_array($select_fields)) ? $select_fields : array($select_fields);
      foreach( $fields as $field_name ) self::criteriaAddSelectColumn( $c, $class_name, $field_name);
    }
    if( $conditon_fields )
    {
      if( is_array( $conditon_fields ) && count($conditon_fields) )
      {
         $conditons = (is_array($conditon_fields[0]))? $conditon_fields : array( $conditon_fields );
      }
      else
        throw new Exception( "Condition parametr must be a not empty array (1 or 2 dimensional)" );
      foreach( $conditons as $key => $conditon )
      {
        $field_name = null;
        $value = null;
        $comparison = null;
        switch( count($conditon) )
        {
          case 1:
            $field_name = $condition;
            break;
          case 2:
            list( $field_name, $value ) = $conditon;
            break;
          case 3:
            list( $field_name, $value, $comparison ) = $conditon;
            break;
          default:
            throw new Exception( "Too many elements in condition #$key specified" );
        }
        self::criteriaAdd( $c, $class_name, $field_name, $value, $comparison );
      }
    }
    return self::invokePeerMethod( $class_name, $method, $c);
  }
 
// private methods
  private static function getMethodName( $class_name, $mode, $field_name ) {
    return $mode . self::getFieldName( $class_name, $field_name );
  }
 
  private static function invokeMethodName( &$object, $mode, $field_name, $value = '' ) {
    $methodName = self::getMethodName(get_class($object), $mode, $field_name);
    return ($mode == 'set') ? $object->$methodName($value) : $object->$methodName();
  }
 
}

save it into /<proj>/lib/myPropelTools.class.php and clear your cache

Thanks to netcrash from the symfony irc channel I came along regular expressions in MySQL: http://dev.mysql.com/doc/refman/4.1/en/regexp.html

Tutorial: http://www.brainbell.com/tutorials/MySQL/Using_MySQL_Regular_Expressions.htm

I'm using it to select all entries that don't start with a letter in an alphabetical pagination:

$c = new Criteria();
$regex = CmsUserPeer :: USERNAME." NOT REGEXP '^[[:alpha:]]'";
$c->add(CmsUserPeer :: USERNAME, $regex, Criteria::CUSTOM);
$this->cms_user = CmsUserPeer :: doSelect($c);

Instead of selecting by REGEXP pookey had the idea of "using substr to get the first char, then getting it's character code, and doing a BETWEEN on it" for performance reasons.

There is another faster solution:

$c = new Criteria();
$cq = "substring( ".CmsUserPeer :: USERNAME.", 1, 1 ) NOT BETWEEN  'a' AND  'z'";
$c->add(CmsUserPeer :: USERNAME, $cq, Criteria::CUSTOM);
$this->cms_user = CmsUserPeer :: doSelect($c);

I'm sure you've been there; you want to use Symfony's native sfPropelPager addon but you have to seed it with a complicated query that can't be reproduced via Criteria. In order to tackle this issue, I created a subclass of sfPropelPager called sfAdvancedPropelPager (it could probably benefit from a better name).

PLEASE NOTE: You will have to change all private methods and properties in sfPropelPager from 'private' to 'protected'. This won't break paging elsewhere in your projects, I promise you! What it will do is allow this subclass to gain access to it's parent's data and functionality. It should be noted that this addon was developed with the current stable version (0.6.3) of Symfony in mind.

I've cited the "search engine" example within the Askeet project as a suitable candidate for demonstration, albeit with some tweaks. See http://www.symfony-project.com/askeet/21 for details!!

<?php
...
public static function search($phrase, $exact = false, $offset = 0, $max = 10)
{
  $words    = array_values(myTools::stemPhrase($phrase));
  $nb_words = count($words);
 
  if (!$words)
  {
    return array();
  }
 
  $con = sfContext::getInstance()->getDatabaseConnection('propel');
 
  // define the base query
  $query = '
      SELECT DISTINCT '.SearchIndexPeer::QUESTION_ID.', COUNT(*) AS nb, SUM('.SearchIndexPeer::WEIGHT.') AS total_weight
      FROM '.SearchIndexPeer::TABLE_NAME;
 
  if (sfConfig::get('app_permanent_tag'))
  {
    $query .= '
      WHERE ';
  }
  else
  {
    $query .= '
      LEFT JOIN '.QuestionTagPeer::TABLE_NAME.' ON '.QuestionTagPeer::QUESTION_ID.' = '.SearchIndexPeer::QUESTION_ID.'
      WHERE '.QuestionTagPeer::NORMALIZED_TAG.' = ? AND ';
  }
 
  $query .= '
      ('.implode(' OR ', array_fill(0, $nb_words, SearchIndexPeer::WORD.' = ?')).')
      GROUP BY '.SearchIndexPeer::QUESTION_ID;
 
  // AND query?
  if ($exact)
  {
    $query .= '
      HAVING nb = '.$nb_words;
  }
 
  $query .= '
      ORDER BY nb DESC, total_weight DESC';
 
  // prepare the statement
  $stmt = $con->prepareStatement($query);
 
  $placeholder_offset = 1;
  if (sfConfig::get('app_permanent_tag'))
  {
    $stmt->setString(1, sfConfig::get('app_permanent_tag'));
    $placeholder_offset = 2;
  }
  for ($i = 0; $i < $nb_words; $i++)
  {
    $stmt->setString($i + $placeholder_offset, $words[$i]);
  }
 
  // time to call our shiny new pager!
  $pager = new sfAdvancedPropelPager('Question', $max);
  $pager->setStatement($stmt);
  $pager->setPage(ceil($offset / $max));
  $pager->init();
  return $pager;
}
...
?>

You would then assign the $pager to a suitable property in your action, and then handle your pagination in your template in the usual fashion as illustrated on <http://www.symfony-project.com/book/trunk/pager> - simple as that!

<?php
 
class sfAdvancedPropelPager extends sfPropelPager
{
  protected
    $statement = null;
 
  public function __construct($class, $defaultMaxPerPage = 10)
  {
    $this->setClass($class);
    $this->setMaxPerPage($defaultMaxPerPage);
    $this->setPage(1);
    $this->parameter_holder = new sfParameterHolder();
    $this->setPeerMethod('retrieveByPk');
  }  
 
  public function init()
  {
    require_once(sfConfig::get('sf_model_lib_dir').'/'.$this->getClassPeer().'.php');
 
    $rs = clone $this->getStatement()->executeQuery(ResultSet::FETCHMODE_NUM);
 
    $this->setNbResults($rs->getRecordCount());
 
    if (($this->getPage() == 0 || $this->getMaxPerPage() == 0))
    {
      $this->setLastPage(0);
    }
    else
    {
      $this->setLastPage(ceil($this->getNbResults() / $this->getMaxPerPage()));
      $this->statement->setOffset(($this->getPage() - 1) * $this->getMaxPerPage());
      $this->statement->setLimit($this->getMaxPerPage());
    }
  }
 
  public function getResults()
  {
    $rs = $this->getStatement()->executeQuery(ResultSet::FETCHMODE_NUM);
 
    $objects = array();
 
    while ($rs->next())
    {
      $objects[] = call_user_func(array($this->getClassPeer(), $this->getPeerMethod()), $rs->getInt(1));
    }
 
    return $objects;
  }
 
  protected function retrieveObject($offset)
  {
    $statement = clone $this->getStatement();
    $statement->setOffset($offset - 1);
    $statement->setLimit(1);
 
    $rs = $statement->executeQuery(ResultSet::FETCHMODE_NUM);
 
    $object = null;
    while ($rs->next())
    {
      $object = call_user_func(array($this->getClassPeer(), $this->getPeerMethod()), $rs->getInt(1));
    }
 
    return $object;
  }
 
  public function getStatement()
  {
    return $this->statement;
  }
 
  public function setStatement($stmt)
  {
    $this->statement = $stmt;
  }
}
 
?>

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

Say you look for the objects of class Foo being created between $from_date and $to_date. This should do the trick:

$c = new Criteria();
$criterion = $c->getNewCriterion(FooPeer::CREATED_AT , date('Y-m-d', $from_date), Criteria::GREATER_EQUAL  );
$criterion->addAnd($c->getNewCriterion(FooPeer::CREATED_AT , date('Y-m-d', $to_date), Criteria::LESS_EQUAL ));
$c->add($criterion);
$shows = FooPeer::doSelect($c);

To use database connections defined in the databases.yml configuration file, you can use the following snippet:

// initialize database manager
$databaseManager = new sfDatabaseManager();
$databaseManager->initialize();

complement complex SQL in SYMFONY:

Example 1: SQL to be implemented:

SELECT b.id FROM article_mark a RIGHT JOIN article b ON a.article_id = b.id ORDER BY a.mark DESC,b.CREATED_AT DESC

Symfony implementation:

    $c=new Criteria();
    $c->addAlias('a', 'article_mark');              //!!!if not using alias of table, the generated sql is not correct
    $c->addAlias('b', 'article');
    $c->addSelectColumn('b.id');
    $c->addSelectColumn('a.article_id');            //!!!if one table has no column added, there's no table name after FROM clause;; actually this column is not what I need
    $c->addDescendingOrderByColumn('a.mark');
    $c->addDescendingOrderByColumn('b.CREATED_AT');
    $c->addJoin('a.ARTICLE_ID','b.ID','RIGHT JOIN');
    $art_marks=ArticlePeer::doSelectRS($c);         //!!! doSelect or doSelectOne can't be used

By this way, $art_marks is a MySQL recordset, using $art_marks[0] get value of column b.id;

Example 2: SQL to be implemented:

select SUM(score) from bury group by article_id having article_id=@ARTICLE_ID

Symfony implementation:

        $c=new Criteria();
        $c->addSelectColumn('SUM(score) as A');     //actually 'as A' has no use
        $c->addSelectColumn(BuryPeer::SCORE);              //!! without this, no table name in generated sql; actually this column is not what I need
 
        $c->addGroupByColumn('article_id');
        $crit=$c->getNewCriterion(BuryPeer::ARTICLE_ID,$article->getId());
        $c->addHaving($crit);
        $buries=BuryPeer::doSelectRS($c);       //only MySQL recordset can be used

To enable InnoDB support in Propel, you can add this line at the end of your config/propel.ini configuration file:

propel.mysql.tableType = InnoDB

The problem

The problem is the following. Assume that you have two tables, say Book and Author. Each book has one author so the getter method to get the author of a book is getAuthor().

Now assume that you modify your model to have a second author. You have two references from the Book table to the Author table. Now your code doesn't work anymore because the propel method names have changed! The propel method names are now pretty cumbersome. They look like getAuthorReferencedByAuthor for the first author and getAuthorReferencedBySecondAuthor for the second one. Bleh!

The solution

Here is a very simple solution to customise all the getters and setters of propel. The idea is to give any name you want in the schema.xml file in the following manner (in the book table for instance):

<foreign-key foreignTable="author" localName="AsFirstAuthorBook" foreignName="Author">
  <reference local="author_id" foreign="id"/>
</foreign-key>
<foreign-key foreignTable="author" localName="AsSecondAuthorBook" foreignName="SecondAuthor">
  <reference local="second_author_id" foreign="id"/>
</foreign-key>

Now with the method i suggest propel will generate getters like getAuthor (for the main author) and getSecondAuthor which seems perfectly logical. To get a list of books that an author wrote as second author you would use getAsSecondAuthorBooks which is not so bad.

For this to work you'll have to add one file in the symfony directory. Put the following in the symfony/addon/propel/builder directory under the name of MyObjectBuilder.php.

<?php
require_once 'symfony/addon/propel/builder/SfObjectBuilder.php';
 
class MyObjectBuilder extends sfObjectBuilder
{
    public function getFKPhpNameAffix(ForeignKey $fk, $plural = false)
    {
    $fkName = $fk->getAttribute('foreignName');
    return $fkName . ($plural ? 's' : '');
    }
 
    public function getRefFKPhpNameAffix(ForeignKey $fk, $plural = false)
    {
        $fkName = $fk->getAttribute('localName');
        return $fkName . ($plural ? 's' : '');
    }
 
}

Now if you want to use that customising feature in a project you only need to change propel.ini on the line in front of propel.builder.object.class and replace SfObjectBuilder with MyObjectBuilder and you are done. That project will use the customising feature. Other projects will use the usual propel naming conventions.

Additional remarks

• You see that you can also easily customise the plurals in the same fashion
• symfony propel-build-schema will be broken since it will not generate the local and foreign names.
• The propel team will apparently add this functionality to the future version of Propel...

When you use the object_select_tag you specify related_class=Author for example. This will return all Authors and use the __toString() method to display the author.

However how do you select a subset of Authors based on some criteria.

To do this you can create a new Peer class that extends your BaseAuthorPeer, for example LiveAuthorPeer.

<?php
 
class LiveAuthorPeer extends BaseAuthorPeer {
 
    public static function doSelect(Criteria $criteria, $con = null)
    {
        $criteria = new Criteria();
        $criteria->add(AuthorPeer::IS_LIVE, 1);
        return AuthorPeer::doSelect($criteria);
    }
 
}
 
?>

You can the call this new class from you object_select_tag and it will return select options for only those authors with the value of IS_LIVE = 1.

<?php echo object_select_tag($books, 'getAuthorId', array (
  'related_class' => 'LiveAuthors'
)) ?>

$con = Propel::getConnection();
try
{
  $con->begin();
 
  // do something
 
  $con->commit();
}
catch (Exception $e)
{
  $con->rollback();
  throw $e;
}

The problem

Let us take the example of snipeet! :-) The function that selects snippets by tags is a custom SQL query. When filtering by tags there is no pager anymore.

Why is that? The problem is that sfPropelPaginate only works with a Criteria object, not with a raw query. As a result you cannot have pagination with raw SQL query which is a rather severe limitation.

The solution

The solution is to first encode the parameter of the query in the criteria (that's the tricky bit) and then to set up a custom peer method.

As i said earlier we will take a simplified version of snipeet as an example. We assume that we have a function that creates a sql statement from a list of tags.

Here we go about setting the dummy criteria that encodes an array of tags:

getCriteriaFromTags($tags)
{
  $c = new Criteria();
  for ($i = 1; $i <= count($tags); ++$i)
  {
    $prefix = 't'.$i;
    // note that that SnippetTagPeer::TABLE_NAME  SnippetTagPeer::NAME are in fact arbitrary
    // any other table and table.column combination would work as well
    $c->addAlias($prefix.SnippetTagPeer::TABLE_NAME, SnippetTagPeer::TABLE_NAME);
    $c->add($prefix.SnippetTagPeer::NAME, $tags[$i-1]);
  }
  return $c;
}

Now you can initialise a sfPropelPaginate object as usual with that Criteria object. When that is done you tell it to use your custom peer method:

$pager->setPeerMethod('doSelectByTags');

Next you code the doSelectByTags method that performs the actual selection from a criteria object:

function doSelectByTags($c)
{
  // first you fetch the tags from the query
  $tags = array();
  foreach($c->keys() as $key)
    $tags[] = $c->get($key);
 
  // here comes your custom sql query 
  // it basically creates a $statement variable from the $tags array
  // .....
 
  // now you have to add the limit and offset:
 $statement->setLimit($c->getLimit());
 $statement->setOffset($c->getOffset());
 // and the rest of the code is as usual
}

Create a batch/load_data.php script with:

<?php
 
define('SF_ROOT_DIR',    realpath(dirname(__FILE__).'/..'));
define('SF_APP',         'frontend');
define('SF_ENVIRONMENT', 'dev');
define('SF_DEBUG',       true);
 
require_once(SF_ROOT_DIR.DIRECTORY_SEPARATOR.'apps'.DIRECTORY_SEPARATOR.SF_APP.DIRECTORY_SEPARATOR.'config'.DIRECTORY_SEPARATOR.'config.php');
 
// initialize database manager
$databaseManager = new sfDatabaseManager();
$databaseManager->initialize();
 
$data = new sfPropelData();
$data->loadData(sfConfig::get('sf_data_dir').DIRECTORY_SEPARATOR.'fixtures');
 
?>

Call it with:

$ cd batch
$ php load_data.php

It will load the data contained in all the .yml files of the data/fixtures/ directory.

In app/lib/ create a file named myDBConnectionFilter.class.php and add:

class myDBConnectionFilter 
{
  public function initialize($filterChain) 
  {
    $db = sfContext::getInstance()->getDatabaseManager()->getDatabase('myschema');
    $db->setConnectionParameter('username', 'myusername');
    $db->setConnectionParameter('password', 'mypassword');
    $db->setConnectionParameter('hostspec', 'localhost');
    $db->setConnectionParameter('database', 'mydatabase');
 
    // The below line is optional - Symfony will connect anyway if no connection is present
    $db->connect();
 
  }
}

Then declare this filter to run on every page load by adding this to app/config/filters.yml:

myDBConnectionFilter:
  class: myDBConnectionFilter

Finally, Symfony will fail at the call above

sfContext::getInstance()->getDatabaseManager()->getDatabase('myschema');

because it does not know what type of database driver 'myschema' requires (MySQL/SQLite/etc.). You must add the following to app/config/databases.yml:

all:
  propel:
    class:          sfPropelDatabase
    param:
      phptype:            mysql

You can now dynamically select databases from the above class.

If you want to make an update according to the primary key you just need to use one criteria:

$c = new Criteria();
 
$c->add(MyObjectPeer::ID,123);
 
$c->add(MyObjectPeer::NAME,'ola');
$c->add(MyObjectPeer::CITY,'pepito');
 
MyObjectPeer::doUpdate($c);
 

and the query is:

UPDATE my_object SET NAME = 'ola',CITY = 'pepito' WHERE my_object.ID=123
 

Compared to the update query using Propel snippet which pass 2 criterias to the BasePeer:doUpdate method, here we pass only one criteria to the MyObjectPeer::doUpdate method. This method remove the index ID of the "set" criteria and use it for the "select" criteria.

You might've noticed that the default behavior of propel is to use numeric retrieval when grabbing rows from the database, and it is hard to override this action.

Sure you can do a custom query, but if you want to use criteria methods instead, this class will be helpful in grabbing a a result set via associative (thus preserving your associative keys and making coding easier on you).

I'd consider this beta level code. It's worked twice for me, both when I had a complex join that I wanted to use criteria for but failed once on a simpler query.

I haven't been able to decode why exactly the failure occured yet.

<?php
/*
*
*  @author: Kum Sackey
* 
*  Defines extension to propel that tweak its core behavior
* 
*/
class sfPropelExtension
{
    /**
    * @desc Return a result select from a criteria object. Unlike the implementation in BasePeer::doSelect(), this version returns the result set in associative mode
    */
    public static function getRSFromCriteria($criteria)
    {
        if(!($criteria instanceOf Criteria))
            throw new sfException('Passed parameter must be an instance of criteria, instead is of type: '.gettype($c));
 
        $dbMap = Propel::getDatabaseMap($criteria->getDbName());
        $con = Propel::getConnection();            
 
        $params = array();
 
        $sql = BasePeer::createSelectSql($criteria, $params);
        $stmt = $con->prepareStatement($sql);            
        $stmt->setLimit($criteria->getLimit());
        $stmt->setOffset($criteria->getOffset());            
 
        self::populateStmtValues($stmt, $params, $dbMap);                        
 
        $rs = $stmt->executeQuery($sql, ResultSet::FETCHMODE_ASSOC);  
 
        return $rs;        
    }
 
    /**
     * Populates values in a prepared statement.
     *
     * @param PreparedStatement $stmt
     * @param array $params array('column' => ..., 'table' => ..., 'value' => ...)
     * @param DatabaseMap $dbMap
     * @return int The number of params replaced.
     */
    private static function populateStmtValues($stmt, $params, DatabaseMap $dbMap)
    {
        $i = 1;
        foreach($params as $param) {
            $tableName = $param['table'];
            $columnName = $param['column'];
            $value = $param['value'];
 
            if ($value === null) {
                $stmt->setNull($i++);
            } else {
                $cMap = $dbMap->getTable($tableName)->getColumn($columnName);
                $setter = 'set' . CreoleTypes::getAffix($cMap->getCreoleType());
                $stmt->$setter($i++, $value);
            }
        }
    } 
}
 

Even though the book is quite good, the part in which unit testing is explained could use some better information. Especially the testing with a database connection (Propel in this case) needs some fixing.

Below you find an example unit test in which the database is accessed.

It tests a fictional class 'testClass' with the method 'load()'. That method accesses the database ... for which Propel needs to be running. The Symfony application that is loaded is called 'myapp';

if (!@constant('SF_APP')) { // Only load constants in not done before (group tests)
    define('SF_APP', 'myapp');
    define('SF_ENVIRONMENT', 'dev');
    define('SF_DEBUG', TRUE);
}
 
if (!@constant('SF_ROOT_DIR')) { // Only load constants in not done before (group tests)
    include(dirname(__FILE__).'/../bootstrap/unit.php');
}
 
sfCore::initSimpleAutoload(array(SF_ROOT_DIR.'/lib/model' // DB model classes
                                ,$sf_symfony_lib_dir // Symfony itself
                                ,dirname(__FILE__).'/../../lib' // Location class to be tested
                                ,dirname(__FILE__).'/../../apps/stageselect/lib' // Location myapp application
                                ,SF_ROOT_DIR.'/plugins')); // Location plugins
 
set_include_path($sf_symfony_lib_dir . '/vendor' . PATH_SEPARATOR . SF_ROOT_DIR . PATH_SEPARATOR . get_include_path());
 
/*
 * Start database connection and Symfony core
 */
sfCore::bootstrap($sf_symfony_lib_dir, $sf_symfony_data_dir);
sfContext::getInstance();
Propel::setConfiguration(sfPropelDatabase::getConfiguration());
Propel::initialize();
 
/*
 * Test
 */
// Init
$oTest = new lime_test(1, new lime_output_color());
 
// Print head
$oTest->diag('testClass');
$oTest->diag('----');
 
// Does the method load() exist in class 'testClass'
$oTest->can_ok('testClass', 'load', 'testClass has method load()');

The problem

Say that you have a model object Son that inherits from the model object Father, via propel inheritance. You want to create an admin generator interface for the object Son. The problem is that propel does not generate a SonPeer class, so you'll have to call:

symfony propel-init-admin Father

but the list function will list all the Father objects instead of the Son objects only. Same problem with the create function that will create a Father object, not a Son object.

The solution

Here is a solution. You will have to overload getFatherOrCreate and addFiltersCriteria:

// add this in the actions.class.php of your admin module
  protected function addFiltersCriteria(&$c)
  {
    $c->add(FatherPeer::CLASS_KEY, FatherPeer::CLASSKEY_SON);
    parent::addFiltersCriteria($c);
  }  
 
  protected function getFatherOrCreate ($id = 'id')
  {
    $son = parent::getFatherOrCreate($id);
 
    if ($son->isNew()) // if it is a new one then we create a Son object
      $son = new Son();
   else
     $this->redirect404Unless($son->isSon()); // we check that we really got a son object
 
   return $son;
  }

Now everything will work as if you were using the Son object.

The following patch will help you exclude the schema generation of certain tables when running propel-build-schema.

This might come in handy when you use propel-build-schema along with plugin schemas.

edit $sf_symfony_lib_dir/vendor/propel-generator/classes/propel/phing/PropelCreoleTransformTask.php

    protected function createDatabaseNode($dbInfo) {
 
        $this->log("Processing database");
 
        $node = $this->doc->createElement("database");
        $node->setAttribute("name", $dbInfo->getName());
 
        if ($vendorNode = $this->createVendorInfoNode($dbInfo->getVendorSpecificInfo())) {
            $node->appendChild($vendorNode);
        }
 
        global $schema_exclude_pattern;
        $pattern = $schema_exclude_pattern;
 
        $this->log("Exclude pattern : ".$pattern);
        // create and add table nodes
        foreach($dbInfo->getTables() as $table) {
            if (preg_match($pattern,$table->getName()))
            {
                $this->log("Skipping : ".$table->getName()." ( matches exclude pattern )");
                continue;
            }
            $tableNode = $this->createTableNode($table);
            $node->appendChild($tableNode);
        }
 
        return $node;
    }
 

pattern provided via the $schema_exclude_pattern variable which can be set in config.php (kinda ugly but it works)

config.php

<?php
//
// symfony directories
$sf_symfony_lib_dir  = '/bridge/lib/symfony/1.0/lib';
$sf_symfony_data_dir = '/bridge/lib/symfony/1.0/data';
 
// skips schema creation for tables which name matches the following pattern 
// when executing propel-build-schema
$schema_exclude_pattern = "/^sf_guard.*/i";
 

More a PHP trick than symfony's one, but as you can't with propel randomize order of results, just do:

$c = new Criteria()
... fill your criteria there ...
$result = MyTablePeer::doSelect($c);
shuffle($result);

obvious? sorry, seen someone asking on IRC once :D

It took me quite some time but here is how to execute a MySQL stored procedure.

$connection = Propel::getConnection();
 
$query = 'CALL Proc(%s, %s, %s)';    
$query = sprintf($query, $var1, $var2, $var3);
 
$mysqli = $connection->getResource();
if($mysqli->multi_query($query)){
  do{
    if($result = $mysqli->use_result()){
      while($row = $result->fetch_assoc()){
        //
      }
      $result->free();
    }
  } while(($mysqli->next_result()));
}
 

Note that you should be using mysqli in order for this to work properly.

By default, I18n content in database does not support fallback in default culture. This snippet allow you to enable I18n content fallback in order to always have a default value for your texts.

This snippet is sponsored by Dorigo consultants.

Fallback I18n content

To enable the fallback, edit your table object class in lib/model/TableClassName.php and add the following code. Then search and replace TableClassName by your table object class name.

  /**
   * Fetch the i18n object for this object culture.
   * 
   * @return     mixed A i18n object
   * @throws     PropelException Any exceptions caught during processing will be
   *     rethrown wrapped into a PropelException.
   */
  public function getCurrentTableClassNameI18n()
  {
    if (!isset($this->current_i18n[$this->culture]))
    {
      $obj = TableClassNameI18nPeer::retrieveByPK($this->getId(), $this->culture);
      if ($obj) // Test if there is a translation for current culture
      {
        $this->setTableClassNameI18nForCulture($obj, $this->culture);
      }
      else // Create a translation for this culture
      {
        $new_i18n = new TableClassNameI18n();
 
        $default_culture = sfConfig::get('sf_i18n_default_culture');
 
        // We try to fetch the default culture translation to initialise the new culture.
        if (!isset($this->current_i18n[$default_culture]))
        {
          $obj = TableClassNameI18nPeer::retrieveByPK($this->getId(), $default_culture);
          if ($obj) // Test if there is a translation for current culture
          {
            $this->setTableClassNameI18nForCulture($obj, $default_culture);
          }
        }
        else
        {
          $obj = $this->current_i18n[$default_culture];
        }
 
        if ($obj)
        {
          $obj->copyInto($new_i18n);
        }
 
        $new_i18n->setId($this->getId());
        $new_i18n->setCulture($this->culture);
 
        $this->setTableClassNameI18nForCulture($new_i18n, $this->culture);
      }
    }
 
    return $this->current_i18n[$this->culture];
  }
 

Saving fallback values at creation

You now need to add default translation when you create a new object. We do that with this doSave function.

To use this function, edit your table object class in lib/model/TableClassName.php and add the following code. Then search and replace TableClassName by your table object class name.

  /**
   * Stores the object in the database while setting default culture if necessary.
   *
   * If the object is new, it inserts it; otherwise an update is performed.
   * All related objects are also updated in this method.
   *
   * @param      Connection $con The database connection
   * @return     int The number of rows affected by this insert/update and any referring fk objects' save() operations.
   * @throws     PropelException Any exceptions caught during processing will be
   *     rethrown wrapped into a PropelException.
   * @see        save()
   */
  protected function doSave($con)
  {
 
    $default_culture = sfConfig::get('sf_i18n_default_culture');
 
    // We try to fetch the default culture translation to initialise the new culture.
    if (!isset($this->current_i18n[$default_culture]))
    {
      $obj = TableClassNameI18nPeer::retrieveByPK($this->getId(), $default_culture, $con);
      if ($obj) // Test if there is a translation for current culture
      {
        $this->setTableClassNameI18nForCulture($obj, $default_culture);
      }
    }
    else
    {
      $obj = $this->current_i18n[$default_culture];
    }
 
    if(!$obj && isset($this->current_i18n[$this->culture]))
    {
      $new_i18n = new TableClassNameI18n();
      $this->current_i18n[$this->culture]->copyInto($new_i18n);
 
      $new_i18n->setId($this->getId());
      $new_i18n->setCulture($default_culture);
 
      $this->setTableClassNameI18nForCulture($new_i18n, $default_culture);
    }
 
    return parent::doSave($con);
  }
 

Fetching a list with default translations

To complete this snippet, here is a fallback version of doSelectWithI18n.

To enable the fallback, edit your table object peer class in lib/model/TableClassNamePeer.php and add the following code. Then search and replace TableClassName by your table object class name.

  /**
   * Selects a collection of TableClassName objects pre-filled with their i18n objects.
   *
   * @param      Criteria $criteria
   * @param      string $culture The selected culture.
   * @param      Connection $con An optional database connection
   * @return     array Array of TableClassName objects.
   * @throws     PropelException Any exceptions caught during processing will be
   *     rethrown wrapped into a PropelException.
   */
  public static function doSelectWithI18n(Criteria $c, $culture = null, $con = null)
  {
    if ($culture === null)
    {
      $culture = sfContext::getInstance()->getUser()->getCulture();
    }
 
    $default_culture = sfConfig::get('sf_i18n_default_culture');
 
    // Set the correct dbName if it has not been overridden
    if ($c->getDbName() == Propel::getDefaultDB())
    {
      $c->setDbName(self::DATABASE_NAME);
    }
 
    TableClassNamePeer::addSelectColumns($c);
    $startcol = (TableClassNamePeer::NUM_COLUMNS - TableClassNamePeer::NUM_LAZY_LOAD_COLUMNS) + 1;
 
    TableClassNameI18nPeer::addSelectColumns($c);
 
    $c->addJoin(TableClassNamePeer::ID, TableClassNameI18nPeer::ID);
    $criterion = $c->getNewCriterion(TableClassNameI18nPeer::CULTURE, $culture);
    $criterion->addOr($c->getNewCriterion(TableClassNameI18nPeer::CULTURE, $default_culture));
    $c->add($criterion);
 
    $rs = BasePeer::doSelect($c, $con);
    $results = array();
    $uncultured_results = array();
 
    while($rs->next()) {
 
      $omClass = TableClassNamePeer::getOMClass();
 
      $cls = Propel::import($omClass);
      $obj1 = new $cls();
      $obj1->hydrate($rs);
      $obj1->setCulture($culture);
 
      if(isset($results[$obj1->getId()]))
      {
        $obj1 = $results[$obj1->getId()];
      }
 
      $omClass = TableClassNameI18nPeer::getOMClass($rs, $startcol);
 
      $cls = Propel::import($omClass);
      $obj2 = new $cls();
      $obj2->hydrate($rs, $startcol);
 
      $obj1->setTableClassNameI18nForCulture($obj2, $obj2->getCulture());
      $obj2->setTableClassName($obj1);
 
      if(!isset($uncultured_results[$obj1->getId()]))
      {
        $uncultured_results[$obj1->getId()] = $obj1;
      }
 
      if($obj2->getCulture() == $culture)
      {
        $uncultured_results[$obj1->getId()] = false;
      }
 
      if(!isset($results[$obj1->getId()]))
      {
        $results[$obj1->getId()] = $obj1;
      }
      elseif($obj2->getCulture() == $culture)
      {
        // Move result to the end of results array to fit eventual sort
        // criteria (ugly fix).
        unset($results[$obj1->getId()]);
        $results[$obj1->getId()] = $obj1;
      }
 
    }
 
    foreach($uncultured_results as $obj1)
    {
      if($obj1)
      {
        $obj1->setCulture($default_culture);
        $default_culture_object = $obj1->getCurrentTableClassNameI18n();
        if($default_culture_object)
        {
          $obj2 = new TableClassNameI18n();
          $default_culture_object->copyInto($obj2);
          $obj2->setCulture($culture);
          $obj2->setTableClassName($obj1);
          $obj1->setTableClassNameI18nForCulture($obj2, $obj2->getCulture());
        }
        $obj1->setCulture($culture);
      }
    }
 
    return array_values($results);
  }
 

Counting results filtered on translations

If you want to use a pager with a filter on translations, you will need this count method. Once this method present, the magic is done by :

    $criteria->setDistinct();
    $pager->setCriteria($criteria);
    $pager->setPeerMethod('doSelectWithI18n');
    $pager->setPeerCountMethod('doCountWithI18n');
 

Note : The setDistinct is very important for this snippet to work. It should not falsify your results, and without it, the doCountWithI18n method could return bad results.

To enable the fallback, edit your table object peer class in lib/model/TableClassNamePeer.php and add the following code. Then search and replace TableClassName by your table object class name.

  /**
   * Returns the number of rows matching criteria with I18N criteria.
   *
   * @param      Criteria $criteria
   * @param      boolean $distinct Whether to select only distinct columns (You can also set DISTINCT modifier in Criteria).
   * @param      Connection $con An optional database connection
   * @param      string $culture The selected culture.
   * @return     int Number of matching rows.
   */
  public static function doCountWithI18n(Criteria $criteria, $distinct = false, $con = null, $culture = null)
  {
    // we're going to modify criteria, so copy it first
    $criteria = clone $criteria;
 
    $default_culture = sfConfig::get('sf_i18n_default_culture');
 
    if ($culture === null)
    {
      // We use current user culture.
      $culture = sfContext::getInstance()->getUser()->getCulture();
    }
 
    // clear out anything that might confuse the ORDER BY clause
    $criteria->clearSelectColumns()->clearOrderByColumns();
    $criteria->addSelectColumn(TableClassNamePeer::COUNT_DISTINCT);
 
    // just in case we're grouping: add those columns to the select statement
    foreach($criteria->getGroupByColumns() as $column)
    {
      $criteria->addSelectColumn($column);
    }
 
    $criteria->addJoin(TableClassNamePeer::ID, TableClassNameI18nPeer::ID);
    $criterion = $criteria->getNewCriterion(TableClassNameI18nPeer::CULTURE, $culture);
    $criterion->addOr($criteria->getNewCriterion(TableClassNameI18nPeer::CULTURE, $default_culture));
    $criteria->add($criterion);
 
    $rs = TableClassNamePeer::doSelectRS($criteria, $con);
    if ($rs->next()) {
      return $rs->getInt(1);
    } else {
      // no rows returned; we infer that means 0 matches.
      return 0;
    }
  }
 

$connection = sfContext::getInstance()->getDatabaseConnection('propel');

<?php
/* Designed to be compatible with sfPropelPager only accept raw sql queries instead
 * of a criteria object and spit back a resultset which you most likely want to
 * fill an array with instead of the standard array of hydrated objects.
 *
 * @author  Noel Tarnoff, Oz Basarir dev AT (NOSPAM) naturalcapitalDOTorg
 *
 * Typical usage scenario:
 * 1) build 2 query strings with common WHERE clause, one for count one for selecting the rows
 * 2) pass the queries in with page and maxPerPage into the constructor ( no need to ->init() )
 * 3) iterate through your result set and build your array of values
 * 4) sit back and watch the fun
 *
 * ex.
 *  $objPager = new nonHydratingPager($query_select, $query_count, $page, $max);
 *
 *  $rs = $objPager->getResultSet();
 *
 *  $arrEntities = array();
 *  while ( $rs->next() ) {
 *     $arrEntities[$] = array('field1'=>$rs->getString(1),
 *                             'field2'=>$rs->getString(2),
 *                             'field3'=>$rs->getString(3));
 *  }
 */
 
 
class nonHydratingPager extends sfPager
{
private
  $resultSet = null;
 
  public function __construct($query, $query_count, $page = 1, $maxPerPage = 25)
  {
    $this->setPage($page);
    $this->setMaxPerPage($maxPerPage);
 
    $con = Propel::getConnection();
 
    $stmt = $con->prepareStatement($query_count);
    $rs = $stmt->executeQuery(ResultSet::FETCHMODE_NUM);
    $rs->next();
 
    $this->setNbResults($rs->get(1));
 
    $this->setLastPage(ceil($this->getNbResults() / $this->getMaxPerPage()));
 
    $startIndex = (($this->getPage()) - 1) * $maxPerPage;
 
 
    $query .= ' LIMIT ' . $maxPerPage . ' OFFSET ' . $startIndex;
    $stmt = $con->prepareStatement($query);
 
    $this->resultSet = $stmt->executeQuery(ResultSet::FETCHMODE_NUM);
  }
 
  public function init() {}
 
  public function getResults() {
    return $this->resultSet;
  }
 
  protected function retrieveObject($offset) {}
 
}

To modify the settings of a connection named 'propel':

$con = sfContext::getInstance()->getDatabaseConnection('propel');
$con->setConnectionParameter('username', 'foo');
$con->setConnectionParameter('password', 'bar');

This works for all the settings that can be defined in the databases.yml (more info in the sfPropelDatabase class source).

You must execute this code before the first query to the database. If you do that after a first query, it fails. This means that the best way to implement it is in a filter.

People using Propel who want to perform custom joins and especially hydrations probably all faced the limited functionality that Propel offers for this: the probably well known doSelectJoinAll and doSelectJoinXXX methods (and similar for the count functionality)

Whenever you want to join only two related tables (or more, but not all), a related-related table (or even deeper) or a table that is related by multiple foreign-keys (like created_by and updated_by both refering to a user) you will find it gets hard to do this with the default functionality offered by Propel.

I have written a Helper/Plugin that solves this limitation, that can be downloaded from the plugin-pages: http://www.symfony-project.org/plugins/sfPropelHelperPlugin

The plugin contains:

• a Helper to perform manipulations on standard Criteria
• new Builders to generate slightly more functions for your object and peer-classes
• a Patch for Propel with a small improvement to allow lazy resolving of the realTable name (the Propel community is somewhat harder to get into contact with, since everything gets marked as spam)

The plugin does not change the way how to work with Propel, it will only expand the possibilities while remaining completely backwards compatible.

An example of how to use this helper:

Imagine you have a class of albums, and that every album can contain photos as well as other albums. You request all photo's sorted reversed on album name and join the related table to get the table-name in one go.

propel:
  photo:
    id:
    album_id:
    name:     varchar(50)
 
  album:
    id:
    album_id:
    name:     varchar(50)
 

php code:

$this->getContext()->getConfiguration()->loadHelpers('sfPropelPropertyPath');
 
$criteria = new Criteria();
 
$sortColumn = AlbumPeer::alias(str_replace('.', '_', 'Photo.Album') , AlbumPeer::NAME); // this should be done nicer some day
$criteria->addDescendingOrderByColumn($sortColumn);
 
$objectPaths = array('Photo', 'Photo.Album'); // this can be reduced (is similar) to array('Photo.Album');
 
$criteria = addJoinsAndSelectColumns($criteria, $objectPaths);
$photos = hydrate($criteria, $objectPaths, $connection = null); // I haven't decided if I want hydrate to perform the joining as well
 
foreach ($photos as $photo)
{
  echo $photo->getAlbum()->getName()." -> ".$photo->getName()."<br>\n";
}
 

The resulting sql is:

SELECT Photo.ID, Photo.ALBUM_ID, Photo.NAME, Photo_Album.ID, Photo_Album.ALBUM_ID, Photo_Album.NAME FROM `photo` `Photo` LEFT JOIN album Photo_Album ON (Photo.ALBUM_ID=Photo_Album.ID) ORDER BY Photo_Album.NAME DESC
 

I designed so called ObjectPaths and PropertyPaths together with a colleague of mine (Frans van der Lek), to make it easier to define classes and respectively properties of the (related) classes. The Object Paths contain the name of the base-class, followed by the relation-names defined in the peer-classes. These relation-names are defined by the builder, but can be extended/modified by extending the basePeer class (getRelations)

You can extend your Classes with custom Get-methods that can be accessed with the property-paths. I will show how this all comes to use, in my next article about the sfDataSourcePlugin, that I will release soon. (A plugin that provides a generic interface to select, sort, filter and iterate-over rows, no matter if they are provided by Propel, Doctrine, an Array, an Imap connection, etc. The Datasource interface is very light (in contradiction to dbFinder (no offense)) which makes it easy to write your own implementation)

Please provide feedback, questions and if desired I can add more examples/info.

While developing in symfony 1.0 I ran into the issue to validate how many checkboxes where actually checked. I created a custom validator class which was able to check for a minimum and a maximum value and throw corresponding error messages. Since I moved the project to symfony 1.2 I needed to update the validator, here is my solution (just place it somewhere in an autoloading directory like %project_root_dir%/lib and clean your cache):

class customValidatorPropelChoiceMany extends sfValidatorPropelChoiceMany
{
    protected function configure($options = array(), $messages = array())
    {
        parent::configure($options, $messages);
 
        $this->addOption('min', null);
        $this->addOption('max', null);
 
        $this->addMessage('min', 'Please select at least %min% fields.');
        $this->addMessage('max', 'Please do not select more than %max% fields.');
    }
 
    protected function doClean($value)
    {
        $value = parent::doClean($value);
 
        if ($this->getOption('multiple'))
        {
            if (count($value) < $this->getOption('min'))
            {
                throw new sfValidatorError($this, 'min', array('min' => $this->getOption('min')));
            }
            if (count($value) > $this->getOption('max'))
            {
                throw new sfValidatorError($this, 'max', array('max' => $this->getOption('max')));
            }
        }
 
        return $value;
    }
}
 

This code can easily be used to validate regular checkboxes (those which don't rely on a propel object), should be as easy as renaming the class and changing the parent to sfValidatorChoiceMany.

A simple class to generate thumbnail linked to the propel model. sfThumbnailPlugin is needed and sfPropelPlugin (of course)

Override two methods create one, add a constant and go !

Give me your opinion !

You need to add into peer class (MediaPeer in exemple) :

<?php
 
class MediaPeer extends BaseMediaPeer
{  
  // directory of file (under uploads file)
  const IMAGE_DIR = 'images/media';
 
  // override
  public static function doDelete($values, PropelPDO $con = null)
  {
    // delete every image file linked
    return sfPropelThumbnail::deleteThumb($values,$con, 'Media');
  }
 
  // Configuration
  public static function getThumbConfig()
  {
 
    return  array( 'image'=>  // each fields to thumbnailize
               array(         // each thumbnail configuration for field
                    array('thumb_dir' => 'thumbnail', 'size' => array(200, 500)),
                    array('thumb_dir' => 'fullsize' , 'size' => array(500, 700), 'crop'=>true),
                    array('thumb_dir' => 'verysmall', 'size' => array(60,60)   , 'crop'=>true)
                    )
                );
  }
}
 

And into your model class (Media in exemple) :

<?php
 
class Media extends BaseMedia
{
  public function setImage($value, $thumb=true)
  {
    // if first call thumbnailize image and after put file name in the db
    if($thumb)
      sfPropelThumbnail::generateThumbnail($value,$this ,'Media', 'image');
    else 
      parent::setImage($value);
  }
} 
 

sfPropelThumbnail

<?php
 
/*
 * @author: Simon Bonjean (sim@45-ouest.be)
 */
 
class sfPropelThumbnail {
  /*
   *  getConfiguration
   */
  protected static function getDefaultConfig($class)
  {
    $dir       = constant($class .'Peer::IMAGE_DIR');     
    $configs   = call_user_func(array($class .'Peer','getThumbConfig')); 
    return array($dir, $configs);
  }
 
  /*
   * Generate the thumbnails
   */
  public static function generateThumbnail($value,$that,$class,$fields_name)
  {
 
    // Variable 
    $upload_path = sfConfig::get('sf_upload_dir');         // Get path to upload
    list($dir, $configs) = self::getDefaultConfig($class); // Get Config and dir name
    $configs = $configs[$fields_name];                     // Get config for name
    $methode = 'set'.ucfirst($fields_name);                // Get Methode 
 
    if(empty($value)) 
    {
      // delete all thumbs
      foreach($configs as $config) {
         $thumb_dir = $config['thumb_dir'];
        if(is_file($upload_path . "/$dir/$fields_name/$thumb_dir/" . $that->getImage()))
            unlink($upload_path . "/$dir/$fields_name/$thumb_dir/" . $that->getImage() );
      }
      // Set l'image sans faire de thumb
 
      call_user_func(array($that,$methode),$value, false);
 
    } else {
      // delete old thumb
      foreach($configs as $config){
        $thumb_dir = $config['thumb_dir'];
        if(is_file($upload_path."/$dir/$fields_name/$thumb_dir/".$that->getImage()))
          unlink( $upload_path . "/$dir/$fields_name/$thumb_dir/".$that->getImage() );
      }
 
      // Set l'image sans faire de thumb 
      call_user_func(array($that,$methode),$value, false);
      // generate each thumbs
      foreach($configs as $config) {
 
        $thumb_dir = $config['thumb_dir'];
        $size      = $config['size']; 
 
 
        // CREATE DIRECTORY IF NOT EXIST !!!
 
        if(!is_dir($upload_path."/$dir/"))
            mkdir($upload_path."/$dir/");                           // base name
        if(!is_dir($upload_path."/$dir/$fields_name/"))             
            mkdir($upload_path."/$dir/$fields_name/");              // field name
        if(!is_dir($upload_path."/$dir/$fields_name/$thumb_dir/"))
            mkdir($upload_path."/$dir/$fields_name/$thumb_dir/");   // thumb name  
 
        if(isset($config['crop']) && $config['crop'] == true) {
          // Make Crop 
          self::cropImage($size[0], $size[1],
            $upload_path."/$dir/$fields_name/".$that->getImage(),
            $upload_path."/$dir/$fields_name/$thumb_dir/".$that->getImage());
        } else {
          // Make Thumb
          $thumbnail = new sfThumbnail($size[0], $size[1]);
          $thumbnail->loadFile($upload_path."/$dir/$fields_name/".$that->getImage()); 
          $thumbnail->save($upload_path."/$dir/$fields_name/$thumb_dir/".$that->getImage(), 'image/png');
        }
      }
    } 
 
  }  
 
  /*
   * delete each thumbnail when you delete binded element
   */
  public static function deleteThumb($values,PropelPDO $con, $class)
  {
    // Variable 
    list($dir, $configs) = self::getDefaultConfig($class);
    $upload_path = sfConfig::get('sf_upload_dir');
 
    if($retVal = call_user_func(array('Base' . $class .'Peer','doDelete'),$values, $con))
 
    foreach($configs as $fields_name => $fields_config) {
 
      $files = self::_getThumb($values, $class, $fields_name);
 
      if($files){     
 
        foreach($files as $file) {
          if(is_file($upload_path."/$dir/$fields_name/".$file)) {
            unlink( $upload_path . "/$dir/$fields_name/".$file );
          }
 
          foreach($fields_config as $config){
            $thumb_dir = $config['thumb_dir'];
            if(is_file($upload_path."/$dir/$fields_name/$thumb_dir/".$file)) {
              unlink( $upload_path . "/$dir/$fields_name/$thumb_dir/".$file );
            }
          }
        }
      }
    }
    return $retVal;
  }
 
  /*
   * retruive all piture
   */ 
  protected  static function _getThumb($values, $class, $fields_name){ 
    // Variable 
    $files       = false;   
    $fields_name = 'get' . ucfirst($fields_name);
 
    if ($values instanceof Criteria) {
      $tmps = call_user_func(array($class.'Peer','doSelect'), $values);
 
      foreach($tmps as $value)
        if($temp_thumb = call_user_func(array($value,$fields_name)) != '')
          $files[] = $temp_thumb;
    } elseif (get_class($values) == $class) {        
 
      if($temp_thumb = call_user_func(array($values,$fields_name))  != '')
        $files[] = $temp_thumb;
 
    } else {
      $tmps = call_user_func(array($classPeer,'retrieveByPKs'),$values);
 
      foreach($tmps as $value)
        if($temp_thumb = call_user_func(array($value,$fields_name)) != '')
          $files[] = $temp_thumb;
    }
    return $files;
  }
 
 
 
  /* 
   * This method thumbnailize image with crop option (center default)
   */
  public static function cropImage($nw, $nh, $source, $dest) { 
      $size = getimagesize($source);
      $len = strlen($source);
      $stype = substr($source,$len-3,3);
      $w = $size[0];
      $h = $size[1]; 
      switch($stype) {
          case 'gif':
            $simg = imagecreatefromgif($source);
          break;
          case 'jpg':
            $simg = imagecreatefromjpeg($source);
          break;
          case 'png':
            $simg = imagecreatefrompng($source);
          break;
      }
      $dimg = imagecreatetruecolor($nw, $nh); 
      $wm = $w/$nw;
      $hm = $h/$nh;
      $h_height = $nh/2;
      $w_height = $nw/2;
      if($w> $h) {
          $adjusted_width = $w / $hm;
          $half_width = $adjusted_width / 2;
          $int_width = $half_width - $w_height;
          imagecopyresampled($dimg,$simg,-$int_width,0,0,0,$adjusted_width,$nh,$w,$h);
      } elseif(($w <$h) || ($w == $h)) {
          $adjusted_height = $h / $wm;
          $half_height = $adjusted_height / 2;
          $int_height = $half_height - $h_height;
          imagecopyresampled($dimg,$simg,0,-$int_height,0,0,$nw,$adjusted_height,$w,$h);
      } else {
          imagecopyresampled($dimg,$simg,0,0,0,0,$nw,$nh,$w,$h);
      }
      imagejpeg($dimg,$dest,150);
  }
}