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>
 

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

You might've noticed that the default behavior of propel is to use numeric retrieval when grabbing rows from the database, and it is hard to override this action.

Sure you can do a custom query, but if you want to use criteria methods instead, this class will be helpful in grabbing a a result set via associative (thus preserving your associative keys and making coding easier on you).

I'd consider this beta level code. It's worked twice for me, both when I had a complex join that I wanted to use criteria for but failed once on a simpler query.

I haven't been able to decode why exactly the failure occured yet.

<?php
/*
*
*  @author: Kum Sackey
* 
*  Defines extension to propel that tweak its core behavior
* 
*/
class sfPropelExtension
{
    /**
    * @desc Return a result select from a criteria object. Unlike the implementation in BasePeer::doSelect(), this version returns the result set in associative mode
    */
    public static function getRSFromCriteria($criteria)
    {
        if(!($criteria instanceOf Criteria))
            throw new sfException('Passed parameter must be an instance of criteria, instead is of type: '.gettype($c));
 
        $dbMap = Propel::getDatabaseMap($criteria->getDbName());
        $con = Propel::getConnection();            
 
        $params = array();
 
        $sql = BasePeer::createSelectSql($criteria, $params);
        $stmt = $con->prepareStatement($sql);            
        $stmt->setLimit($criteria->getLimit());
        $stmt->setOffset($criteria->getOffset());            
 
        self::populateStmtValues($stmt, $params, $dbMap);                        
 
        $rs = $stmt->executeQuery($sql, ResultSet::FETCHMODE_ASSOC);  
 
        return $rs;        
    }
 
    /**
     * Populates values in a prepared statement.
     *
     * @param PreparedStatement $stmt
     * @param array $params array('column' => ..., 'table' => ..., 'value' => ...)
     * @param DatabaseMap $dbMap
     * @return int The number of params replaced.
     */
    private static function populateStmtValues($stmt, $params, DatabaseMap $dbMap)
    {
        $i = 1;
        foreach($params as $param) {
            $tableName = $param['table'];
            $columnName = $param['column'];
            $value = $param['value'];
 
            if ($value === null) {
                $stmt->setNull($i++);
            } else {
                $cMap = $dbMap->getTable($tableName)->getColumn($columnName);
                $setter = 'set' . CreoleTypes::getAffix($cMap->getCreoleType());
                $stmt->$setter($i++, $value);
            }
        }
    } 
}
 

The problem

Let us take the example of snipeet! :-) The function that selects snippets by tags is a custom SQL query. When filtering by tags there is no pager anymore.

Why is that? The problem is that sfPropelPaginate only works with a Criteria object, not with a raw query. As a result you cannot have pagination with raw SQL query which is a rather severe limitation.

The solution

The solution is to first encode the parameter of the query in the criteria (that's the tricky bit) and then to set up a custom peer method.

As i said earlier we will take a simplified version of snipeet as an example. We assume that we have a function that creates a sql statement from a list of tags.

Here we go about setting the dummy criteria that encodes an array of tags:

getCriteriaFromTags($tags)
{
  $c = new Criteria();
  for ($i = 1; $i <= count($tags); ++$i)
  {
    $prefix = 't'.$i;
    // note that that SnippetTagPeer::TABLE_NAME  SnippetTagPeer::NAME are in fact arbitrary
    // any other table and table.column combination would work as well
    $c->addAlias($prefix.SnippetTagPeer::TABLE_NAME, SnippetTagPeer::TABLE_NAME);
    $c->add($prefix.SnippetTagPeer::NAME, $tags[$i-1]);
  }
  return $c;
}

Now you can initialise a sfPropelPaginate object as usual with that Criteria object. When that is done you tell it to use your custom peer method:

$pager->setPeerMethod('doSelectByTags');

Next you code the doSelectByTags method that performs the actual selection from a criteria object:

function doSelectByTags($c)
{
  // first you fetch the tags from the query
  $tags = array();
  foreach($c->keys() as $key)
    $tags[] = $c->get($key);
 
  // here comes your custom sql query 
  // it basically creates a $statement variable from the $tags array
  // .....
 
  // now you have to add the limit and offset:
 $statement->setLimit($c->getLimit());
 $statement->setOffset($c->getOffset());
 // and the rest of the code is as usual
}