Code snippets for symfony 1.x

Navigation

Refine Tags

Snippets tagged "model"

script for to generate (all) modules

This Bash script transform: 'BaseModel.class.php' => 'BaseModel' => 'Model' then call doctrine:generate-admin frontend 'Model', for all files from lib/model/doctrine/base.

#!/bin/bash
for file_class in `ls lib/model/doctrine/base`  do
    base_model=${file_class/%.class.php/}      
    model=${base_model/#Base/}                 
    ./symfony doctrine:generate-admin --env="prod" frontend $model
done
 
by Vlad Bazon on 2010-08-01, tagged bash  doctrine  generateadmin  model  modul  symfony 

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)

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)

Custom Query Objects

Features:

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>
 
by Thomas Schäfer on 2008-03-23, tagged custom  model  query 

Nested Set Navigation Component Module (PART 4)

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;
 
by Thomas Schäfer on 2008-03-19, tagged model  navigation  nested  set 

Enhancing a base model object by filling it with custom attributes from a custom query

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 = $statement->executeQuery();
        $resultset->next();
        return $resultset->getInt('max');
    }
 
    public function getMaxLeft(){
        $connection = Propel::getConnection();
        $query = 'SELECT MAX(%s) AS max FROM %s';
        $query = sprintf($query, NavigationPeer::TREE_LEFT, NavigationPeer::TABLE_NAME);
        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();
        $resultset->next();
        return $resultset->getInt('max');
    }
 
    public function getRootNodes(){
 
        $connection = Propel::getConnection();
        $query = 'SELECT * FROM %s WHERE %s %s %s;';
 
        $query = sprintf($query, NavigationPeer::TABLE_NAME, NavigationPeer::TREE_PARENT, Criteria::EQUAL, 0);
        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();
        $credential = array();
        foreach($resultset as $result){
            $credential[$result["name"]]["component_id"] = $result["component_id"];
            $credential[$result["name"]]["name"] = $result["name"];
            $credential[$result["name"]]["left"] = $result["tree_left"];
            $credential[$result["name"]]["right"] = $result["tree_right"];
        }
        return $credential;
    }
 
 
    public function getTree($offset = 0, $limit = 10, $options = null){
 
 
        $query = '
SELECT
    *,
    (
        SELECT
            CONCAT(COUNT(*))
        FROM md_components MA
        WHERE
        (
            (
                MA.TREE_LEFT < MM.TREE_LEFT AND MA.TREE_RIGHT > MM.TREE_RIGHT
            )
            AND MA.TREE_SCOPE = MM.TREE_SCOPE
        )
    )
    AS isLevel,
    (
        SELECT MZ4.COMPONENT_ID
                FROM md_components MZ4 , md_components MZ5
                WHERE MZ5.TREE_LEFT BETWEEN MZ4.TREE_LEFT AND MZ4.TREE_RIGHT
                AND MZ5.COMPONENT_ID = MM.COMPONENT_ID
                ORDER BY MZ4.TREE_LEFT  LIMIT 1,1
    )
    AS selfFirstLevel,
    (
        SELECT
            IF( MB.TREE_RIGHT - MB.TREE_LEFT > 1, 1, 0)
        FROM md_components MB
        WHERE MB.COMPONENT_ID = MM.COMPONENT_ID
        AND MB.TREE_SCOPE = MM.TREE_SCOPE
    )
    AS hasChilds,
    (
        SELECT MC.COMPONENT_ID FROM md_components MC
        WHERE (MM.TREE_LEFT = MC.TREE_RIGHT + 1)
        AND MC.TREE_SCOPE = MM.TREE_SCOPE
    )
    AS prevSibling,
    (
        SELECT
            MD.COMPONENT_ID
        FROM md_components MD
        WHERE (MM.TREE_RIGHT = MD.TREE_LEFT - 1)
        AND MD.TREE_SCOPE = MM.TREE_SCOPE
    )
    AS nextSibling,
    (
        SELECT
            IF
            (
                (
                    SELECT
                        IF
                        (
                            MQ.TREE_RIGHT - MQ.TREE_LEFT > 1,
                            1,
                            0
                        )
                    FROM md_components MQ
                    WHERE MQ.COMPONENT_ID = MM.COMPONENT_ID
                    AND MQ.TREE_SCOPE = MM.TREE_SCOPE
                ) = 1 ,
                ME.COMPONENT_ID,
                NULL
            )
        FROM md_components ME
        WHERE ME.TREE_LEFT = MM.TREE_LEFT + 1
        AND ME.TREE_SCOPE = MM.TREE_SCOPE
    )
    AS firstChild,
    (
        SELECT
            IF
            (
                (
                    SELECT
                        IF(
                            MS.TREE_RIGHT - MS.TREE_LEFT > 1,
                            1,
                            0
                        )
                    FROM md_components MS
                    WHERE MS.COMPONENT_ID = MM.COMPONENT_ID
                    AND MS.TREE_SCOPE = MM.TREE_SCOPE
                ) = 1 ,
                MF.COMPONENT_ID,
                NULL
            )
        FROM md_components MF
        WHERE MF.TREE_RIGHT = MM.TREE_RIGHT - 1
        AND MF.TREE_SCOPE = MM.TREE_SCOPE
 
    )
    AS lastChild,
    (
              SELECT
            MX.COMPONENT_ID
            FROM md_components MX
        WHERE MX.TREE_LEFT - 1 =
        (
                  SELECT
            MZ.TREE_RIGHT
            FROM md_components MZ
            WHERE (MZ.TREE_RIGHT > MM.TREE_RIGHT) AND (MZ.TREE_LEFT < MM.TREE_LEFT) AND (MZ.TREE_PARENT = 0)
            AND MZ.TREE_SCOPE = MM.TREE_SCOPE
        )
 
    )
    AS nextRoot,
    (
        SELECT
            MX.COMPONENT_ID
            FROM md_components MX
        WHERE MX.TREE_RIGHT + 1 =
        (
                  SELECT
            MZ2.TREE_LEFT
            FROM md_components MZ2
            WHERE (MZ2.TREE_RIGHT > MM.TREE_RIGHT) AND (MZ2.TREE_LEFT < MM.TREE_LEFT) AND (MZ2.TREE_PARENT = 0)
            AND MZ2.TREE_SCOPE = MM.TREE_SCOPE
        )
 
    )
    AS prevRoot,
    (
            SELECT
            MZ3.COMPONENT_ID
            FROM md_components MZ3
            WHERE (MZ3.TREE_RIGHT > MM.TREE_RIGHT) AND (MZ3.TREE_LEFT < MM.TREE_LEFT) AND (MZ3.TREE_PARENT = 0)
            AND MZ3.TREE_SCOPE = MM.TREE_SCOPE
 
    )
    AS selfRoot
 
FROM md_components MM
';
 
        $query .= ' ORDER BY MM.TREE_LEFT ASC ';
        $query .= 'LIMIT '. $offset . ', ' . $limit;
 
        $connection = Propel::getConnection();
        $statement = $connection->createStatement(  );
        $result = $statement->executeQuery( $query , ResultSet::FETCHMODE_NUM);
 
        $objects = NavigationPeer::populateObjects( $result );
 
        $phpNames = NavigationPeer::getFieldNames(BasePeer::TYPE_PHPNAME);
 
        // merge custom and native attributes
        $additionalColumns = array_merge( $phpNames, $this->getCustomAttributes() );
 
        foreach($result as $key => $object){
            foreach($object as $column => $value){
                if($column > count($phpNames)-1){
                    $method = $additionalColumns[$column];
                    $objects[$key-1]->$method = $value;
                }
            }
        }
        return $objects;
    }
 
    /*custom getter functions*/
    public function lastChild(){
        return $this->lastChild;
    }
 
    public function selfFirstLevel(){
        return $this->selfFirstLevel;
    }
 
    public function firstChild(){
        return $this->firstChild;
    }
 
    public function prevRoot(){
        return $this->prevRoot;
    }
 
    public function nextRoot(){
        return $this->nextRoot;
    }
 
    public function selfRoot(){
        return $this->selfRoot;
    }
 
    public function nextSibling(){
        return $this->nextSibling;
    }
 
    public function prevSibling(){
        return $this->prevSibling;
    }
 
    public function isLevel(){
        return $this->isLevel;
    }
 
    public function hasChilds(){
        return $this->hasChilds;
    }
}
 
