Code snippets for symfony 1.x

Navigation

Refine Tags

Snippets tagged "propel custom"

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)

Get a propel record set via associative

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);
            }
        }
    } 
}
 
by Kum Sackey on 2007-09-20, tagged associative  custom  propel  query 

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 

How to get a propel pager working with a custom SQL query

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
}
by Olivier Verdier on 2006-05-25, tagged custom  pager  pagination  propel  query  raw  sql