Code snippets for symfony 1.x

Navigation

Refine Tags

Snippets tagged "propel"

Generic action to save edit-in-place fields Update

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;
  }
 
by Torsten Zander on 2008-02-03, tagged ajax  propel 

subqueries with criteria

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

by noname noname on 2008-01-26, tagged criteria  order  propel  sql  subquery 
(2 comments)

Simulating a BETWEEN construct

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.

by Jesse Dhillon on 2007-08-22, tagged between  criteria  criterion  date  mysql  propel  query  range 
(2 comments)

Autosetting Created_by and Updated_by fields in all models

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

by Boris Duin on 2007-01-29, tagged propel 
(7 comments)

Find durable events between two dates with Propel

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.

Appartment Reservation
id id
... appartment_id
begin_date
end_date
...

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);
  }
}
by Francois Zaninotto on 2006-09-14, tagged date  propel 
(2 comments)

update query using Propel

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);
by Francois Zaninotto on 2006-07-03, tagged criteria  propel 
(8 comments)

Minimal CSS pagination helper

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

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) ?>
by brikou on 2006-07-19, tagged css  helper  pager  pagination  propel 
(6 comments)

Setting UTF-8 for Propel with MySQL tables

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.

by Klemen Slavič on 2006-06-19, tagged collation  propel  utf8 
(5 comments)

Custom criteria for comparing 2 fields from the same record

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);
by Francois Zaninotto on 2006-07-10, tagged criteria  propel 
(4 comments)

Pagination navigation helper

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') ?>
by Francois Zaninotto on 2006-05-20, tagged helper  pager  pagination  propel 

Creating SQL for multiple insert

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);
 
by Yi Sheng Yap on 2008-05-13, tagged database  multiple  propel  sql 
(2 comments)

Using multiple databases from Propel

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);
by Kota Sakoda on 2006-08-30, tagged database  model  propel 
(1 comment)

Using SQL aggregate functions

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

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

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

propel behavior to extend classes with separate tables

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" :

by Vincent Texier on 2007-08-13, tagged behavior  inheritance  model  propel 
(7 comments)

Change propel connections at runtime

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();
 
by Jean Elsner on 2007-11-29, tagged change  connection  propel  runtime 

Retrieve model objects with custom SQL query

$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 );
by Yuriy Smirnov on 2007-08-07, tagged custom  model  object  propel  sql 

Mutex - Mutual exclusion semaphore

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.

by Oriol Mercade on 2007-06-01, tagged exclusion  mutex  mutual  propel  semaphore 

Retrieve Propel Object Using Non-Primary Key Field

<?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.

by Kum Sackey on 2007-05-17, tagged propel 

foreignkey to sf_guard_user

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:
by Gordon Franke on 2007-04-27, tagged database  foreignkey  model  plugin  propel  schema  security  user  yml 
(1 comment)

myPropelTools

<?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

by Serg Kalachev on 2007-03-02, tagged propel 
(2 comments)

Propel: Select entries that are not alpha

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);
by ian iam on 2007-02-12, tagged criteria  mysql  propel  regexp 

sfAdvancedPropelPager

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;
  }
}
 
?>
by Nick Winfield on 2006-12-01, tagged pager  pagination  propel 
(1 comment)

Sub-selects using Propel

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
  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);
by Greg Militello on 2006-08-17, tagged criteria  propel  query  sql 
(4 comments)

Query objects between two dates with Criteria

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);
by Francois Zaninotto on 2006-05-24, tagged criteria  date  propel 
(6 comments)

Use the model in a batch or a test

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();
by Fabien Potencier on 2006-05-21, tagged batch  configuration  propel  test 
(2 comments)

Complement complex SQL in SYMFONY

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
by William Duan on 2007-01-07, tagged model  propel  sql 

Use InnoDB with Propel

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
by Fabien Potencier on 2006-05-20, tagged mysql  propel 

Customise propel getters and setters

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

by Olivier Verdier on 2006-07-17, tagged foreignkey  getter  propel  setter 

object_select_tag related classes

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'
)) ?>
by Alistair Stead on 2006-06-20, tagged forms  objects  propel 
(7 comments)

Use transaction in the model

$con = Propel::getConnection();
try
{
  $con->begin();
 
  // do something
 
  $con->commit();
}
catch (Exception $e)
{
  $con->rollback();
  throw $e;
}
by Fabien Potencier on 2006-05-20, tagged propel  transaction 
(2 comments)

How to get a propel pager working with a custom SQL query

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
}
by Olivier Verdier on 2006-05-25, tagged custom  pager  pagination  propel  query  raw  sql 

Load data batch

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.

by Francois Zaninotto on 2006-05-20, tagged batch  data  propel 
(4 comments)

Select a database dynamically

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.

by Darren Schreiber on 2006-06-22, tagged connection  database  dynamic  multiple  propel  select 
(1 comment)

update according to the primary key using propel

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.

by sampq on 2007-12-05, tagged criteria  propel 
(1 comment)

Get a propel record set via associative

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);
            }
        }
    } 
}
 
by Kum Sackey on 2007-09-20, tagged associative  custom  propel  query 

Unit testing with Propel

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()');
by Jordi Backx on 2007-08-07, tagged database  propel  test  unit 
(1 comment)

How to use the admin generator along with propel inheritance

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.

by Olivier Verdier on 2006-05-25, tagged admin  generator  inheritance  propel 
(2 comments)

Little trick to randomize results

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

by Romain Dorgueil on 2006-05-29, tagged column  criteria  database  order  propel  random 
(8 comments)

Executing a MySQL stored procedure

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 Marcel van Leeuwen on 2008-04-18, tagged model  mysqli  propel  sql 
(1 comment)