$columns_map = array
(
    'left'   => NavigationPeer::TREE_LEFT,
    'right'  => NavigationPeer::TREE_RIGHT,
    'parent' => NavigationPeer::TREE_PARENT,
    'scope'  => NavigationPeer::TREE_SCOPE
);
 
sfPropelBehavior::add('Navigation', array('actasnestedset' => array('columns' => $columns_map)));
 
by Thomas Schäfer on 2008-03-19, tagged custom  model  query 

Nested Set Navigation Component Module (PART 3)

NestedSet Model for Navigation

model scheme

enhanced nested set model class

enhanced module action class

navigation list tabular cell template

Features:

TEMPLATES

NestedSet Action Controls

Look at module action class for further information.

/apps/backend/modules/navigation/templates/_list_td_tabular.php
 
 
 
 
// delete action for node enabled if it has parent
<?php if($navigation->getParent()>0): ?>
<?php echo link_to(__('delete'), 'navigation/delete?component_id='.$navigation->getComponentId(), array ('post' => true,'confirm' => __('Are you sure?'),));?>
<?php endif; ?>
 
// edit node
<?php echo link_to(__('edit'), 'navigation/edit?component_id='.$navigation->getComponentId());?>
 
 
// tree actions if node level greater zero
<?php if($navigation->hasChilds()>0 && $navigation->isLevel() > 0): ?>
 
// perform tree delete action
<?php echo link_to(__('delete tree'), 'navigation/deleteDescendants?component_id='.$navigation->getComponentId(), array ('post' => true,'confirm' => __('Are you sure?')));?>
<?php if($navigation->hasChilds() > 0):?>
 
// allow tree copy action if node has children
<?php echo link_to(__('copy tree'), 'navigation/copyDescendants?component_id='.$navigation->getComponentId().'&root_id='.$navigation->selfRoot(), array ('post' => true,'confirm' => __('Are you sure?')));?>
<?php endif; ?>
<?php endif; ?>
 
// move operations
<?php if($navigation->isLevel() > 0): ?>
<?php
    // if a node's root has a successor then move to previous root
    if($navigation->nextRoot()){
        echo link_to(__('moveRootPrev'), 'navigation/moveRootPrev?component_id='. $navigation->getComponentId().'&root_id='.$navigation->selfRoot(), array('class' => 'moveRootPrev'));
    }
    // if a node's root has a predecessor then move to next root
    if($navigation->prevRoot()){
        echo link_to(__('moveRootNext'), 'navigation/moveRootNext?component_id='.$navigation->getComponentId().'&root_id='.$navigation->selfRoot(), array('class' => 'moveRootNext'));
    }
?>
<?php endif; ?>
 
// self-explaining
<?php echo link_to(__('insertAsFirstChildOf'), 'navigation/insertAsFirstChildOf?component_id='. $navigation->getComponentId(), array('class' => 'insertAsFirstChildOf'));?>
<?php echo link_to(__('insertAsLastChildOf'), 'navigation/insertAsLastChildOf?component_id='.$navigation->getComponentId(), array('class' => 'insertAsLastChildOf'));?>
if($navigation->isLevel() > 0){
    echo link_to(__('insertAsPrevSiblingOf'), 'navigation/insertAsPrevSiblingOf?component_id='.$navigation->getComponentId(), array('class' => 'insertAsPrevSiblingOf'));
    echo link_to(__('insertAsNextSiblingOf'), 'navigation/insertAsNextSiblingOf?component_id='.$navigation->getComponentId(), array('class' => 'insertAsNextSiblingOf'));
}
?>
<?php if($navigation->getParent()>0): ?>
    // move to parent node and place before
    if($navigation->isLevel()>1){
        echo link_to(__('moveLeftUp'), 'navigation/moveLeftUp?component_id='. $navigation->getComponentId(), array('class' => 'moveLeftUp'));
    }
 
    // move to parent node and place after
    if($navigation->isLevel()>1){
        echo link_to(__('moveLeftDown'), 'navigation/moveLeftDown?component_id='.$navigation->getComponentId(), array('class' => 'moveLeftDown'));
    }
 
    // place as predecessor's child
    if ( $navigation->isLevel() > 0 && $navigation->prevSibling() ) {
        echo link_to(__('moveRightPrev'), 'navigation/moveRightPrev?component_id='.$navigation->getComponentId(), array('class' => 'moveRightPrev'));
    }
 
    // place as successor's child
    if ( $navigation->isLevel() > 0 && $navigation->nextSibling() ){
        echo link_to(__('moveRightNext'), 'navigation/moveRightNext?component_id='.$navigation->getComponentId(), array('class' => 'moveRightNext'));
    }
?>
 
if($navigation->isLevel()>0){
    if($navigation->prevSibling()){
        echo link_to(__('moveToPrevSiblingOf'), 'navigation/moveToPrevSiblingOf?component_id='.$navigation->getComponentId(),array('class' => 'moveUp'));
    }
 
    if($navigation->nextSibling()){
        echo link_to(__('moveToNextSiblingOf'), 'navigation/moveToNextSiblingOf?component_id='.$navigation->getComponentId(), array('class' => 'moveDown'));
    }
}
?>
 

Enjoy it.

by Thomas Schäfer on 2008-03-19, tagged model  navigation  nested  set 

Nested Set Module

NestedSet Model for Navigation

model scheme

enhanced nested set model class

enhanced module action class

navigation list tabular cell template

