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

If you have come across the need to use a helper that is from another module, with this function you can do so by specifying the helper in this format "my_module/HelperName". If you do not specify a module, it will look in the current module name.

function my_use_helper()
{
  $args = func_get_args();
 
  foreach($args AS $arg)
  {
    if( strstr($arg, '/') )
    {
      $moduleName = substr($arg, 0, strpos($arg, '/'));
      $helper = substr($arg, strpos($arg, '/') + 1, strlen($arg));
    } else {
      $moduleName = sfContext::getInstance()->getModuleName();
      $helper = $arg;
    }
 
    sfLoader::loadHelpers(array($helper), $moduleName);
  } 
}
 
// Usage
my_use_helper('my_module/HelperName');