exclude tables from propel-build-schema

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";
 
by Kostas Papadimitriou on 2007-12-15, tagged creole  database  generation  propel  propelbuildschema  schema 

default culture content fallback for i18n tables

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;
    }
  }
 
by Pierre-Yves Landuré on 2007-10-10, tagged culture  data  database  i18n  object  propel 
(4 comments)

Fetch database connection

$connection = sfContext::getInstance()->getDatabaseConnection('propel');
by Dustin Whittle on 2006-05-22, tagged creole  database  propel 
(2 comments)

nonHydratingPager class (a pager for when you dont want to hydrate your propel object)

<?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) {}
 
}
by noel on 2007-02-21, tagged nohydrate  nonhydrating  nonhydratingpager  pager  pagination  propel 
(4 comments)

Change database connection settings dynamically

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.

by Francois Zaninotto on 2006-06-20, tagged connection  database  propel 
(1 comment)

Collate in propel sort

$c->addAscendingOrderByColumn( 'NAME COLLATE utf8_bin' );

quite obvious

by karol _ on 2011-06-13, tagged criteria  orderby  propel  sort 

Executing MySQL stored prodcedure in Symfony 1.2

I've written this code in my module action to get resultset returned by a stored procedure

$sql = sprintf('CALL sp_name(%s)', "parameter_value");      
 
$con_pdo = Propel::getConnection();
 
$result_pdo_stat = $con_pdo->query($sql);
 
$this->transactions = $result_pdo_stat->fetchAll(); //setting $transactions for my template
 

Please let me know if you find it useful

by shaheedulhaq sadi on 2010-06-24, tagged propel  storedprodcedure  symfony 
(1 comment)

Dump fixtures using Criteria

In case you have a populated database and you need to generate a fixtures for it, you can use symfony propel:data-dump tasks or manually call methods of sfPropelData.

But If your database contains too much data to be fetched, this methods will not work for you.

For such cases I have extended sfPropelData to use Propel Criteria objects to filter dumped data. For example, you can set limit 30 records for every model to prevent dumping all records from database.

Usage:

You can create special tasks to generate fixtures and use there class listed below

$data = new fxPropelData();
 
// Defining criterias
$c1 = new Criteria();
$c1->setLimit(30);
$c2 = clone($c1);
$c2->add('IS_DELETED',0);
 
// array with models and criterias for them
    $models = array(
      'Fruit'=> $c1,
      'Vegetable' => $c2,
    );
 
$data->dumpDataWithCriteria(sfConfig::get('sf_data_dir').'/fixtures/food.yml',$models, $options['connection']);
 

class fxPropelData

class fxPropelData extends sfPropelData {
 
  public function dumpDataWithCriteria($directoryOrFile, $models = array(), $connectionName = null) {
 
    $dumpData = $this->getDataWithCriteria($models, $connectionName);
 
    // save to file(s)
    if (!is_dir($directoryOrFile)) {
      file_put_contents($directoryOrFile, sfYaml::dump($dumpData, 3));
    }
    else {
      $i = 0;
      foreach ($models as $tableName => $criteria) {
        if (!isset($dumpData[$tableName])) {
          continue;
        }
 
        file_put_contents(sprintf("%s/%03d-%s.yml", $directoryOrFile, ++$i, $tableName), sfYaml::dump(array($tableName => $dumpData[$tableName]), 3));
      }
    }
  }
 
  protected function getDataWithCriteria($models, $connectionName = 'propel') {
    $this->loadMapBuilders();
    $this->con = Propel::getConnection($connectionName);
    $this->dbMap = Propel::getDatabaseMap($connectionName);
 
 
    $dumpData = array();
 
    foreach ($models as $model => $criteria) {
      $tableName = $model;
 
      $tableMap = $this->dbMap->getTable(constant(constant($tableName.'::PEER').'::TABLE_NAME'));
      $hasParent = false;
      $haveParents = false;
      $fixColumn = null;
      foreach ($tableMap->getColumns() as $column) {
        $col = strtolower($column->getName());
        if ($column->isForeignKey()) {
          $relatedTable = $this->dbMap->getTable($column->getRelatedTableName());
          if ($tableName === $relatedTable->getPhpName()) {
            if ($hasParent) {
              $haveParents = true;
            }
            else {
              $fixColumn = $column;
              $hasParent = true;
            }
          }
        }
      }
 
      if ($haveParents) {
        // unable to dump tables having multi-recursive references
        continue;
      }
 
      // get db info
      $resultsSets = array();
      if ($hasParent) {
        $resultsSets[] = $this->fixOrderingOfForeignKeyDataInSameTableWithCriteria($resultsSets, $criteria, $tableName, $fixColumn);
      }
      else {
        $stmt = call_user_func(array($tableName.'Peer', 'doSelectStmt'),$criteria,$this->con);
        $resultsSets[] = array_merge($stmt->fetchAll(PDO::FETCH_ASSOC),$resultsSets);
        $stmt->closeCursor();
        unset($stmt);
      }
 
 
      foreach ($resultsSets as $rows) {
        if(count($rows) > 0) {
          if (!isset($dumpData[$tableName])) $dumpData[$tableName] = array();
 
          foreach ($rows as $row) {
            $pk = $tableName;
            $values = array();
            $primaryKeys = array();
            $foreignKeys = array();
 
            foreach ($tableMap->getColumns() as $column) {
              $col = $column->getName();
              $isPrimaryKey = $column->isPrimaryKey();
 
              if (is_null($row[$col])) {
                continue;
              }
 
              if ($isPrimaryKey) {
                $value = $row[$col];
                $pk .= '_'.$value;
                $primaryKeys[$col] = $value;
              }
 
              if ($column->isForeignKey()) {
                $relatedTable = $this->dbMap->getTable($column->getRelatedTableName());
                if ($isPrimaryKey) {
                  $foreignKeys[$col] = $row[$col];
                  $primaryKeys[$col] = $relatedTable->getPhpName().'_'.$row[$col];
                }
                else {
                  $values[$col] = $relatedTable->getPhpName().'_'.$row[$col];
 
                  $values[$col] = strlen($row[$col]) ? $relatedTable->getPhpName().'_'.$row[$col] : '';
                }
              }
              elseif (!$isPrimaryKey || ($isPrimaryKey && !$tableMap->isUseIdGenerator())) {
                // We did not want auto incremented primary keys
                $values[$col] = $row[$col];
              }
            }
 
            if (count($primaryKeys) > 1 || (count($primaryKeys) > 0 && count($foreignKeys) > 0)) {
              $values = array_merge($primaryKeys, $values);
            }
 
            $dumpData[$tableName][$pk] = $values;
          }
        }
      }
    }
 
    return $dumpData;
  }
 
