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.

Problem:

Mysql produce TINYINT fields because BOOLEAN is just a synonym for TINYINT. And if you have TINYINT fields with value 0 or 1 (like BOOLEAN fields) in your database and use command symfony propel-build-schema to generate schema, you will have one problem with admin generator, because it will produse TINYINT fields as text form fields with value - 0 or 1, not as checkboxes. Admin generator needs BOOLEAN fields in schema to produse checkboxes.

Solution:

Solution to automate transformation field type tinyint to boolean where field name prefix is "is_".

sfPakeTransformTinyint.php

<?php
pake_desc( 'apply tinyint-boolean transformation to your data model' );
pake_task( 'transform-schema-tinyint', 'project_exists' );
 
function run_transform_schema_tinyint( $task, $args ) 
{
 
    // Check params
    // -- missing params ?
    if ( !count($args) ) {
        throw new Exception( 'You must provide a transformation to apply.' );
    }
 
    // -- schema exists ?
    $schema_filename = sprintf( '%s/schema.xml', sfConfig::get('sf_config_dir') );
    if ( !file_exists($schema_filename) ) {
        throw new Exception( "Missing schema.xml" );
    }
 
    // Backup schema
    pake_copy( $schema_filename, $schema_filename . '.previous', array('override' => true) );
 
     //do hard work - tinyint->boolean
    if ($args[0] == 'do') {
        $handle = fopen($schema_filename, "r");
        $contents = '';
        while (!feof($handle)) {
          $contents .= fread($handle, 8192);
        }
        fclose($handle);
 
        $contents = preg_replace('/(name="is_.*?type=")TINYINT"/i','$1BOOLEAN"',$contents);
 
        $handle = fopen($schema_filename, "w+");
        fwrite($handle, $contents);
        fclose($handle);
    }
     //undo hard work - boolean->tinyint
    if ($args[0] == 'undo') {
        $handle = fopen($schema_filename, "r");
        $contents = '';
        while (!feof($handle)) {
          $contents .= fread($handle, 8192);
        }
        fclose($handle);
 
        $contents = preg_replace('/(name="is_.*?type=")BOOLEAN"/i','$1TINYINT"',$contents);
 
        $handle = fopen($schema_filename, "w+");
        fwrite($handle, $contents);
        fclose($handle);
    }
 
}
 
?>

copy this code and drop it as new file in SF_DATA_DIR/tasks/ use command: symfony transform-schema-tinyint do to change tinyint to boolean, where field name with "is_" prefix, than rebuild your model with propel-build-model, clear cache, and use admin generator with checkboxes.

If something going wrong, do this command to undo changes in your model:

symfony transform-schema-tinyint undo (to change boolean to tinyint, where field name with "is_" prefix)

or you may use schema.xml.previous <- this is your schema before transformation

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

PROJECT_DIR/config/databases.yml

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

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

PROJECT_DIR/config/database1.schema.xml

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

PROJECT_DIR/config/database2.schema.xml

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

Last, build model command.

$ symfony propel-build-model

Build complete.

Example, getting multiple databases connection handler.

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

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

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

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

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

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

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

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

  propel.builder.AddBehaviors = true
   

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

  >symfony propel-build-model
   

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

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

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

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

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

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

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

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

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

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

If you're like me, you hate having to populate your objects (from the model) with parameters from the request. Wouldn't it be nice to specify a little nugget of configuration, and then never worry about it again? Consider the following, which is tedious and boring:

Old Example

class mymoduleActions extends sfActions
{
  ...
 
  public function executeUpdate()
  {
    $employee = new Employee();
    $employee->setFirstName($this->getRequestParameter('first_name'));
    $employee->setLastName($this->getRequestParameter('last_name'));
    $employee->setSsn($this->getRequestParameter('ssn'));
    $employee->setDob($this->getRequestParameter('dob'));
    ...
    $this->employee = $employee;
  }
}

And the list goes on. Imagine, however, that you have the following in your module filters.yml file:

myPopulateObjectFilter:
  class: myPopulateObjectFilter
  param:
    use_database:  on
    model_object:  employee
    model_class:   Employee
    exclude:       [ssn]
    defaults:
      first_name:  example default name

If you had a filter that automatically created an object, and based upon the previous yaml, populated that object with data from the request (after a user hit submit on a form containing this information), then subsequently set that object for retrieval as a request attribute, you'd be a pretty happy person right?

New Example

class mymoduleActions extends sfActions
{
  ...
 