Features:

NavigationModel Scheme

  <table name="md_components" phpName="Navigation">
    <column name="component_id" type="INTEGER" size="11" primaryKey="true" required="true" autoIncrement="true"/>
    <column name="name" type="VARCHAR" size="50" required="true" phpName="Name"/>
    <column name="action" type="VARCHAR" size="50" phpName="Action"/>
    <column name="caption" type="VARCHAR" size="50" phpName="Caption"/>
    <column name="description" type="LONGVARCHAR" phpName="Description"/>
    <column name="parameter" type="VARCHAR" size="50" phpName="Parameter"/>
    <column name="image" type="VARCHAR" size="50" phpName="Image"/>
    <column name="tree_left" type="INTEGER" size="11" phpName="Left"/>
    <column name="tree_right" type="INTEGER" size="11" phpName="Right"/>
    <column name="tree_parent" type="INTEGER" size="11" default="0" phpName="Parent"/>
    <column name="tree_scope" type="INTEGER" size="11" default="0" phpName="Scope"/>
    <column name="created_at" type="TIMESTAMP" required="true" phpName="CreationDate"/>
    <column name="created_by" type="INTEGER" size="11" default="1" phpName="Creator"/>
    <column name="templatable" type="BOOLEAN" phpName="IsTemplate"/>
    <column name="blockable" type="BOOLEAN" phpName="IsBlocked"/>
    <column name="navigatable" type="BOOLEAN" phpName="IsNavigation"/>
    <column name="deleted_at" type="TIMESTAMP"/>
    <column name="deleted_by" type="INTEGER" size="11"/>
    <foreign-key foreignTable="sf_guard_user" name="creator" onDelete="setnull">
      <reference local="created_by" foreign="id"/>
    </foreign-key>
    <foreign-key foreignTable="sf_guard_user" name="FK_USER" onDelete="setnull">
      <reference local="deleted_by" foreign="id"/>
    </foreign-key>
  </table>
 

enhanced nested set model class

<?php
 
/**
 * Subclass for representing a row from the 'md_components' table.
 *
 *
 *
 * @package lib.model
 */
class Navigation extends BaseNavigation
{
    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 = $statement->executeQuery();
        $resultset->next();
        return $resultset->getInt('max');
    }
 
    public function getMaxLeft(){
        $connection = Propel::getConnection();
        $query = 'SELECT MAX(%s) AS max FROM %s';
        $query = sprintf($query, NavigationPeer::TREE_LEFT, NavigationPeer::TABLE_NAME);
        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();
        $resultset->next();
        return $resultset->getInt('max');
    }
 
    public function getRootNodes(){
 
        $connection = Propel::getConnection();
        $query = 'SELECT * FROM %s WHERE %s %s %s;';
 
        $query = sprintf($query, NavigationPeer::TABLE_NAME, NavigationPeer::TREE_PARENT, Criteria::EQUAL, 0);
        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();
        $credential = array();
        foreach($resultset as $result){
            $credential[$result["name"]]["component_id"] = $result["component_id"];
            $credential[$result["name"]]["name"] = $result["name"];
            $credential[$result["name"]]["left"] = $result["tree_left"];
            $credential[$result["name"]]["right"] = $result["tree_right"];
        }
        return $credential;
    }
 
 
    public function getTree($offset = 0, $limit = 10, $options = null){
 
 
        $query = '
SELECT
    *,
    (
        SELECT
            CONCAT(COUNT(*))
        FROM md_components MA
        WHERE
        (
            (
                MA.TREE_LEFT < MM.TREE_LEFT AND MA.TREE_RIGHT > MM.TREE_RIGHT
            )
            AND MA.TREE_SCOPE = MM.TREE_SCOPE
        )
    )
    AS isLevel,
    (
        SELECT MZ4.COMPONENT_ID
                FROM md_components MZ4 , md_components MZ5
                WHERE MZ5.TREE_LEFT BETWEEN MZ4.TREE_LEFT AND MZ4.TREE_RIGHT
                AND MZ5.COMPONENT_ID = MM.COMPONENT_ID
                ORDER BY MZ4.TREE_LEFT  LIMIT 1,1
    )
    AS selfFirstLevel,
    (
        SELECT
            IF( MB.TREE_RIGHT - MB.TREE_LEFT > 1, 1, 0)
        FROM md_components MB
        WHERE MB.COMPONENT_ID = MM.COMPONENT_ID
        AND MB.TREE_SCOPE = MM.TREE_SCOPE
    )
    AS hasChilds,
    (
        SELECT MC.COMPONENT_ID FROM md_components MC
        WHERE (MM.TREE_LEFT = MC.TREE_RIGHT + 1)
        AND MC.TREE_SCOPE = MM.TREE_SCOPE
    )
    AS prevSibling,
    (
        SELECT
            MD.COMPONENT_ID
        FROM md_components MD
        WHERE (MM.TREE_RIGHT = MD.TREE_LEFT - 1)
        AND MD.TREE_SCOPE = MM.TREE_SCOPE
    )
    AS nextSibling,
    (
        SELECT
            IF
            (
                (
                    SELECT
                        IF
                        (
                            MQ.TREE_RIGHT - MQ.TREE_LEFT > 1,
                            1,
                            0
                        )
                    FROM md_components MQ
                    WHERE MQ.COMPONENT_ID = MM.COMPONENT_ID
                    AND MQ.TREE_SCOPE = MM.TREE_SCOPE
                ) = 1 ,
                ME.COMPONENT_ID,
                NULL
            )
        FROM md_components ME
        WHERE ME.TREE_LEFT = MM.TREE_LEFT + 1
        AND ME.TREE_SCOPE = MM.TREE_SCOPE
    )
    AS firstChild,
    (
        SELECT
            IF
            (
                (
                    SELECT
                        IF(
                            MS.TREE_RIGHT - MS.TREE_LEFT > 1,
                            1,
                            0
                        )
                    FROM md_components MS
                    WHERE MS.COMPONENT_ID = MM.COMPONENT_ID
                    AND MS.TREE_SCOPE = MM.TREE_SCOPE
                ) = 1 ,
                MF.COMPONENT_ID,
                NULL
            )
        FROM md_components MF
        WHERE MF.TREE_RIGHT = MM.TREE_RIGHT - 1
        AND MF.TREE_SCOPE = MM.TREE_SCOPE
 
    )
    AS lastChild,
    (
              SELECT
            MX.COMPONENT_ID
            FROM md_components MX
        WHERE MX.TREE_LEFT - 1 =
        (
                  SELECT
            MZ.TREE_RIGHT
            FROM md_components MZ
            WHERE (MZ.TREE_RIGHT > MM.TREE_RIGHT) AND (MZ.TREE_LEFT < MM.TREE_LEFT) AND (MZ.TREE_PARENT = 0)
            AND MZ.TREE_SCOPE = MM.TREE_SCOPE
        )
 
    )
    AS nextRoot,
    (
        SELECT
            MX.COMPONENT_ID
            FROM md_components MX
        WHERE MX.TREE_RIGHT + 1 =
        (
                  SELECT
            MZ2.TREE_LEFT
            FROM md_components MZ2
            WHERE (MZ2.TREE_RIGHT > MM.TREE_RIGHT) AND (MZ2.TREE_LEFT < MM.TREE_LEFT) AND (MZ2.TREE_PARENT = 0)
            AND MZ2.TREE_SCOPE = MM.TREE_SCOPE
        )
 
    )
    AS prevRoot,
    (
            SELECT
            MZ3.COMPONENT_ID
            FROM md_components MZ3
            WHERE (MZ3.TREE_RIGHT > MM.TREE_RIGHT) AND (MZ3.TREE_LEFT < MM.TREE_LEFT) AND (MZ3.TREE_PARENT = 0)
            AND MZ3.TREE_SCOPE = MM.TREE_SCOPE
 
    )
    AS selfRoot
 
FROM md_components MM
';
 
        $query .= ' ORDER BY MM.TREE_LEFT ASC ';
        $query .= 'LIMIT '. $offset . ', ' . $limit;
 
        $connection = Propel::getConnection();
        $statement = $connection->createStatement(  );
        $result = $statement->executeQuery( $query , ResultSet::FETCHMODE_NUM);
 
        $objects = NavigationPeer::populateObjects( $result );
 
        $phpNames = NavigationPeer::getFieldNames(BasePeer::TYPE_PHPNAME);
 
        $additionalColumns =
            array_merge(
                $phpNames,
                array("isLevel", "selfFirstLevel", "hasChilds", "prevSibling", "nextSibling", "firstChild", "lastChild", "prevRoot", "nextRoot", "selfRoot")
            );
 
        foreach($result as $key => $object){
            foreach($object as $column => $value){
                if($column > 15){
                    $method = $additionalColumns[$column];
 
                    $objects[$key-1]->$method = $value;
                }
            }
        }
        return $objects;
    }
 
    /*no setter functions*/
    public function lastChild(){
        return $this->lastChild;
    }
 
    public function selfFirstLevel(){
        return $this->selfFirstLevel;
    }
 
    public function firstChild(){
        return $this->firstChild;
    }
 
    public function prevRoot(){
        return $this->prevRoot;
    }
 
    public function nextRoot(){
        return $this->nextRoot;
    }
 
    public function selfRoot(){
        return $this->selfRoot;
    }
 
    public function nextSibling(){
        return $this->nextSibling;
    }
 
    public function prevSibling(){
        return $this->prevSibling;
    }
 
    public function isLevel(){
        return $this->isLevel;
    }
 
    public function hasChilds(){
        return $this->hasChilds;
    }
}
 