  protected function fixOrderingOfForeignKeyDataInSameTableWithCriteria($resultsSets, $criteria, $tableName, $column, $in = null)
  {
    is_null($in) ? $criteria->add($column->getName(),NULL) : $criteria->add($column->getName(),$in,Criteria::IN);
 
    $stmt = call_user_func(array($tableName.'Peer', 'doSelectStmt'),$criteria,$this->con);
 
    $in = array();
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC))
    {
      $in[] = "'".$row[strtolower($column->getRelatedColumnName())]."'";
      $resultsSets[] = $row;
    }
 
    if ($in = implode(', ', $in))
    {
      $resultsSets = $this->fixOrderingOfForeignKeyDataInSameTableWithCriteria($resultsSets, $tableName, $column, $in);
    }
 
    return $resultsSets;
  }
 
 
}
 
by Davert on 2009-12-11, tagged creiteria  fixtures  functional  propel  test  unit 

Verify form field matches existing field: sfValidatorPropelExisting

This snippet may be used as a post-validator to verify that a column exists within a Propel table. It's a trivial derivative of the sfValidatorPropelUnique (so much so I left the copyrights in place). It's strictly a that validator with the logic reversed.

/*
 * This file is part of the symfony package.
 * (c) Fabien Potencier <fabien.potencier@symfony-project.com>
 *
 * For the full copyright and license information, please view the LICENSE
 * file that was distributed with this source code.
 */
 
/**
 * sfValidatorPropelExisting validates that the uniqueness of a column.
 *
 * Warning: sfValidatorPropelExisting is susceptible to race conditions.
 * To avoid this issue, wrap the validation process and the model saving
 * inside a transaction.
 *
 * @package    symfony
 * @subpackage validator
 * @author     Fabien Potencier <fabien.potencier@symfony-project.com>
 * @version    SVN: $Id: sfValidatorPropelExisting.class.php 13249 2008-11-22 16:10:11Z fabien $
 */
class sfValidatorPropelExisting extends sfValidatorSchema
{
  /**
   * Constructor.
   *
   * @param array  $options   An array of options
   * @param array  $messages  An array of error messages
   *
   * @see sfValidatorSchema
   */
  public function __construct($options = array(), $messages = array())
  {
    parent::__construct(null, $options, $messages);
  }
 
  /**
   * Configures the current validator.
   *
   * Available options:
   *
   *  * model:              The model class (required)
   *  * column:             The unique column name in Propel field name format (required)
   *                        If the uniquess is for several columns, you can pass an array of field names
   *  * field               Field name used by the form, other than the column name
   *  * primary_key:        The primary key column name in Propel field name format (optional, will be introspected if not provided)
   *                        You can also pass an array if the table has several primary keys
   *  * connection:         The Propel connection to use (null by default)
   *  * throw_global_error: Whether to throw a global error (false by default) or an error tied to the first field related to the column option array
   *
   * @see sfValidatorBase
   */
  protected function configure($options = array(), $messages = array())
  {
    $this->addRequiredOption('model');
    $this->addRequiredOption('column');
    $this->addOption('field', null);
    $this->addOption('primary_key', null);
    $this->addOption('connection', null);
    $this->addOption('throw_global_error', false);
 
    $this->setMessage('invalid', 'An object with the same "%column%" already exist.');
  }
 
  /**
   * @see sfValidatorBase
   */
  protected function doClean($values)
  {
    if (!is_array($values))
    {
      throw new InvalidArgumentException('You must pass an array parameter to the clean() method (this validator can only be used as a post validator).');
    }
 
    if (!is_array($this->getOption('column')))
    {
      $this->setOption('column', array($this->getOption('column')));
    }
 
    if (!is_array($field = $this->getOption('field')))
    {
      $this->setOption('field', $field ? array($field) : array());
    }
    $fields = $this->getOption('field');
 
    $criteria = new Criteria();
    foreach ($this->getOption('column') as $i => $column)
    {
      $name = isset($fields[$i]) ? $fields[$i] : $column;
      if (!array_key_exists($name, $values))
      {
        // one of the column has be removed from the form
        return $values;
      }
 
      $colName = call_user_func(array(constant($this->getOption('model').'::PEER'), 'translateFieldName'), $column, BasePeer::TYPE_FIELDNAME, BasePeer::TYPE_COLNAME);
 
      $criteria->add($colName, $values[$name]);
    }
 
    $object = call_user_func(array(constant($this->getOption('model').'::PEER'), 'doSelectOne'), $criteria, $this->getOption('connection'));
 
    // if no object or if we're updating the object, it's ok
    if (!is_null($object) && !$this->isUpdate($object, $values))
    {
      return $values;
    }
 
    $error = new sfValidatorError($this, 'invalid', array('column' => implode(', ', $this->getOption('column'))));
 
    if ($this->getOption('throw_global_error'))
    {
      throw $error;
    }
 
    $columns = $this->getOption('column');
 
    throw new sfValidatorErrorSchema($this, array($columns[0] => $error));
  }
 