  public function executeUpdate()
  {
    $this->employee = $this->getRequest()->getAttribute('employee');
    ...
  }
}

Of course, you'd be free to do whatever you want with the results of the prepopulated object.

A great application would be an alternative to the fillin form filter. If you have your template set up to populate inputs from an object from the model, this filter can populate that object for you, and you can simply pass it on to the template.

The Code

<?php
 
/**
 * myPopulateObjectFilter
 * Uses configuration in filters.yml to create an object,
 * populate it with data from the request,
 * and set a request attribute with the result.
 *
 * @author  Stephen Riesenberg
 */
class myPopulateObjectFilter extends sfFilter
{
    protected
        $defaults       = null,
        $controller     = null,
        $request        = null;
 
    public function initialize($context, $parameters = array())
    {
        parent::initialize($context, $parameters);
 
        // get defaults from filters.yml (if any) and create new parameter holder to store them
        $this->defaults = new sfParameterHolder();
        $this->defaults->add($this->getParameter('defaults', array()));
 
        // get controller and request
        $this->controller   = $this->getContext()->getController();
        $this->request      = $this->getContext()->getRequest();
    }
 
    public function execute($filterChain)
    {
        // get request variable list
        $vars = $this->request->getParameterHolder()->getNames();
        $exclude = array_merge($this->getParameter('exclude', array()), array('module', 'action'));
        $vars = array_diff($vars, $exclude);
 
        $funcs = array();
        foreach ($vars as $var)
        {
            $funcs[$var] = 'set'.ucfirst(sfInflector::camelize($var));
        }
 
        // get model_object and model_class
        $object = $this->getParameter('model_object');
        $class = $this->getParameter('model_class');
 
        // fetch from the database or create the object to fill in
        if ($this->getParameter('use_database', false) && null !== ($id = $this->request->getParameter('id')))
        {
            $peer_class = $class . "Peer";
            $record = $peer_class::retrieveByPk($id);
        }
        else
        {
            $record = new $class();
        }
 
        // something like: array('id' => 'setId', 'my_field' => 'setMyField', ...)
        foreach ($funcs as $var => $func)
        {
            if (is_callable(array($record, $func)))
            {
                $record->$func($this->getValue($var));
            }
        }
 
        // set the updated record into a request attribute
        $this->request->setAttribute($object, $record);
 
        // execute next filter
        $filterChain->execute();
    }
 
    protected function getDefault($var)
    {
        return $this->defaults->get($var);
    }
 
    protected function getValue($var)
    {
        return $this->request->getParameter($var) != '' ? $this->request->getParameter($var) : $this->getDefault($var);
    }
}

NOTE: Place this in a file called myPopulateObjectFilter.class.php in the myproject/apps/myapp/lib/ directory.

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

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:

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

As enum is a MySQL specific type, you cannot have a column of type enum in your schema.yml (which is database-independent). One solution to simulate it is to create another table to store the different possible values. But the Model class offer an alternative solution that is probably more elegant.

Imagine you have a status column in you article table you want to be an enumerated list with values like (open, closed). Simply declare the status column as integer and then modify the model as follows:

In ArticlePeer.php:

class ArticlePeer...
{
   static protected $STATUS_INTEGERS = array('open', 'closed');
   static protected $STATUS_VALUES = null;
 
   static public function getStatusFromIndex($index)
   {
     return self::$STATUS_INTEGERS[$index];
   }
 
   static public function getStatusFromValue($value)
   {
     if (!self::$STATUS_VALUES)
     {
       self::STATUS_VALUES = array_flip(self::$STATUS_INTEGERS);
     }
 
     $values = strtolower($value);
 
     if (!isset(self::STATUS_VALUES[$value])
     {
       throw new PropelException(sprintf('Status cannot take "%s" as a value', $value));
     }
 
     return self::STATUS_VALUES[strtolower($value)];
   }
}

In Article.php:

class Article
{
   public function setStatusName($value)
   {
     $this->setStatus(ArticlePeer::getStatusFromValue($value));
   }
 
   public function getStatusName()
   {
     return ArticlePeer::getStatusFromIndex($this->getStatus());
   }
}

(Original tip from Fabien)

Features:

• dynamically enhance a base model object with getter methods

• dynamically populate a custom model object with getters from a custom query

• supports prepared statements

