$dateFormat = new sfDateFormat();
$value = $dateFormat->format(time(), 'I');
$user->set('last_login',$value);

Example Table static methods for retrieving data. Passing query objects to allow you to modify and reuse existing methods.

static function retrieveUsers($query = null)
{
  if( $query === null )
  {
    $query = new Doctrine_Query();
  }
 
  $query->select('u.*');
  $query->from('User u');
  $users = $query->execute();
 
  return $users;
}
 
static function retrieveUsersWithProfile($query)
{
  if( $query === null )
  {
    $query = new Doctrine_Query();
  }
 
  $query->select('p.*')
  $query->innerJoin('u.UserProfile p');
 
  return UserTable::retrieveUsers($query);
}
 
static function retrieveUserByUsername($username, $query = null)
{
   if( $query === null )
   {
     $query = new Doctrine_Query();
   }
 
   $query->where('u.username = ?', $username);
   $users = UserTable::retrieveUsers($query);
 
   return $users[0];
}

$query = new Doctrine_Query();
$query->select('c.*, COUNT(u.id) num_users'); // soon it may be possible to do COUNT(u.id) as num_users like in sql
$query->from('Church c');
$query->leftjoin('c.User u');
$query->orderby('c.name asc');
$query->groupby('c.id');
$churches = $query->execute();
 
foreach($churches AS $church)
{
   echo $church->name.' - '.$church->Users[0]->num_users.'<br/>';
}

That last part is tricky, no real way to know that from any of the documentation that num_users is in $church->users[0]->num_users.

$user = new User(); // new record
$user = Doctrine_Manager::getInstance()->getTable('User')->find(1); // existing record
 
$user['username'] = 'username';
$user->username = 'username';
$user->setUsername('username');
$user->set('username', 'username');
 
echo $user['username']; // username
echo $user->username; // username
echo $user->getUsername(); // username
echo $user->get('username'); // username

This is a model function with a field named avatar_id, that links to the avatar table, id field, and allows you to set the id based on the name of the avatar.

static function getAvatarByName($name)
{
  $query = new Doctrine_Query();
  $query->select('a.id');
  $query->from('Avatar a');
  $query->where('name = ?', $name);
  $avatar = $query->execute();
 
  return $avatar[0];
}
 
public function setAvatarIdByName($name)
{
  $avatar = AvatarTable::getAvatarByName($name);
  $this->setAvatarId($avatar->getId());
}

I wanted to add created_by and updated_by columns to my Doctrine schema. But the existing templates doesn't support that, so i've rewritten the Timestampable template.

Now Doctrine can automatically update these fields to the specific username oder userid.

config/doctrine/schema.yml:

ExampleUser:
  tableName: example_user
  columns:
    id:
      primary: true
      autoincrement: true
      type: integer(4)
    name:
      type: string(255)
    password:
      type: string(255)
  actAs:
    Userid:
      created:
        name: created_by
        type: integer
      updated:
        name: updated_by
        type: string
 

If you are writing integer as type the template will call $sf_user->getId(), if you write string it will call $sf_user->getUsername().

So the above example will write the ID to created_by column and the username to updated_by column.

lib/doctrine/Doctrine/Template/Userid.php:

<?php
/*
 *  $Id$
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
 * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
 * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
 * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
 * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
 * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
 * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
 * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
 * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
 * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 *
 * This software consists of voluntary contributions made by many individuals
 * and is licensed under the LGPL. For more information, see
 * <http://www.phpdoctrine.org>.
 */
 
/**
 * Doctrine_Template_Userid
 *
 * Easily add created and updated by ids or usernames to your doctrine records
 *
 * @package     Doctrine
 * @subpackage  Template
 * @license     http://www.opensource.org/licenses/lgpl-license.php LGPL
 * @link        www.phpdoctrine.org
 * @since       1.0
 * @version     $Revision$
 * @author      Thomas Boerger <tb@mosez.net>
 */
class Doctrine_Template_Userid extends Doctrine_Template
{
    /**
     * Array of userid options
     *
     * @var string
     */
    protected $_options = array(
        'created' =>  array(
            'name'          =>  'created_by',
            'type'          =>  'integer',
            'disabled'      => false,
            'expression'    => false,
            'options'       =>  array()
        ),
        'updated' =>  array(
            'name'          =>  'updated_by',
            'type'          =>  'integer',
            'disabled'      => false,
            'expression'    => false,
            'onInsert'      => true,
            'options'       =>  array()
        )
    );
 
    /**
     * __construct
     *
     * @param string $array.
     * @return void
     */
    public function __construct(array $options)
    {
        $this->_options = Doctrine_Lib::arrayDeepMerge($this->_options, $options);
    }
 