$columns_map = array
(
    'left'   => NavigationPeer::TREE_LEFT,
    'right'  => NavigationPeer::TREE_RIGHT,
    'parent' => NavigationPeer::TREE_PARENT,
    'scope'  => NavigationPeer::TREE_SCOPE
);
 
sfPropelBehavior::add('Navigation', array('actasnestedset' => array('columns' => $columns_map)));
 

enhanced module action class

/apps/backend/modules/navigation/actions/actions.class.php

<?php
 
/**
 * navigation actions.
 *
 * @package    dispo
 * @subpackage navigation
 * @author     Your name here
 * @version    SVN: $Id: actions.class.php 2288 2006-10-02 15:22:13Z fabien $
 */
class navigationActions extends autonavigationActions
{
    public function executeList()
    {
 
        $this->typeOfList = 1;
 
        switch($this->typeOfList)
        {
            case 1:
                $pager = new Navigation();
                $this->pager = $pager->getTree(0,200);
                break;
            default:
 
                $this->processSort();
 
                $this->processFilters();
 
                // pager
 
                $navigation = new Navigation();
                $this->pager = new sfPropelPager('Navigation', 100);
                $c = new Criteria();
                $c->addAscendingOrderByColumn(NavigationPeer::TREE_LEFT);
 
                if($this->getUser()->hasCredential('SHOW_ALL_NAVIGATION_ROOTS')){
 
                } else {
                    $rootNodes = $navigation->getRootNodes();
                    $conditions = array();
                    foreach($rootNodes as $credentialName => $credential) {
                        if($this->getUser()->hasCredential($credentialName)){
                            $c1 = $c->getNewCriterion(NavigationPeer::TREE_LEFT, $credential["left"], Criteria::GREATER_THAN);
                            $c2 = $c->getNewCriterion(NavigationPeer::TREE_RIGHT, $credential["right"], Criteria::LESS_THAN);
                            $conditions[] = $c1->addAnd($c2);
                        }
                    }
                    if(count($conditions) > 1) {
                        foreach($conditions as $condition){
                            $c->addOr($condition);
                        }
                    } else {
                        $c->add($conditions[0]);
                    }
                }
 
 
                $this->addSortCriteria($c);
                $this->addFiltersCriteria($c);
                $this->pager->setCriteria($c);
 
                $this->pager->setPage($this->getRequestParameter('page', 1));
                $this->pager->init();
 
                break;
        }
    }
 
    public function executeShow() {
        $this->component = NavigationPeer::retrieveByPk($this->getRequestParameter('component_id'));
        $this->paths = $this->component->getPath();
    }
 
    public function executeBlockable() {
        $navigation = NavigationPeer::retrieveByPk($this->getRequestParameter('component_id'));
        $state = $this->getRequestParameter('state')==1 ? 1 : 0;
        $navigation->setIsBlocked($state);
        $navigation->save();
        $this->state = $state;
        $this->navigation = $navigation;
    }
 
    public function executeNavigatable() {
        $navigation = NavigationPeer::retrieveByPk($this->getRequestParameter('component_id'));
        $state = $this->getRequestParameter('state')==1 ? 1 : 0;
        $navigation->setIsNavigation($state);
        $navigation->save();
        $this->state = $state;
        $this->navigation = $navigation;
    }
 
 
    private function getPrimaryRoot(){
        return NavigationPeer::retrieveByPk(1);
    }
 
 
    // Default Root Node
    public function executeMakeRoot(){
 
        $root = new Navigation();
        $root->makeRoot();
        $root->save();
 
        return $this->redirect('navigation/edit?component_id='. $root->getNavigationId());
 
    }
 
    // Further Root Node
    public function executeMakeNextRoot(){
 
        $root = new Navigation();
        $root->makeNextRoot();
        $root->save();
 
 
        return $this->redirect('navigation/edit?component_id='. $root->getNavigationId());
 
    }
 
    // Delete Branch
    public function executeDeleteDescendants(){
        $navigation = NavigationPeer::retrieveByPk($this->getRequestParameter('component_id'));
        $navigation->deleteDescendants();
        $navigation = $navigation->reload();
        $navigation->delete();
        return $this->redirect('navigation/list');
    }
 