  Why? Building complex queries with Criteria is still hard (e.g. a 24-relation-join-query with functions, stored procedures etc. is still impossible to understand respectively hard to code). The snippet code takes the column names to build the setters and getters automatically. And! It's fast to code.

Example 1: ->doTree(0,200) : a model function that i wrote, returns a nested set result set.

Example 2: a simple custom query

Example 3: a custom prepared join query using sf_guard model tables

Example 4: a more complex prepared custom join query using sf_guard model tables

<?php
 
define('SF_ROOT_DIR',    realpath(dirname(__FILE__).'/..'));
define('SF_APP',         'backend');
define('SF_ENVIRONMENT', 'prod');
define('SF_DEBUG',       false);
 
require_once(SF_ROOT_DIR.DIRECTORY_SEPARATOR.'apps'.DIRECTORY_SEPARATOR.SF_APP.DIRECTORY_SEPARATOR.'config'.DIRECTORY_SEPARATOR.'config.php');
 
class CustomObject {
 
    private $objectName;
 
    public function __construct($name){
        $this->objectName = $name;
    }
 
    public function getObjectName(){
        return $this->objectName();
    }
 
    public function __call($funcname, $args = array()) {
        if (!function_exists($funcname)){
            $method = substr($funcname, 3);
            $methodType = substr($funcname, 0, 3);
            switch($methodType){
                case "set":
                    $this->{$method} = $args[0];
                    break;
                case "get":
                    return $this->{$method};
                    break;
            }
 
        } else {
            trigger_error("Call to Function with call_user_func_array failed", E_USER_ERROR);
        }       
    }
 
    public function __set($name, $value){
        $this->{$name} = $value;        
    }
 
    public function __get($name){
        return $this->{$name};
    }
 
}
 
class CustomQueryResultSet {
 
    private $columns;
    private $queryColumns;
    private $resultSet;
 
    public function __construct($callerObject = false, $className = false, $peerClassName = false) {
        if (is_object($callerObject) && !empty($className) && !empty($peerClassName)){          
            $fieldConstants = call_user_func(array($peerClassName, 'getFieldNames'), BasePeer::TYPE_FIELDNAME );
            $phpConstants = call_user_func(array($peerClassName, 'getFieldNames'), BasePeer::TYPE_PHPNAME );            
            foreach($callerObject as $key => $object){
                if(1===$key){
                    $this->queryColumns = array_keys($object);
                    $this->columns = array_merge( $phpConstants, array_diff($this->queryColumns, $fieldConstants ));
                }
                $this->populateCustomObject($object, $key, $className);
            }
        } elseif (is_object($callerObject) && empty($className) && empty($peerClassName)){          
            foreach($callerObject as $key => $object){
                if(1===$key){
                    $this->queryColumns = array_keys($object);
                    foreach($this->queryColumns as $name){
                        $this->columns[] = sfInflector::camelize($name);
                    }
                }
                $this->populateCustomObject($object, $key, "CustomQuery");
            }       
 
        } else {
            trigger_error("You lost the object", E_USER_ERROR);
        }
    }
 
    private function populateCustomObject($callerObject, $key, $className){
        $customObject = new CustomObject($className);
        foreach($this->queryColumns as $ckey => $columnName){
            $methodName = "set" . ucfirst($this->columns[$ckey]);
            $customObject->{$methodName}($callerObject[$columnName]); 
        }       
        $this->resultSet[$key-1] = $customObject;       
    }
 
    public function getResultSet(){
        return $this->resultSet;
    }
 
    public function getColumns(){
        return $this->columns;
    }
}
 
class CustomQueryObject {
 
    private $className;
    private $peerClassName;
    private $module;
    private $properties;
    private $caller;
    private $resultSet;
    private $customized = false;
 
    public function __construct($callerClassName = false) {
        if (class_exists($callerClassName)){
            $this->caller = new $callerClassName();
            $this->setClass($callerClassName);
            $this->setPeerClass($callerClassName);
        } else if(false===$callerClassName){
            $this->customized = true;
        }
    }
 
    public function __call($funcname, $args = array()) {
        if (false===$this->isCustomized() && is_object($this->caller) && function_exists('call_user_func_array')){
            $this->caller = call_user_func_array(array(&$this->caller, $funcname), $args);
            $resultSet = new CustomQueryResultSet($this->caller, $this->getClass(), $this->getPeerClass());
            return $resultSet->getResultSet();
        } else {
            trigger_error("Call to Function with call_user_func_array failed", E_USER_ERROR);
        }        
    }
 
    protected function isCustomized(){
        return $this->customized;
    }
 
    public function query($query){
        $connection = Propel::getConnection();
        $statement = $connection->createStatement();
        $this->caller = $statement->executeQuery( $query ); 
        $resultSet = new CustomQueryResultSet($this->caller);   
        return $resultSet->getResultSet();  
    }
 