    /**
     * setTableDefinition
     *
     * @return void
     */
    public function setTableDefinition()
    {
        if(!$this->_options['created']['disabled']) {
            $this->hasColumn(
                $this->_options['created']['name'],
                $this->_options['created']['type'],
                null,
                $this->_options['created']['options']
            );
        }
        if(!$this->_options['updated']['disabled']) {
            $this->hasColumn(
                $this->_options['updated']['name'],
                $this->_options['updated']['type'],
                null,
                $this->_options['updated']['options']
            );
        }
        $this->addListener(new Doctrine_Template_Listener_Userid($this->_options));
    }
}
 

lib/doctrine/Doctrine/Template/Listener/Userid.php:

<?php
/*
 *  $Id$
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
 * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
 * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
 * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
 * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
 * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
 * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
 * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
 * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
 * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 *
 * This software consists of voluntary contributions made by many individuals
 * and is licensed under the LGPL. For more information, see
 * <http://www.phpdoctrine.org>.
 */
 
/**
 * Doctrine_Template_Listener_Userid
 *
 * @package     Doctrine
 * @subpackage  Template
 * @license     http://www.opensource.org/licenses/lgpl-license.php LGPL
 * @link        www.phpdoctrine.org
 * @since       1.0
 * @version     $Revision$
 * @author      Thomas Boerger <tb@mosez.net>
 */
class Doctrine_Template_Listener_Userid extends Doctrine_Record_Listener
{
    /**
     * Array of userid options
     *
     * @var string
     */
    protected $_options = array();
 
    /**
     * __construct
     *
     * @param string $options.
     * @return void
     */
    public function __construct(array $options)
    {
        $this->_options = $options;
    }
 
    /**
     * preInsert
     *
     * @param object $Doctrine_Event.
     * @return void
     */
    public function preInsert(Doctrine_Event $event)
    {
        if(!$this->_options['created']['disabled']) {
            $createdName = $this->_options['created']['name'];
            $event->getInvoker()->$createdName = $this->getUserId('created');
        }
 
        if(!$this->_options['updated']['disabled'] && $this->_options['updated']['onInsert']) {
            $updatedName = $this->_options['updated']['name'];
            $event->getInvoker()->$updatedName = $this->getUserId('updated');
        }
    }
 
    /**
     * preUpdate
     *
     * @param object $Doctrine_Event.
     * @return void
     */
    public function preUpdate(Doctrine_Event $event)
    {
        if( ! $this->_options['updated']['disabled']) {
            $updatedName = $this->_options['updated']['name'];
            $event->getInvoker()->$updatedName = $this->getUserId('updated');
        }
    }
 
    /**
     * getUserId
     *
     * Gets the userid or username depending on field format
     *
     * @param string $type.
     * @return void
     */
    public function getUserId($type)
    {
        $options = $this->_options[$type];
 
        if ($options['expression'] !== false && is_string($options['expression'])) {
            return new Doctrine_Expression($options['expression']);
        } elseif (!class_exists("pakeApp")) {
            switch($options['type']) 
            {
                case 'integer':
                    if (class_exists('sfGuardUser')) {
                      return sfContext::getInstance()->getUser()->getAttribute('user_id', null, 'sfGuardSecurityUser');
                    } else {
                      return sfContext::getInstance()->getUser()->getId();
                    }
                    break;
                case 'string':
                    return sfContext::getInstance()->getUser()->getUsername();
                    break;
                default:
                    return 'n/a';
                    break;
            }
        }
    }
}
 

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

It's very annoying that the pake tasks @doctrine-build-all@ and @doctrine-build-all-load@ don't work. So I've thought, why don't write a shell script which executes the single steps for me.

Just create a new file in your batch directory. For example 'build' or 'doctrine'

#!/usr/bin/env bash
 
symfony doctrine-drop-db <your app>
symfony doctrine-build-db <your app>
symfony doctrine-build-model <your app>
symfony doctrine-build-sql <your app>
symfony doctrine-insert-sql <your app>
 
php batch/<your load-data-batch-file>.php

// This is meant to be in actions.class.php as one of your actions. // Note how it redirects to your view action after saving.

public function executeSave() { $req = $this->getRequest();

$q = new Doctrine_Query();
$user = $q->from('UserInfo')->where('id = ?',$this->getRequestParameter('id'))->execute()->getFirst();
 
$user->merge($req->getParameterHolder()->getAll());
$user->save();
 
$this->redirect('user/view?did=save&id='.$user->id);

}