    // insert first child node
    public function executeInsertAsFirstChildOf(){
        $this->message = "Nun wird ein Kindelement erzeugt";
        $navigation = NavigationPeer::retrieveByPk($this->getRequestParameter('component_id'));
 
        $child = new Navigation();
        $child->insertAsFirstChildOf($navigation);
        $child->save();
        return $this->redirect('navigation/edit?component_id='. $child->getNavigationId());
 
    }
 
    // insert last child node
    public function executeInsertAsLastChildOf(){
 
        $this->message = "Nun wird ein Kindelement erzeugt";
 
        $navigation = NavigationPeer::retrieveByPk($this->getRequestParameter('component_id'));
 
        $child = new Navigation();
        $child->insertAsLastChildOf($navigation);
        $child->save();
 
 
        return $this->redirect('navigation/edit?component_id='. $child->getNavigationId());
 
    }
 
    // insert sibling before
    public function executeInsertAsNextSiblingOf(){
 
        $navigation = NavigationPeer::retrieveByPk($this->getRequestParameter('component_id'));
 
        $child = new Navigation();
        $child->insertAsNextSiblingOf($navigation);
        $child->save();
 
 
        return $this->redirect('navigation/edit?component_id='. $child->getNavigationId());
    }
 
    // insert sibling after
    public function executeInsertAsPrevSiblingOf(){
 
        $navigation = NavigationPeer::retrieveByPk($this->getRequestParameter('component_id'));
 
        $child = new Navigation();
        $child->insertAsPrevSiblingOf($navigation);
        $child->save();
 
        return $this->redirect('navigation/edit?component_id='. $child->getNavigationId());
    }
 
    /*Tree Modification*/
    // UP
    public function executeMoveToPrevSiblingOf(){
 
        $navigation = NavigationPeer::retrieveByPk($this->getRequestParameter('component_id'));
        $siblingNavigation = $navigation->retrievePrevSibling($navigation);
 
        $navigation->moveToPrevSiblingOf($siblingNavigation);
        $navigation->save();
 
        return $this->redirect('navigation/list');
 
    }
    //DOWN
    public function executeMoveToNextSiblingOf(){
 
        $navigation = NavigationPeer::retrieveByPk($this->getRequestParameter('component_id'));
        $siblingNavigation = $navigation->retrieveNextSibling($navigation);
 
        $navigation->moveToNextSiblingOf($siblingNavigation);
        $navigation->save();
 
        return $this->redirect('navigation/list');
 
    }
 
 
    // LEFT UP
    public function executeMoveLeftUp(){
 
        $navigation = NavigationPeer::retrieveByPk($this->getRequestParameter('component_id'));
        $parentNavigation = NavigationPeer::retrieveByPk($navigation->getParent());
        $navigation->moveToPrevSiblingOf($parentNavigation);
        $navigation->save();
        return $this->redirect('navigation/list');
    }
 
    // LEFT DOWN
    public function executeMoveLeftDown(){
 
        $navigation = NavigationPeer::retrieveByPk($this->getRequestParameter('component_id'));
        $parentNavigation = NavigationPeer::retrieveByPk($navigation->getParent());
        $navigation->moveToNextSiblingOf($parentNavigation);
        $navigation->save();
        return $this->redirect('navigation/list');
 
    }
 
    // RIGHT FIRST
    public function executeMoveRightPrev(){
 
        $navigation = NavigationPeer::retrieveByPk($this->getRequestParameter('component_id'));
        $prevSibling = $navigation->retrievePrevSibling($navigation);
        $navigation->moveToLastChildOf($prevSibling);
        $navigation->save();
        return $this->redirect('navigation/list');
    }
 
    // RIGHT LAST
    public function executeMoveRightNext(){
 
        $navigation = NavigationPeer::retrieveByPk($this->getRequestParameter('component_id'));
        $nextSibling = $navigation->retrieveNextSibling($navigation);
        $navigation->moveToLastChildOf($nextSibling);
        $navigation->save();
        return $this->redirect('navigation/list');
 
    }
 
    // ROOT PREV
    public function executeMoveRootPrev(){
        $navigation = NavigationPeer::retrieveByPk($this->getRequestParameter('component_id'));
        $rootNavigation = NavigationPeer::retrieveByPk($this->getRequestParameter('root_id'));
        $prevRootNavigation = $rootNavigation->retrievePrevSibling($rootNavigation);
        $navigation->moveToLastChildOf($prevRootNavigation);
        $navigation->save();
 
        return $this->redirect('navigation/list');
    }
 
    // ROOT NEXT
    public function executeMoveRootNext(){
        $navigation = NavigationPeer::retrieveByPk($this->getRequestParameter('component_id'));
        $rootNavigation = NavigationPeer::retrieveByPk($this->getRequestParameter('root_id'));
        $nextRootNavigation = $rootNavigation->retrieveNextSibling($rootNavigation);
        $navigation->moveToLastChildOf($nextRootNavigation);
        $navigation->save();
        return $this->redirect('navigation/list');
    }
 
    // Duplicate Tree
    public function executeCopyDescendants(){
 
        $navigation = NavigationPeer::retrieveByPk($this->getRequestParameter('component_id'));
        $parentNavigation = NavigationPeer::retrieveByPk($navigation->getParent());
 
        $newParentNavigation = $this->copyNode($navigation, $parentNavigation);
 
        $this->iterateOnDescendants($navigation->getDescendants() , $newParentNavigation);
 
        return $this->redirect('navigation/list');
    }
 
    private function iterateOnDescendants($navigations, $prevNavigation){
 
        foreach($navigations as $navigation){
            $actualNavigation = $this->copyNodeDescendants($navigation, $prevNavigation);
            $prevNavigation = $actualNavigation;
        }
    }
 