    public function prepare($query, $parameters = array()){
        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        if(!empty($parameters)){
            $increment = 1;
            foreach($parameters as $parameter){
                foreach($parameter as $type => $value){
                    switch($type){
                        case "int":
                        case "integer":     
                            $statement->setInt($increment, $value);
                            break;
                        case "str":
                        case "string":  
                            $statement->setString($increment, $value);
                            break;
                        case "decimal":
                        case "float":
                            $statement->setFloat($increment, $value);
                            break;
                        case "bool":
                        case "boolean":
                            $statement->setBoolean($increment, $value);
                            break;
                        case "blob":
                            $statement->setBlob($increment, $value);
                            break;
                        case "cblob":
                            $statement->setClob($increment, $value);
                            break;
                        case "date":
                            $statement->setDate($increment, $value);
                            break;
                        case "time":
                            $statement->setTime($increment, $value);
                            break;
                        case "timestamp":
                            $statement->setTimestamp($increment, $value);
                            break;
                        case "array":
                            $statement->setArray($increment, $value);
                            break;
                        case "NULL":
                        case "null":
                            $statement->setNull($increment, $value);
                            break;
                        case "double":
                            $statement->setDouble($increment, $value);
                            break;
                    }
                    $increment++;
                }
            }
 
        }
        $this->caller = $statement->executeQuery(); 
        $resultSet = new CustomQueryResultSet($this->caller);   
        return $resultSet->getResultSet();  
    }
 
    public function getColumns(){
        return $this->resultSetColumns;
    }
 
 
    public function getPeerClass(){
        return $this->peerClassName;
    }
 
    public function setPeerClass($name){
        $this->peerClassName = $name . "Peer";
    }
 
    public function getClass(){
        return $this->className;
    }
 
    public function setClass($name){
        $this->className = $name ;
    }
}
 
?><html>
<body>
<p>
<?php
 
$case = 4;
switch($case){
    case 1:
        // executing a custom query from an existing model object 
        $custom = "sfNavigation";
        $customObject = new CustomQueryObject("sfNavigation");
        $objects = $customObject->doTree(0,50);
        foreach($objects as $key => $object){
            echo    $object->getComponentId() . " " .
                    $object->getCaption() . " " .
                    $object->getAction()  . " Level: " .
                    $object->getTreeLevel().  "<br>";
        }   
        break;
    case 2:
        //executing a custom query and create a custom object       
        $customObject = new CustomQueryObject();
        $objects = $customObject->query("SELECT * FROM md_components");
        foreach($objects as $key => $object){
            echo    $object->getComponentId() . " " .
                    $object->getCaption() . " " .
                    $object->getAction()  . "<br>";
        }
        break;
    case 3:
        // custom object with prepared statement 
        $customObject = new CustomQueryObject();
        $objects = $customObject->prepare("SELECT
u.username ,
g.id,
g.name
FROM
sf_guard_user AS u
Left Join sf_guard_user_group AS ug ON ug.user_id = u.id
Right Join sf_guard_group AS g ON g.id = ug.group_id
WHERE u.id = ?
;
        ", 
        array(
            array(
                "integer" => sfContext::getInstance()->getRequest()->getParameter("id")
                )
            )
        );
        foreach($objects as $key => $object){
            echo    $object->getUsername() . " " .
                    $object->getId() . " " .
                    $object->getName()  . "<br>";
        }
        break;
    case 4:
        // custom object with prepared statement 
        $customObject = new CustomQueryObject();
        $objects = $customObject->prepare("SELECT
u.username AS user_name,
g.id AS user_id,
g.name AS group_name,
p.name AS permission_name
FROM
sf_guard_user AS u
Left Join sf_guard_user_group AS ug ON ug.user_id = u.id
Right Join sf_guard_group AS g ON ug.group_id = g.id
Left Join sf_guard_group_permission AS gp ON g.id = gp.group_id
Left Join sf_guard_permission AS p ON p.id = gp.permission_id       
WHERE u.id = ?
;
        ", 
        array(
            array(
                "integer" => sfContext::getInstance()->getRequest()->getParameter("id")
                )
            )
        );
        foreach($objects as $key => $object){
            echo    $object->getUserName() . " " .
                    $object->getUserId() . " " .
                    $object->getGroupName()  . " ".
                    $object->getPermissionName()  . "<br>";
        }
        break;
 
}
 
?>
</p>
</body>
</html>
 

Custom Queries

Full tree

