Relating to the post http://www.symfony-project.org/forum/index.php/m/32510/?srch=insert+values+propel

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('ScToolkit::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\')';
    $phpNameMap = array();
    eval('$phpNameMap = array_flip('.$classPeer.'::getPhpNameMap());');
 
    //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($dataKey=='CREATED_AT' || $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);
 
    $phpNameMap = array();
    eval('$phpNameMap = array_flip('.$classPeer.'::getPhpNameMap());');
 
    $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);
 

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.

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

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