  /**
   * Returns whether the object is being updated.
   *
   * @param BaseObject  $object   A Propel object
   * @param array       $values   An array of values
   *
   * @return Boolean     true if the object is being updated, false otherwise
   */
  protected function isUpdate(BaseObject $object, $values)
  {
    // check each primary key column
    foreach ($this->getPrimaryKeys() as $column)
    {
      $columnPhpName = call_user_func(array(constant($this->getOption('model').'::PEER'), 'translateFieldName'), $column, BasePeer::TYPE_FIELDNAME, BasePeer::TYPE_PHPNAME);
      $method = 'get'.$columnPhpName;
      if (!isset($values[$column]) or $object->$method() != $values[$column])
      {
        return false;
      }
    }
 
    return true;
  }
 
  /**
   * Returns the primary keys for the model.
   *
   * @return array An array of primary keys
   */
  protected function getPrimaryKeys()
  {
    if (is_null($this->getOption('primary_key')))
    {
      $primaryKeys = array();
      $tableMap = call_user_func(array(constant($this->getOption('model').'::PEER'), 'getTableMap'));
      foreach ($tableMap->getColumns() as $column)
      {
        if (!$column->isPrimaryKey())
        {
          continue;
        }
 
        $primaryKeys[] = call_user_func(array(constant($this->getOption('model').'::PEER'), 'translateFieldName'), $column->getPhpName(), BasePeer::TYPE_PHPNAME, BasePeer::TYPE_FIELDNAME);
      }
 
      $this->setOption('primary_key', $primaryKeys);
    }
 
    if (!is_array($this->getOption('primary_key')))
    {
      $this->setOption('primary_key', array($this->getOption('primary_key')));
    }
 
    return $this->getOption('primary_key');
  }
}
 
by Mike Crowe on 2009-10-24, tagged propel  validator 

Default culture content for i18n table

The table i18n doesn't support default culture. This snippet add the support of the default culture.