      SELECT n.name, COUNT(*)-1 AS level
      FROM md_components AS n,md_components AS p
      WHERE n.tree_left BETWEEN p.tree_left AND p.tree_left
      GROUP BY n.tree_left
      ORDER BY n.tree_left;
 

Tree with further info (level, children counter etc.):

 SELECT n.*,
 round((n.tree_right-n.tree_left-1)/2,0) AS countChildren,
 count(*)-1+(n.tree_left>1) AS level,
 ((min(p.tree_right)-n.tree_right-(n.tree_left>1))/2) > 0 AS countPredecessors,
 (((n.tree_left-max(p.tree_left)>1))) AS countSuccessors
 FROM md_components n, md_components p
 WHERE n.tree_left BETWEEN p.tree_left AND p.tree_right AND (p.component_id != n.component_id OR n.tree_left = 1)
 GROUP BY n.component_id
 ORDER BY n.tree_left;
 

Get parent nodes up to root node. Used for breadcrumbs.

SELECT p.*
FROM md_components n, md_components p
WHERE n.tree_left BETWEEN p.tree_left AND p.tree_right
AND n.component_id = 18
ORDER BY n.tree_left;
 

Getting the first level items of root 1

SELECT 
    o.*,
    COUNT(p.component_id)-1 AS level
FROM md_components AS n, 
    md_components AS p, 
    md_components AS o 
WHERE o.tree_left BETWEEN p.tree_left AND p.tree_right 
    AND o.tree_left BETWEEN n.tree_left AND n.tree_right
    AND n.component_id = 1 
GROUP BY o.tree_left
HAVING level = 1
ORDER BY o.tree_left
 

user's navigation tree filtered by group permissions using sf_guard plugin:

SELECT
mc.*
FROM
(
    md_components AS mc
    Left Join md_components_has_sf_guard_permission AS cp ON cp.md_components_component_id = mc.component_id
    Left Join sf_guard_permission AS pm ON pm.id = cp.sf_guard_permission_id
    Inner Join sf_guard_group_permission AS pg ON pm.id = pg.permission_id
    Left Join sf_guard_user_group AS ug ON pg.group_id = ug.group_id 
), md_components AS cm
 
WHERE
    mc.tree_left BETWEEN cm.tree_left AND cm.tree_left
AND
    ug.user_id =  1
GROUP BY mc.tree_left
      ORDER BY mc.tree_left;
 

user's navigation tree filtered by group permissions and additional user permissions using sf_guard plugin:

SELECT
mc.*
FROM
(
    md_components AS mc
    Left Join md_components_has_sf_guard_permission AS cp ON cp.md_components_component_id = mc.component_id
    Left Join sf_guard_permission AS pm ON pm.id = cp.sf_guard_permission_id
    Inner Join sf_guard_group_permission AS pg ON pm.id = pg.permission_id
    Left Join sf_guard_user_group AS ug ON pg.group_id = ug.group_id 
    Left Join sf_guard_user_permission AS um ON pm.id = um.permission_id
), md_components AS cm
 
WHERE
    mc.tree_left BETWEEN cm.tree_left AND cm.tree_left
AND
    (ug.user_id =  1 AND um.user_id=1)
GROUP BY mc.tree_left
      ORDER BY mc.tree_left;
 

The code below shows how to enhance a base model from a custom query.

<?php
 
/**
 * Subclass for representing a row from the 'md_components' table.
 *
 *
 *
 * @package lib.model
 */
class Navigation extends BaseNavigation
{
 
    private $customAttributes = array(
            "isLevel", 
            "selfFirstLevel", 
            "hasChilds", 
            "prevSibling", 
            "nextSibling", 
            "firstChild", 
            "lastChild", 
            "prevRoot", 
            "nextRoot", 
            "selfRoot"
            );
 
    protected function getCustomAttributes(){
        return $this->customAttributes();
    }
 
    public function makeNextRoot()
    {
        $max = $this->getMaxRight();
        $this->setLeftValue($max + 1);
        $this->setRightValue($max + 2);
    }
 
    public function getNavigationId(){
        return $this->getComponentId();
    }
 
    public function getMaxRight(){
 
        $connection = Propel::getConnection();
        $query = 'SELECT MAX(%s) AS max FROM %s';
 
        $query = sprintf($query, NavigationPeer::TREE_RIGHT, NavigationPeer::TABLE_NAME);
        $statement = $connection->prepareStatement($query);
        $resultset = $