    private function transferData(Navigation $navigation){
 
        $newNavigation = new Navigation();
        $newNavigation->setName($navigation->getName());
        $newNavigation->setAction($navigation->getAction());
        $newNavigation->setParameter($navigation->getParameter());
        $newNavigation->setCaption($navigation->getCaption());
        $newNavigation->setDescription($navigation->getDescription());
        $newNavigation->setIsNavigation(0);
        $newNavigation->setIsBlocked(0);
        $newNavigation->setIsTemplate(0);
        return <
by anonymous on 2008-03-19, tagged model  navigation  nested  set 

Enhanced DB Designer XSL Transformer Utility

USAGE: create folders in project directory /batch/in /batch/out

place - transform.php - dbd2propel.xsl - transform into folder /batch

call transform script from symfony project console root directory: [LINUX] 1. php batch/transformer your_schema - input from /batch/in/your_schema.xml - output to folder /config

  1. php batch/transformer your_schema yourout

call transform script from symfony project console root directory: [WINDOWS] 1. php batch/transformer.php your_schema - input from /batch/in/your_schema.xml - output to folder /config

  1. php batch/transformer.php your_schema yourout

transform

#!/usr/bin/env php
<?php
chdir(dirname(__FILE__));
include('transform.php');
?>
 

tranform.php

<?php
/*
This PHP5 script will load the transform a DB Designer 4 database model to the
propel database schema file format
*/
 
define('SCHEMA_INPUT_DIR', dirname(__FILE__). DIRECTORY_SEPARATOR . 'in' . DIRECTORY_SEPARATOR );
define('SCHEMA_OUTPUT_TEST_DIR', dirname(__FILE__). DIRECTORY_SEPARATOR . 'out' . DIRECTORY_SEPARATOR );
define('SCHEMA_OUTPUT_DIR', dirname(dirname(__FILE__)). DIRECTORY_SEPARATOR .'config'.DIRECTORY_SEPARATOR );
 
 
$args = $_SERVER['argv'];
 
if(count($args) > 1 && count($args) < 4 && is_file(SCHEMA_INPUT_DIR . $args[1] . '.xml')) {
 
    $loadedDBDesignerScheme = SCHEMA_INPUT_DIR . $args[1] . '.xml';
    $processedScheme        = !empty($args[2])
                            ? SCHEMA_OUTPUT_TEST_DIR . $args[2] . '.xml'
                            : SCHEMA_OUTPUT_DIR .'schema.xml';
 
    $transformatorScheme    = 'dbd2propel.xsl';
 
    // load the DB Designer 4 XML
    $xml = new DOMDocument;
    $xml->load($loadedDBDesignerScheme);
 
    // load the transformation stylesheet
    $xsl = new DOMDocument;
    $xsl->load($transformatorScheme);
 
    $proc = new XSLTProcessor();
    // attach the xsl rules
    $proc->importStyleSheet($xsl);
 
    $schema_xml = $proc->transformToXML($xml);
 
    file_put_contents($processedScheme, $schema_xml);
 
    echo "\nTransformation done successfully\n";
    echo "DB Designer Input Schema File: " . $args[1] . ".xml\n";
    if(!empty($args[2]))
        echo "Symfony Output Schema File: " . $args[2] . ".xml\n";
    echo "Folder:" . ($processedScheme==SCHEMA_OUTPUT_DIR .'schema.xml' ?SCHEMA_OUTPUT_DIR: SCHEMA_OUTPUT_TEST_DIR);
 
} elseif(!is_file(SCHEMA_INPUT_DIR . $args[1] . '.xml') ){
    echo "\nError!\nschema file >>". SCHEMA_INPUT_DIR . $args[1] . '.xml<< does not exist'."\n\n";
} else {
    echo "\nSyntax: \n" ;
    echo "transform <schema_in> [optional: schema_out]";
}
 
?>
 

dbd2propel.xsl

<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
<xsl:strip-space elements="*"/>
 
<!--
DB Designer XML to Propel Schema XML
 
==== Author: Jonathan Graham <jkgraham@gmail.com>
==== Version: 0.4 (2006-11-13) (http://blog.tooleshed.com/?p=6)
==== Description:
This XSL will transform a DB Designer 4 database model XML file into a
Propel database schema file.  This allows you to design your database
model using DB Designer 4 (models are saved in XML format) and then
easily create the Propel database schema file.
 
The PROPEL properties that this XSL will translate are listed below.
 
TABLE: name, description
COLUMN: name, primaryKey, required, type, size, scale, default, autoIncrement, description
FOREIGN-KEY: foreignTable, name, onDelete
REFERENCE: local, foreign
INDEX: index (not related to FK), unique, fulltext
 
==== Usage:
- Simply feed this XSL into your favorite XSLT engine along with your DB Designer
XML model.  The resulting output is a Propel database schema XML.
 
==== Collaboration:
Peter Banik <peter@froggle.org> - UNIQUE TAG
Benedicto Franco Jr. - MULTIPLE FOREIGN KEY, DATABASE NAME
Martin Kreidenweis <martin@kreidenweis.com> - Bug fixes, INDEX
Michiel Hakvoort - onDelete
Michel D'HOOGE - FULLTEXT
Thomas Schaefer - v0.5
 
==== Software:
Propel: http://propel.phpdb.org/
DB Designer 4: http://www.fabforce.net/dbdesigner4/
 
==== Copyright (c) 2004-2006, Jonathan Graham
Licensed under the GNU Lesser General Public License (LGPL) - http://www.gnu.org/copyleft/lgpl.html.
 
==== Change Log
version 0.1 (2004-11-08) - initial version
version 0.2 (2006-10-18) - Added Peter and Benedicto's updates.
version 0.3 (2006-11-05) - added non-unique-INDEXes and onDelete
version 0.4 (2006-11-13) - added support for index names and FULLTEXT indexes, changed license to LGPL
version 0.5 (2008-01-16) - added support for comments for enabling phpName-Attribute, changed license to LGPL
version 0.5.1 (2008-02-07) - changed support for comments for enabling phpName-Attribute, changed license to LGPL
 
-->
 
<!-- ============================================================ DATABASE template -->
<xsl:template match="/">
    <database defaultIdMethod="native">
        <xsl:attribute name="name">
            <xsl:value-of select="/DBMODEL/SETTINGS/GLOBALSETTINGS/@ModelName"/>
        </xsl:attribute>
        <xsl:attribute name="package">
            <xsl:value-of select="/DBMODEL/SETTINGS/GLOBALSETTINGS/@Comments"/>
        </xsl:attribute>
        <xsl:if test="/DBMODEL/SETTINGS/GLOBALSETTINGS/@Comments != ''">
            <xsl:if test="contains(/DBMODEL/SETTINGS/GLOBALSETTINGS/@Comments,'package=')">
                <xsl:attribute name="package">
                    <xsl:value-of select="substring-after(/DBMODEL/SETTINGS/GLOBALSETTINGS/@Comments,'package=')"  />
                </xsl:attribute>
                <xsl:variable name="aa" select="substring-before(/DBMODEL/SETTINGS/GLOBALSETTINGS/@Comments,'package=')"/>
                <xsl:choose>
                    <xsl:when test="string-length($aa) &gt; 0">
                        <xsl:attribute name="description">
                            <xsl:value-of select="substring-before(/DBMODEL/SETTINGS/GLOBALSETTINGS/@Comments,'package=')"  />
                        </xsl:attribute>
                    </xsl:when>
                    <xsl:otherwise />
                </xsl:choose>
            </xsl:if>
        </xsl:if>
        <xsl:apply-templates />
    </database>
</xsl:template>
 
<!-- ============================================================ TABLES template -->
<xsl:template match="/DBMODEL/METADATA/TABLES/TABLE">
    <xsl:if test="@Collapsed = 0">
        <xsl:if test="@IsLinkedObject = 0">
            <table>
                <xsl:attribute name="name">
                    <xsl:value-of select="@Tablename"/>
                </xsl:attribute>
                <xsl:if test="@Comments != ''">
                    <xsl:if test="contains(@Comments,'phpName=')">
                        <xsl:attribute name="phpName">
                            <xsl:value-of select="substring-after(@Comments,'phpName=')"  />
                        </xsl:attribute>
                        <xsl:variable name="aa" select="substring-before(@Comments,'phpName=')"/>
                        <xsl:choose>
                            <xsl:when test="string-length($aa) &gt; 0">
                                <xsl:attribute name="description">
                                    <xsl:value-of select="substring-before(@Comments,'phpName=')"  />
                                </xsl:attribute>
                            </xsl:when>
                            <xsl:otherwise />
                        </xsl:choose>
                    </xsl:if>
                    <xsl:if test="not(contains(@Comments,'phpName='))">
                        <xsl:attribute name="description">
                            <xsl:value-of select="@Comments"  />
                        </xsl:attribute>
                    </xsl:if>
                </xsl:if>
                <xsl:apply-templates />
            </table>
        </xsl:if>
    </xsl:if>
</xsl:template>
 
 
<!-- ============================================================ COLUMNS template -->
<xsl:template match="COLUMNS/COLUMN">
    <column>
        <!-- get data type -->
        <xsl:variable name="datatype">
            <xsl:call-template name="get_datatype">
                <xsl:with-param name="id"><xsl:value-of select="@idDatatype"/></xsl:with-param>
            </xsl:call-template>
        </xsl:variable>
 
        <!-- remove parens from datatypeparams -->
        <xsl:variable name="dtpclean">
            <xsl:call-template name="clean_dataparams">
                <xsl:with-param name="dtp"><xsl:value-of select="@DatatypeParams"/></xsl:with-param>
            </xsl:call-template>
        </xsl:variable>
 
        <!-- ==== name ==== -->
        <xsl:attribute name="name">
            <xsl:value-of select="@ColName"/>
        </xsl:attribute>
 
        <!-- ==== type ==== -->
        <xsl:attribute name="type">
            <xsl:value-of select="$datatype"/>
        </xsl:attribute>
 
        <xsl:if test="$dtpclean != ''">
            <!-- ==== size ==== -->
            <xsl:attribute name="size">
                <xsl:call-template name="get_datasize">
                    <xsl:with-param name="dtpc"><xsl:value-of select="$dtpclean"/></xsl:with-param>
                    <xsl:with-param name="dtype"><xsl:value-of select="$datatype"/></xsl:with-param>
                </xsl:call-template>
            </xsl:attribute>
 
            <xsl:if test="contains('FLOAT,DOUBLE',$datatype)">
                <!-- ==== scale ==== -->
                <xsl:attribute name="scale">
                    <xsl:value-of select="substring-after($dtpclean,',')"/>
                </xsl:attribute>
            </xsl:if>
 
        </xsl:if>
 
        <!-- ==== primaryKey ==== -->
        <xsl:if test="@PrimaryKey = '1'">
            <xsl:attribute name="primaryKey">true</xsl:attribute>
        </xsl:if>
 
        <!-- ==== required ==== -->
        <xsl:if test="@NotNull = '1'">
            <xsl:attribute name="required">true</xsl:attribute>
        </xsl:if>
 
        <!-- ==== default ==== -->
        <xsl:if test="@DefaultValue != ''">
            <xsl:attribute name="default">
                <xsl:value-of select="@DefaultValue"/>
            </xsl:attribute>
        </xsl:if>
 
        <!-- ==== autoIncrement ==== -->
        <xsl:if test="@AutoInc = '1'">
            <xsl:attribute name="autoIncrement">true</xsl:attribute>
        </xsl:if>
 
        <!-- ==== description ==== -->
        <xsl:if test="@Comments != ''">
            <xsl:if test="contains(@Comments,'phpName=')">
                <xsl:attribute name="phpName">
                    <xsl:value-of select="substring-after(@Comments,'phpName=')"  />
                </xsl:attribute>
                <xsl:variable name="aa" select="substring-before(@Comments,'phpName=')"/>
                <xsl:choose>
                    <xsl:when test="string-length($aa) &gt; 0">
                        <xsl:attribute name="description">
                            <xsl:value-of select="substring-before(@Comments,'phpName=')"  />
                        </xsl:attribute>
                    </xsl:when>
                    <xsl:otherwise />
                </xsl:choose>
            </xsl:if>
            <xsl:if test="not(contains(@Comments,'phpName='))">
                <xsl:attribute name="description">
                    <xsl:value-of select="@Comments"  />
                </xsl:attribute>
            </xsl:if>
        </xsl:if>
 
    </column>
</xsl:template>
 
<!-- ============================================================ RELATIONS template -->
<xsl:template match="RELATIONS_END/RELATION_END">
 
    <xsl:variable name="id"><xsl:value-of select="@ID"/></xsl:variable>
 
    <xsl:call-template name="show_ForeignKey">
        <xsl:with-param name="relation" select="/DBMODEL/METADATA/RELATIONS/RELATION[@ID=$id]"/>
    </xsl:call-template>
 
</xsl:template>
 
<!-- ============================================================ INDEX template -->
<xsl:template match="INDICES/INDEX">
    <xsl:choose>
        <xsl:when test="@IndexKind = '1' and @FKRefDef_Obj_id='-1'">
            <index>
                <xsl:attribute name="name"><xsl:value-of select="@IndexName"/></xsl:attribute>
                <xsl:apply-templates select="INDEXCOLUMNS/INDEXCOLUMN" mode="normal"/>
            </index>
        </xsl:when>
        <xsl:when test="@IndexKind = '2'">
            <unique>
                <xsl:attribute name="name"><xsl:value-of select="@IndexName"/></xsl:attribute>
                <xsl:apply-templates select="INDEXCOLUMNS/INDEXCOLUMN" mode="unique"/>
            </unique>
        </xsl:when>
        <xsl:when test="@IndexKind = '3'">
            <index>
                <xsl:attribute name="name"><xsl:value-of select="@IndexName"/></xsl:attribute>
                <xsl:apply-templates select="INDEXCOLUMNS/INDEXCOLUMN" mode="normal"/>
                <vendor type="mysql">
                    <parameter name="Index_type" value="FULLTEXT"/>
                </vendor>
            </index>
        </xsl:when>
    </xsl:choose>
</xsl:template>
 
 
<!-- ============================================================ columns within an index -->
<xsl:template match="INDICES/INDEX/INDEXCOLUMNS/INDEXCOLUMN" mode="normal">
    <xsl:variable name="columnId"><xsl:value-of select="@idColumn"/></xsl:variable>
    <index-column>
        <xsl:attribute name="name"><xsl:value-of select="//COLUMNS/COLUMN[@ID=$columnId]/@ColName"/></xsl:attribute>
    </index-column>
</xsl:template>
 
<xsl:template match="INDICES/INDEX/INDEXCOLUMNS/INDEXCOLUMN" mode="unique">
    <xsl:variable name="columnId"><xsl:value-of select="@idColumn"/></xsl:variable>
    <unique-column>
        <xsl:attribute name="name"><xsl:value-of select="//COLUMNS/COLUMN[@ID=$columnId]/@ColName"/></xsl:attribute>
    </unique-column>
</xsl:template>
 
 
<!-- ============================================================ show_ForeignKey -->
<xsl:template name="show_ForeignKey">
    <xsl:param name="relation"/>
    <foreign-key>
 
        <!-- foreignTable -->
        <xsl:attribute name="foreignTable">
            <xsl:value-of select="/DBMODEL/METADATA/TABLES/TABLE[@ID=$relation/@SrcTable]/@Tablename"/>
        </xsl:attribute>
 
        <!-- name -->
        <xsl:attribute name="name">
            <xsl:value-of select="$relation/@RelationName"/>
        </xsl:attribute>
 
        <!-- onDelete -->
        <xsl:attribute name="onDelete">
 
            <xsl:variable name="actionId">
                <xsl:call-template name="str_replace">
                    <xsl:with-param name="stringIn" select="substring-before(substring-after($relation/@RefDef,'\n'), '\n')"/>
                    <xsl:with-param name="charsIn" select="'OnDelete='"/>
                    <xsl:with-param name="charsOut" select="''"/>
                </xsl:call-template>
            </xsl:variable>
 
            <xsl:call-template name="get_actiontype">
                <xsl:with-param name="id" select="$actionId" />
            </xsl:call-template>
 
        </xsl:attribute>
 
        <!-- === reference tag === -->
        <xsl:call-template name="build_fk">
            <xsl:with-param name="stringIn" select="$relation/@FKFields"/>
        </xsl:call-template>
 
    </foreign-key>
</xsl:template>
 
<!--
============================================================
============================================================ template "functions"
============================================================
-->
 
<!-- ============================================================ get_datatype -->
<xsl:template name="get_datatype">
    <xsl:param name="id"/>
 
    <xsl:variable name="type">
        <xsl:value-of select="/DBMODEL/SETTINGS/DATATYPES/DATATYPE[@ID=$id]/@TypeName"/>
    </xsl:variable>
 
    <xsl:choose>
        <xsl:when test="$type = 'DATETIME'" >TIMESTAMP</xsl:when>
        <xsl:when test="$type = 'TEXT'" >LONGVARCHAR</xsl:when>
        <xsl:when test="$type = 'BOOL'" >BOOLEAN</xsl:when>
        <xsl:otherwise>
            <xsl:value-of select="$type"/>
        </xsl:otherwise>
    </xsl:choose>
 
</xsl:template>
 
 
<!-- ============================================================ get_datasize -->
<xsl:template name="get_datasize">
    <xsl:param name="dtpc"/>
    <xsl:param name="dtype"/>
 
    <xsl:choose>
        <xsl:when test="contains('FLOAT,DOUBLE',$dtype)" >
            <xsl:value-of select="substring-before($dtpc,',')"/>
        </xsl:when>
        <xsl:otherwise>
            <xsl:value-of select="$dtpc"/>
        </xsl:otherwise>
    </xsl:choose>
 
</xsl:template>
 
 
<!-- ============================================================ clean_dataparams -->
<xsl:template name="clean_dataparams">
    <xsl:param name="dtp"/>
 
    <xsl:variable name="dtp2">
        <xsl:call-template name="str_replace">
            <xsl:with-param name="stringIn" select="$dtp"/>
            <xsl:with-param name="charsIn" select="'('"/>
            <xsl:with-param name="charsOut" select="''"/>
        </xsl:call-template>
    </xsl:variable>
 
    <xsl:call-template name="str_replace">
        <xsl:with-param name="stringIn" select="$dtp2"/>
        <xsl:with-param name="charsIn" select="')'"/>
        <xsl:with-param name="charsOut" select="''"/>
    </xsl:call-template>
 
</xsl:template>
 
 
<!-- ============================================================ str_replace -->
<xsl:template name="str_replace">
    <xsl:param name="stringIn"/>
    <xsl:param name="charsIn"/>
    <xsl:param name="charsOut"/>
    <xsl:choose>
        <xsl:when test="contains($stringIn,$charsIn)">
            <xsl:value-of select="concat(substring-before($stringIn,$charsIn),$charsOut)"/>
            <xsl:call-template name="str_replace">
                <xsl:with-param name="stringIn" select="substring-after($stringIn,$charsIn)"/>
                <xsl:with-param name="charsIn" select="$charsIn"/>
                <xsl:with-param name="charsOut" select="$charsOut"/>
            </xsl:call-template>
        </xsl:when>
        <xsl:otherwise>
            <xsl:value-of select="$stringIn"/>
        </xsl:otherwise>
    </xsl:choose>
</xsl:template>
 
 
<!-- ============================================================== build_fk -->
<xsl:template name="build_fk">
    <xsl:param name="stringIn"/>
    <xsl:variable name="FKClean">
        <xsl:value-of select="substring-before($stringIn, '\n')"/>
    </xsl:variable>
    <reference>
    <xsl:attribute name="local">
        <xsl:value-of select="substring-after($FKClean, '=')"/>
    </xsl:attribute>
    <xsl:attribute name="foreign">
        <xsl:value-of select="substring-before($FKClean, '=')"/>
    </xsl:attribute>
    </reference>
    <xsl:if test="contains(substring-after($stringIn,'\n'),'=')">
        <xsl:call-template name="build_fk">
            <xsl:with-param name="stringIn" select="substring-after($stringIn,'\n')"/>
        </xsl:call-template>
    </xsl:if>
</xsl:template>
 
<!-- ======================================================== get_actiontype -->
 
<xsl:template name="get_actiontype">
    <xsl:param name="id"/>
 
    <xsl:choose>
        <xsl:when test="$id = 0">restrict</xsl:when>
        <xsl:when test="$id = 1">cascade</xsl:when>
        <xsl:when test="$id = 2">setnull</xsl:when>
        <xsl:otherwise>restrict</xsl:otherwise>
    </xsl:choose>
</xsl:template>
 
</xsl:stylesheet>
 

Enjoy it, cause it saves a lot of time!

by Thomas Schäfer on 2008-03-19, tagged dbdesigner  model  transform  xml  xsl 

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)

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 

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 

Doctrine-build-all(-load) workaround

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
by Halil Köklü on 2007-07-25, tagged batch  doctrine  model 

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)

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 

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 

Populate Object Filter

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.

by Stephen Riesenberg on 2007-01-06, tagged filter  model  object 
(2 comments)

Simulating an enum column type in the model

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)

by Francois Zaninotto on 2006-10-27, tagged enum  getter  model  mysql  setter 
(8 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)

sfPakeTransformTinyint.php

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

by Alex Gemini on 2006-08-28, tagged cli  generator  model  pake  task 
(5 comments)