This snippet is a port of the snippet "default culture content fallback for i18n tables" for Symfony 1.2

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.
   *
   * @param   string          $culture    The culture to set
   * @return  mixed                       A i18n object
   * @throws  PropelException             Any exceptions caught during processing will be rethrown wrapped into a PropelException.
   * @link    http://snippets.symfony-project.org/snippet/237 -- modified for Symfony 1.2
   */
  public function getCurrentTableClassNameI18n($culture = null)
  {
    if (is_null($culture)) {
      $culture = is_null($this->culture) ? sfPropel::getDefaultCulture() : $this->culture;
    }
 
    if (!isset($this->current_i18n[$culture]))
    {
      $obj = TableClassNameI18nPeer::retrieveByPK($this->getId(), $culture);
      if ($obj !== null) { // Test if there is a translation for current culture
        $this->setTableClassNameI18nForCulture($obj, $culture);
      } else { // Create a translation for this culture
        $new_i18n = new TableClassNameI18n();
 
        $default_culture = sfConfig::get('sf_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 !== null) { // Test if there is a translation for current culture
            $this->setTableClassNameI18nForCulture($obj, $default_culture);
          }
        } else {
          $obj = $this->current_i18n[$default_culture];
        }
 
        if ($obj !== null) {
          $obj->copyInto($new_i18n);
        }
 
        $new_i18n->setId($this->getId());
        $new_i18n->setCulture($culture);
 
        $this->setTableClassNameI18nForCulture($new_i18n, $culture);
      }
    }
 
    return $this->current_i18n[$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()
   * @link       http://snippets.symfony-project.org/snippet/237 -- modified for Symfony 1.2
   */
  protected function doSave(PropelPDO $con)
  {
    $default_culture = sfConfig::get('sf_default_culture');
    $current_culture = is_null($this->culture) ? sfPropel::getDefaultCulture() : $this->culture;
    $obj = null;
 
    // 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 !== null) { // Test if there is a translation for current culture
        $this->setTableClassNameI18nForCulture($obj, $default_culture);
      }
    } else {
      $obj = $this->current_i18n[$default_culture];
    }
 
    if($obj === null && isset($this->current_i18n[$current_culture])) {
      $new_i18n = new TableClassNameI18n();
      $this->current_i18n[$current_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   The criteria's object
   * @param     string           $culture    The selected culture.
   * @param     PropelPDO        $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.
   * @link      http://snippets.symfony-project.org/snippet/237 -- modified for Symfony 1.2
   */
  public static function doSelectWithI18n(Criteria $criteria, $culture = null, PropelPDO $con = null)
  {
    $criteria = clone $criteria;
 
    if ($culture === null) {
      $culture = sfContext::getInstance()->getUser()->getCulture();
    }
 
    $default_culture = sfConfig::get('sf_default_culture');
 
    // Set the correct dbName if it has not been overridden
    if ($criteria->getDbName() == Propel::getDefaultDB()) {
      $criteria->setDbName(self::DATABASE_NAME);
    }
 
    TableClassNamePeer::addSelectColumns($c);
    $startcol = (TableClassNamePeer::NUM_COLUMNS - TableClassNamePeer::NUM_LAZY_LOAD_COLUMNS) + 1;
 
    TableClassNameI18nPeer::addSelectColumns($c);
 
    $criteria->addJoin(TableClassNamePeer::ID, TableClassNameI18nPeer::ID);
    $criterion = $criteria->getNewCriterion(TableClassNameI18nPeer::CULTURE, $culture);
    $criterion->addOr($criteria->getNewCriterion(TableClassNameI18nPeer::CULTURE, $default_culture));
    $criteria->add($criterion);
 
    $stmt = BasePeer::doSelect($c, $con);
    $results = array();
    $uncultured_results = array();
 
    while($row = $stmt->fetch(PDO::FETCH_NUM)) {
 
      $obj1 = new TableClassName();
      $obj1->hydrate($row);
      $obj1->setCulture($culture);
 
      if(isset($results[$obj1->getId()])) {
        $obj1 = $results[$obj1->getId()];
      }
 
      $omClass = TableClassNameI18nPeer::getOMClass($row, $startcol);
 
      $cls = Propel::importClass($omClass);
      $obj2 = new $cls();
      $obj2->hydrate($row, $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

Before enable the fallback, you must create a constant in your table object peer class in lib/model/TableClassNamePeer.php and replace table by your table name

const COUNT_DISTINCT = 'COUNT(DISTINCT table.ID)';
 

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   The criteria's object
   * @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.
   * @link        http://snippets.symfony-project.org/snippet/237 -- modified for Symfony 1.2
   */
  public static function doCountWithI18n(Criteria $criteria = null, $distinct = false, PropelPDO $con = null, $culture = null)
  {
    // we're going to modify criteria, so copy it first
    if ($criteria === null){
      $criteria = new Criteria();
    } else {
      $criteria = clone $criteria;
    }
 
    $default_culture = sfConfig::get('sf_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::doSelectStmt($criteria, $con);
    if ($res = $rs->fetchColumn(0)) {
      return $res;
    } else {
      // no rows returned; we infer that means 0 matches.
      return 0;
    }
  }
 
by Simon Leblanc on 2009-09-20, tagged culture  data  database  i18n  object  propel  symfony12 

performing custom joins and hydrations with Propel

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:

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.

by Leon van der Ree on 2009-06-07, tagged criteria  custom  database  hydrate  join  model  propel  sfpropelhelperplugin 
(1 comment)

Custom Validator for multiple checkboxes

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.

by Volker on 2009-03-04, tagged array  checkboxes  choice  propel  validator 

Thumbnail with propel

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);
  }
}
 
by Simon Bonjean on 2009-03-01, tagged propel  sfthumbnail  thumb  thumbnail 

myPakeTransformSchemaSfguard.php

or apply transformation on sfGuard tables into your main schema

Problem

When you generate a schema with sf_guard tables from your main database with the following command, the schema contains all the tables included the sf_guard tables (it's normal).

symfony propel-build-schema xml
 

Then after, when you build the model classes, the sfGuardPlugin classes have a wrong name and are in a wrong place (lib/). There is a post on this problem, how to correctly use propel-build-schema after sfGuardPlugin installation?

symfony propel-build-model
 

Solution

To correct the schema, apply the transformation on the XML schema after the schema generation :

symfony transform-schema-sfguard xml
 

The script add in the schema for all sf_guard_* tables, the package and the phpName with the camelcase used by the plugin.

Also you have to switch off the schema in the plugin to don't have twice declaration :

mv ./plugins/sfGuardPlugin/config/schema.yml ./plugins/sfGuardPlugin/config/schema.yml.off
 

The script: myPakeTransformSchemaSfguard.php

pake_desc( 'apply transformation on sfGuard tables into your main schema' );
pake_task( 'transform-schema-sfguard', 'project_exists' );
 
function run_transform_schema_sfguard($task, $args) 
{
  // Check params
  // -- missing params ?
  if ( !count($args) > 1 ) {
    throw new Exception("You must provide a transformation to apply.\nsymfony transform-schema-sfguard\nsymfony transform-schema-sfguard xml");
  }
 
  // -- schema exists ?
  if ($args[0] == 'xml')
  {
    $schema_filename = sprintf( '%s/schema.xml', sfConfig::get('sf_config_dir') );
    if ( !file_exists($schema_filename) ) {
        throw new Exception( "Missing schema.xml" );
    }
  }
  else
  {
    $schema_filename = sprintf( '%s/schema.yml', sfConfig::get('sf_config_dir') );
    if ( !file_exists($schema_filename) ) {
        throw new Exception( "Missing schema.yml (not yet implemented)" );
    } else {
        throw new Exception( "schema.yml not yet implemented" );
    }
  }
 
  // Backup schema
  //pake_copy( $schema_filename, $schema_filename . '.previous', array('override' => true) );
 
 
  if ($args[0] == 'xml')
  {
    $handle = fopen($schema_filename, "r");
 
    // get the entire file in $contents
    $contents = '';
    while (!feof($handle))
    {
      $contents.= fread($handle, 8192);
    }
    fclose($handle);
 
    $num = preg_match_all('/<table(.*)>/i', $contents, $matches);
 
    // each table definition found
    foreach ($matches[0] as $val)
    {
      if (!preg_match('/package|phpName/i', $val) && preg_match('/name="(sf_guard_.+?)"/i', $val, $val_matches)) {
 
        $table_name = $val_matches[1];
 
        //$php_name   = sfInflector::camelize($val_matches[1]);
        $php_name = sfToolkit::pregtr($val_matches[1], array('#/(.?)#e'   => "'::'.strtoupper('\\1')",
                                                             '/(_)(.)/e'  => "strtoupper('\\2')",
                                                             '/(^)(.)/e'  => "strtolower('\\2')"));
 
        $pattern    = '/(<table.*)(name="'.$table_name.'")(.*>)/i';
        $replace    = '$1$2 package="plugins.sfGuardPlugin.lib.model" phpName="'.$php_name.'" $3';
        $contents   = preg_replace($pattern, $replace, $contents, 1, $count);
 
        pake_echo($table_name);
      } 
    }
 
    // write the result
    $handle = fopen($schema_filename, "w+");
    fwrite($handle, $contents);
    fclose($handle);
  }
}
 

Installation

Put the myPakeTransformSchemaSfguard.php script into data/tasks directory

Use

mv ./plugins/sfGuardPlugin/config/schema.yml ./plugins/sfGuardPlugin/config/schema.yml.off
 
symfony propel-build-schema xml
 
symfony transform-schema-sfguard xml
 
symfony propel-build-model
 
by Olivier LOYNET on 2008-04-09, tagged cli  pake  propel  schema  sfguard 

Listing sub-classes

When you use class inheritance in Propel, it can be quite useful to get a sorted array of all the descendant classes of any base class in your model.

First we need a simple method to determine whether a particular class inherits from a base class. We do this by ascending the class inheritance tree until we either a) find the base class we're looking for or b) reach the top of the tree

  function inheritsFrom($AClass,$ABaseClass)
  {
    $bIsSubClass=false;   
    $strClass=$AClass;                           
    while (($strClass!='') and !$bIsSubClass)
    {                                         
      $bIsSubClass=(get_parent_class($strClass)==$ABaseClass);
      $strClass=get_parent_class($strClass);                
    }
 
    return $bIsSubClass;     
  }  
 

Then we can extend the sfCore class, as shown below, to generate an array of sub-classes, that exist in the "model". I've added a couple of extra options, to allow class names to be converted to class key constants, and abstract classes to be ignored, if desired.

class EnhancedCore extends sfCore
{   
  public static function subClassesOf($AClass,$AKeyField='',$AIgnoreAbstractClasses=false)
  {
    $modelDir=sfConfig::get('sf_model_lib_dir');
    $intPathLength=strlen($modelDir);
    $arrClasses=array();    
    foreach(self::$classes as $strClass => $strPath)
    {
      //ignore classes outside the "model" library folder   
      if (substr($strPath,0,$intPathLength)==$modelDir)
      {       
        if (inheritsFrom($strClass,$AClass))
        {          
          $bIgnoreClass=false;
          if ($AIgnoreAbstractClasses)
          {
            $reflector=new ReflectionClass($strClass);
            $bIgnoreClass=$reflector->isAbstract();                        
          }
 
          if (!$bIgnoreClass)
          {
            $strKey=$strClass;
            if ($AKeyField!='')
            {
              $peerReflector=new ReflectionClass($AClass.'Peer');                      
              $strConstant=strtoupper($AKeyField).'_'.strtoupper($strClass);
              if (array_key_exists($strConstant,$peerReflector->getConstants()))
              {           
                $strKey=$peerReflector->getConstant($strConstant);
              }               
            }
 
            $arrClasses[$strKey]=$strClass;             
          }
        }        
      }    
    }
 
    asort($arrClasses);     
    return $arrClasses;
  }
}
 

So how about a simple example, in order to demonstrate its use. Let's say we have a schema.xml file like so:

<table name="person" 
    <column name="id" type="INTEGER" required="true" autoIncrement="true" primaryKey="true" />
    <column name="name" type="VARCHAR" size="50" required="true"/>            
    <column name="classkey" type="INTEGER" required="true" inheritance="single">    
      <inheritance key="1" class="Employee" extends="person"/>
      <inheritance key="2" class="Customer" extends="person"/>
      <inheritance key="3" class="Manager" extends="Employee"/>                        
    </column>
  </table>  
 

Symfony produces one base class (Person) and one peer class (PersonPeer) from this schema, as well as three sub-classes (Employee,Customer,Manager). Then the following simple code

$classes=EnhancedCore::subClassesOf('Person','classkey'); 
var_dump($classes) 
 

outputs >>>

array(3) {
    [2]=>
    string(8) "Customer"
    [1]=>
    string(8) "Employee"
    [3]=>
    string(7) "Manager"
    }
 
by Todd Martin on 2008-01-21, tagged inheritance  propel  subclasses 

propel behavior to extend classes with separate tables

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" end a subclass "subElement" :

by whoknows on 2007-08-13, tagged behavior  inheritance  model  propel 

Doctrine import from propel improvement

Sometimes a table needs to have non-autoincremented primary keys (for example, to be able to import data from other sources keeping the same id's for objects).

I use the following procedure:

  1. DBDesigner -> Propel schema (config/schema.xml) through DBDesigner2Propel

  2. Generate Doctrine schema (config/doctrine/schema.yml) through symfony doctrine-import.

The problem is that the resulting schema.yml no longer includes information about the primary keys, so doctrine-insert-sql and doctrine-build-model will create an id field as autoincremented primary key.

The solution is a small patch which doesn't change the default behaviour. Look at sfDoctrineSchemaPropelLoader.class.php around the line 52 and modify a few lines in the code to be as the following:

// columns
foreach ($propelDatabaseSchema->getChildren($tableDesc) as $col_name => $columnDescription)
{
  $docCol = new sfDoctrineColumnSchema($col_name, $columnDescription, true);
 
  if($col_name == 'id') {
          if($docCol->getColumnInfo()->get('autoIncrement') === true) {
                  // We skip integer auto-increment primary keys, but we keep the rest
                  continue;
          }
  }
  $class->addColumn($docCol);
}

There is a bug in sfDoctrineColumnSchema.class.php which should be fixed too. The problem is that it is intersecting with the non-translated constraints instead the ones already translated from propel.

The fix is easy:

Original line:

    if ($constraints = array_intersect_key($columnDescription, array_flip(self::$allowedConstraints)))

Change to this:

    // Change to this:
    if ($constraints = array_intersect_key($this->columnInfo->getAll(), array_flip(self::$allowedConstraints)))
by Fernando Monera on 2007-08-07, tagged autoincrement  convert  dbdesigner  doctrine  import  primary  propel 

Double List for ManyToMany relationships (deprecated)

I needed the object_admin_double_list helper of the admin generator for my regular projects (without using admin generator). So I ported the admin_double_list helper a bit and now I want to publish this for you.

The admin_double_list helper is for selecting multiple items from a pool of items using ManyToMany relationships. For example to associate a user to multiple groups.

The helper itself has two parts: - two helper functions - two javascript functions

After that I have an example how the three controller, view and model parts handle this helper.

The snippet itself

Helper file

/**
 * two multiline select tags with associated and unassociated items
 *
 * @return string
 * @param object object
 * @param string method of object
 * @param array options
 * @param array html options of select tags
 **/
function double_list($object, $method, $options = array(), $html_options = array())
{
  $options = _parse_attributes($options);
 
  // get the lists of objects
  list($all_objects, $objects_associated, $associated_ids) = _get_object_list($object, $method, _get_option($options, 'through_class'));
 
  // options
  $html_options['multiple'] = _get_option($html_options, 'multiple', true);
  $html_options['size'] = _get_option($html_options, 'size', 10);
  $html_options['class'] = 'double_list';
 
  $label_assoc = _get_option($options, 'associated_label', 'Zugehörige Gruppen');
  $label_all   = _get_option($options, 'unassociated_label', 'Gruppenliste');
  $name1 = _get_option($options, 'associated', 'associated');
  $name2 = _get_option($options, 'unassociated', 'unassociated');
  $form = _get_option($options, 'form_id', 'editForm');
 
  // unassociated objects
  $objects_unassociated = array();
  foreach ($all_objects as $object)
  {
    if (!in_array($object->getPrimaryKey(), $associated_ids))
      $objects_unassociated[] = $object;
  }
 
  // select tags
  $select1 = select_tag($name1, options_for_select(_get_options_from_objects($objects_associated), '', $options), $html_options);
  unset($html_options['class']);
  $select2 = select_tag($name2, options_for_select(_get_options_from_objects($objects_unassociated), '', $options), $html_options);
 
  // output skeloton
  $html =
'<div style="float:left; padding-right: 20px;">
  <label for="%s">%s</label>
  %s
</div>
<div class="float:left; padding-right: 20px; padding-top: 20px">%s<br />%s</div>
<div class="float:left;">
  <label for="%s">%s</label>
  %s
</div>
<div style="clear:both"></div>';
 
  // include js library
  $response = sfContext::getInstance()->getResponse();
  $response->addJavascript('/js/double_list.js', 'last');
 
  return sprintf($html,
      $name1, $label_assoc, $select1,
      link_to_function(image_tag('resultset_previous'), "double_list_move(\$('{$name2}'), \$('{$name1}'))"),
      link_to_function(image_tag('resultset_next'), "double_list_move(\$('{$name1}'), \$('{$name2}'))", 'style=display:block'),
      $name2, $label_all, $select2,
      $form
    );
}
 
/**
 * retrieve object list via propel
 *
 * @return array
 * @param object root object
 * @param string retrieving method
 * @param string name of satellite class
 **/
function _get_object_list($object, $method, $middleClass)
{
  // get object
  $object = $object instanceof sfOutputEscaper ? $object->getRawValue() : $object;
 
  // get all objects
  $objects = sfPropelManyToMany::getAllObjects($object, $middleClass);
  // get related objects
  $objects_associated = sfPropelManyToMany::getRelatedObjects($object, $middleClass);
  // get ids
  $ids = array_map(create_function('$o', 'return $o->getPrimaryKey();'), $objects_associated);
 
  return array($objects, $objects_associated, $ids);
}
 

You probably want to modify the look of the list using css (like I do). So you can change the $js variable like you want f.e. adding class names. And you perhaps also want to change the image paths (I used two icons of the famfamfam icon library).

The javascript

Put the code below in a file called double_list.js in your js directory. (For individual path and file name, modify the double_list helper, search for $response)

function double_list_move(src, dest)
{
  for (var i = 0; i < src.options.length; i++)
  {
    if (src.options[i].selected)
    {
      dest.options[dest.length] = new Option(src.options[i].text, src.options[i].value);
      src.options[i] = null;
      --i;
    }
  }
}
 
function double_list_submit()
{
  // get all selects with double list class
    selects = $$('select.double_list');
 
    selects.each(function(element){
        for (var i = 0; i < element.options.length; i++)
            element.options[i].selected = true;
    });
 
    return true;
}
 

Example

I would like to show an example how to handle this helper in the three patterns.

The example is easy. Assigning an user to many groups.

Model layer

We have to build a table which handles the ManyToMany relationship.

user_group:
  _attributes:
    phpName:    UserGroup
  group_id:
    type:       integer
    primaryKey: true
    foreignTable:groups
    foreignReference:id
    onDelete:   cascade
  user_id:
    type:       integer
    primaryKey: true
    foreignTable:users
    foreignReference:id
    onDelete:   cascade
 

Don't forget to rebuild all db stuff and to clear the cache.

Presentation layer

Now we display the double_list:

<?php echo double_list($user, 'getUserGroups', 'through_class=UserGroup associated=groups unassociated=not_groups associated_label=Associated Groups unassociated_label=Group list') ?>
 

The first parameter is the user object, than the method of the object retrieving the UserGroup records. I think the options are clear.

Controller layer

At last we have to save the selection of the user. Before doing this we have to delete all UserGroup objects of the user, because we would assign it twice, if the item was selected before.

// clear group data to save it again
$c = new Criteria();
$c->add(UserGroupPeer::USER_ID, $user->getId());
UserGroupPeer::doDelete($c);
 
// save groups
$groups = $this->getRequestParameter('groups');
if ($groups)
{
  foreach ($groups as $id)
  {
    $group = new UserGroup();
    $group->setGroupId($id);
    $group->setUserId($user->getId());
    $group->save();
  }
}
 

It was a bit too long. As I said, this is a port of the original object_admin_double_list helper, which can be practically only used with the Admin Generator.

Please check the snippet, because I use this in a little bit more customized version.

by Halil Köklü on 2007-07-04, tagged admin  form  propel 
(2 comments)

Tutorial: How to make symfony/propel use both Sqlite and Mysql or many database handles

Multiple databases is not really the strongest area of Propel when using symfony, for this it is better recomanded the use of sfDoctrine plugin.

Still, some propel-symfony based projects may require that one or many modules will have to use different database handles, and to do that, first you need to define each database into your database.yml file.

You can have as many dbs you like and on any combination you want, but for this example we will have one mysql db and one sqlite db.

Your database.yml file should look like this:

all:

  database1:
    class:                sfPropelDatabase
    param:
      phptype:            mysql
      hostspec:           localhost
      database:           mysql_databse
      username:           my_username

  database2:
    class:                sfPropelDatabase
    param:
      phptype:            sqlite
      database:           ../data/sqlite_database.db

Once you have the databases defined you need to write the schema for each database you have defined and with this step you can decide which table (model) will use which handle.

In our case we'll have two schema.yml files because we have two dbs and they will look like this:

database1.schema.yml

---
database1:

  table_1:
    _attributes: 
      idMethod: native
    id: 
      type: INTEGER
      required: true
      autoIncrement: true
      primaryKey: true
    value: 
      type: VARCHAR
      size: 32
      required: true

  table_2: 
    _attributes: 
      idMethod: native
    id: 
      type: INTEGER
      required: true
      autoIncrement: true
      primaryKey: true
    value: 
      type: VARCHAR
      size: 32
      required: true

database2.schema.yml

---
database2:

  table_3:
    _attributes: 
      idMethod: native
    id: 
      type: INTEGER
      required: true
      autoIncrement: true
      primaryKey: true
    value: 
      type: VARCHAR
      size: 32
      required: true

Now is time to build the models

$ symfony propel-build-model

If you want to use other symfony commands that uses the database directly like:

$ symfony propel-build-sql
$ symfony propel-insert-sql

You'll have to configure the dbs connection into propel.ini.

Here we have a problem because you can't use both databases with propel.ini, so you'll have to do this one at a time.

database1 on propel.ini

propel.database            = mysql
propel.database.createUrl  = mysql://localhost/
propel.database.url        = mysql://localhost/mysql_database

database2 on propel.ini

propel.database            = sqlite
propel.database.createUrl  = sqlite://./../data/sqlite_database.db
propel.database.url        = sqlite://./../data/sqlite_database.db

Once you have the models generated and the data loaded into your databases, you can play with your databases.yml file.

If you generate the databases for every handle you'll like, you can switch a model from a handle to another.

by Dorin Mirulescu on 2007-06-19, tagged database  propel 
(10 comments)

Autosetting Created_by and Updated_by fields in all models

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 autossetting Created_by and Updated_by fields (ao 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'
require_once 'sfObjectBuilder.php';
* @author     Boris Duin 
 */
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

for

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

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:

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());
    }
by whoknows on 2007-01-29, tagged model  propel 

Custom column selection and use of distinct using Propel Criteria

Here is an example of obtaining a custom set of select columns using Propel Criteria, as opposed to using a custom SQL statement. The use of distinct is also demonstrated.

// Set up empty select
$c = new Criteria();
$c->clearSelectColumns();
 
// Read distinct(owningdepot) from the load table...
$c->addSelectColumn(TmsLoadPeer::OWNINGDEPOT);
$c->addGroupByColumn(TmsLoadPeer::OWNINGDEPOT);
$c->setDistinct();
 
// ...which are IN the provided list
$c->add(TmsLoadPeer::LOAD_ID, $loadIds, Criteria::IN);
$depots = TmsLoadPeer::doSelectRS($c);
 
// Then return an array of depots
$arrDep = array();
foreach ($depots as $depot)
{
  $arrDep[] = $depot[0];
}
by halfer on 2006-08-23, tagged criteria  propel 

Making a specific Pake task quieter

We wanted to use a 'propel-insert-sql' in our acceptance tests suite to clear DB before every test reducing interferences. We all learned here http://www.symfony-project.com/snippets/snippet/16 how to call a Pake task from our PHP code. To get a quiet 'propel-insert-sql' task letting 'test' task be verbose and reporting test results we must add a method to the pakeTask class in pakeTask.class.php file:

public function setVerbose() 
{
  $this-&gt;verbose = false;
}

and edit sfPakePropel.php file to make 'propel-insert-sql' task quiet:

function run_propel_insert_sql($task, $args)
{
  $task-&gt;setVerbose();
  _call_phing($task, &#039;insert-sql&#039;);
}

This way we have a lot quiter acceptance test suite and a clean DB whenever we want.

by Jacopo Romei on 2006-07-31, tagged cli  pake  propel  test