Snippets tagged "propel database"

ordered by date get by popularity collapse all

Default culture content for i18n table

The table i18n doesn't support default culture. This snippet add the support of the default culture.

This snippet is a port of the snippet "default culture content fallback for i18n tables" for Symfony 1.2

Fallback I18n content

To enable the fallback, edit your table object class in lib/model/TableClassName.php and add the following code. Then search and replace TableClassName by your table object class name.

  /**
   * Fetch the i18n object for this object culture.
   *
   * @param   string          $culture    The culture to set
   * @return  mixed                       A i18n object
   * @throws  PropelException             Any exceptions caught during processing will be rethrown wrapped into a PropelException.
   * @link    http://snippets.symfony-project.org/snippet/237 -- modified for Symfony 1.2
   */
  public function getCurrentTableClassNameI18n($culture = null)
  {
    if (is_null($culture)) {
      $culture = is_null($this->culture) ? sfPropel::getDefaultCulture() : $this->culture;
    }
 
    if (!isset($this->current_i18n[$culture]))
    {
      $obj = TableClassNameI18nPeer::retrieveByPK($this->getId(), $culture);
      if ($obj !== null) { // Test if there is a translation for current culture
        $this->setTableClassNameI18nForCulture($obj, $culture);
      } else { // Create a translation for this culture
        $new_i18n = new TableClassNameI18n();
 
        $default_culture = sfConfig::get('sf_default_culture');
 
        // We try to fetch the default culture translation to initialise the new culture.
        if (!isset($this->current_i18n[$default_culture])) {
          $obj = TableClassNameI18nPeer::retrieveByPK($this->getId(), $default_culture);
          if ($obj !== null) { // Test if there is a translation for current culture
            $this->setTableClassNameI18nForCulture($obj, $default_culture);
          }
        } else {
          $obj = $this->current_i18n[$default_culture];
        }
 
        if ($obj !== null) {
          $obj->copyInto($new_i18n);
        }
 
        $new_i18n->setId($this->getId());
        $new_i18n->setCulture($culture);
 
        $this->setTableClassNameI18nForCulture($new_i18n, $culture);
      }
    }
 
    return $this->current_i18n[$culture];
  }

Saving fallback values at creation

You now need to add default translation when you create a new object. We do that with this doSave function.

To use this function, edit your table object class in lib/model/TableClassName.php and add the following code. Then search and replace TableClassName by your table object class name.

  /**
   * Stores the object in the database while setting default culture if necessary.
   *
   * If the object is new, it inserts it; otherwise an update is performed.
   * All related objects are also updated in this method.
   *
   * @param      Connection      $con The database connection
   * @return     int             The number of rows affected by this insert/update and any referring fk objects' save() operations.
   * @throws     PropelException Any exceptions caught during processing will be rethrown wrapped into a PropelException.
   * @see        save()
   * @link       http://snippets.symfony-project.org/snippet/237 -- modified for Symfony 1.2
   */
  protected function doSave(PropelPDO $con)
  {
    $default_culture = sfConfig::get('sf_default_culture');
    $current_culture = is_null($this->culture) ? sfPropel::getDefaultCulture() : $this->culture;
    $obj = null;
 
    // We try to fetch the default culture translation to initialise the new culture.
    if (!isset($this->current_i18n[$default_culture])) {
      $obj = TableClassNameI18nPeer::retrieveByPK($this->getId(), $default_culture, $con);
      if ($obj !== null) { // Test if there is a translation for current culture
        $this->setTableClassNameI18nForCulture($obj, $default_culture);
      }
    } else {
      $obj = $this->current_i18n[$default_culture];
    }
 
    if($obj === null && isset($this->current_i18n[$current_culture])) {
      $new_i18n = new TableClassNameI18n();
      $this->current_i18n[$current_culture]->copyInto($new_i18n);
 
      $new_i18n->setId($this->getId());
      $new_i18n->setCulture($default_culture);
 
      $this->setTableClassNameI18nForCulture($new_i18n, $default_culture);
    }
 
    return parent::doSave($con);
  }

Fetching a list with default translations

To complete this snippet, here is a fallback version of doSelectWithI18n.

To enable the fallback, edit your table object peer class in lib/model/TableClassNamePeer.php and add the following code. Then search and replace TableClassName by your table object class name.

  /**
   * Selects a collection of TableClassName objects pre-filled with their i18n objects.
   *
   * @param     Criteria         $criteria   The criteria's object
   * @param     string           $culture    The selected culture.
   * @param     PropelPDO        $con        An optional database connection
   * @return    array                        Array of TableClassName objects.
   * @throws    PropelException              Any exceptions caught during processing will be rethrown wrapped into a PropelException.
   * @link      http://snippets.symfony-project.org/snippet/237 -- modified for Symfony 1.2
   */
  public static function doSelectWithI18n(Criteria $criteria, $culture = null, PropelPDO $con = null)
  {
    $criteria = clone $criteria;
 
    if ($culture === null) {
      $culture = sfContext::getInstance()->getUser()->getCulture();
    }
 
    $default_culture = sfConfig::get('sf_default_culture');
 
    // Set the correct dbName if it has not been overridden
    if ($criteria->getDbName() == Propel::getDefaultDB()) {
      $criteria->setDbName(self::DATABASE_NAME);
    }
 
    TableClassNamePeer::addSelectColumns($c);
    $startcol = (TableClassNamePeer::NUM_COLUMNS - TableClassNamePeer::NUM_LAZY_LOAD_COLUMNS) + 1;
 
    TableClassNameI18nPeer::addSelectColumns($c);
 
    $criteria->addJoin(TableClassNamePeer::ID, TableClassNameI18nPeer::ID);
    $criterion = $criteria->getNewCriterion(TableClassNameI18nPeer::CULTURE, $culture);
    $criterion->addOr($criteria->getNewCriterion(TableClassNameI18nPeer::CULTURE, $default_culture));
    $criteria->add($criterion);
 
    $stmt = BasePeer::doSelect($c, $con);
    $results = array();
    $uncultured_results = array();
 
    while($row = $stmt->fetch(PDO::FETCH_NUM)) {
 
      $obj1 = new TableClassName();
      $obj1->hydrate($row);
      $obj1->setCulture($culture);
 
      if(isset($results[$obj1->getId()])) {
        $obj1 = $results[$obj1->getId()];
      }
 
      $omClass = TableClassNameI18nPeer::getOMClass($row, $startcol);
 
      $cls = Propel::importClass($omClass);
      $obj2 = new $cls();
      $obj2->hydrate($row, $startcol);
 
      $obj1->setTableClassNameI18nForCulture($obj2, $obj2->getCulture());
      $obj2->setTableClassName($obj1);
 
      if(!isset($uncultured_results[$obj1->getId()])) {
        $uncultured_results[$obj1->getId()] = $obj1;
      }
 
      if($obj2->getCulture() == $culture) {
        $uncultured_results[$obj1->getId()] = false;
      }
 
      if(!isset($results[$obj1->getId()])) {
        $results[$obj1->getId()] = $obj1;
      } elseif($obj2->getCulture() == $culture) {
        // Move result to the end of results array to fit eventual sort
        // criteria (ugly fix).
        unset($results[$obj1->getId()]);
        $results[$obj1->getId()] = $obj1;
      }
    }
 
    foreach ($uncultured_results as $obj1) {
      if ($obj1) {
        $obj1->setCulture($default_culture);
        $default_culture_object = $obj1->getCurrentTableClassNameI18n();
        if ($default_culture_object) {
          $obj2 = new TableClassNameI18n();
          $default_culture_object->copyInto($obj2);
          $obj2->setCulture($culture);
          $obj2->setTableClassName($obj1);
          $obj1->setTableClassNameI18nForCulture($obj2, $obj2->getCulture());
        }
        $obj1->setCulture($culture);
      }
    }
 
    return array_values($results);
  }

Counting results filtered on translations

Before enable the fallback, you must create a constant in your table object peer class in lib/model/TableClassNamePeer.php and replace table by your table name

const COUNT_DISTINCT = 'COUNT(DISTINCT table.ID)';

  /**
   * Returns the number of rows matching criteria with I18N criteria.
   *
   * @param       Criteria    $criteria   The criteria's object
   * @param       boolean     $distinct   Whether to select only distinct columns (You can also set DISTINCT modifier in Criteria).
   * @param       Connection  $con        An optional database connection
   * @param       string      $culture    The selected culture.
   * @return      int                     Number of matching rows.
   * @link        http://snippets.symfony-project.org/snippet/237 -- modified for Symfony 1.2
   */
  public static function doCountWithI18n(Criteria $criteria = null, $distinct = false, PropelPDO $con = null, $culture = null)
  {
    // we're going to modify criteria, so copy it first
    if ($criteria === null){
      $criteria = new Criteria();
    } else {
      $criteria = clone $criteria;
    }
 
    $default_culture = sfConfig::get('sf_default_culture');
 
    if ($culture === null) {
      // We use current user culture.
      $culture = sfContext::getInstance()->getUser()->getCulture();
    }
 
    // clear out anything that might confuse the ORDER BY clause
    $criteria->clearSelectColumns()->clearOrderByColumns();
    $criteria->addSelectColumn(TableClassNamePeer::COUNT_DISTINCT);
 
    // just in case we're grouping: add those columns to the select statement
    foreach($criteria->getGroupByColumns() as $column)
    {
      $criteria->addSelectColumn($column);
    }
 
    $criteria->addJoin(TableClassNamePeer::ID, TableClassNameI18nPeer::ID);
    $criterion = $criteria->getNewCriterion(TableClassNameI18nPeer::CULTURE, $culture);
    $criterion->addOr($criteria->getNewCriterion(TableClassNameI18nPeer::CULTURE, $default_culture));
    $criteria->add($criterion);
 
    $rs = TableClassNamePeer::doSelectStmt($criteria, $con);
    if ($res = $rs->fetchColumn(0)) {
      return $res;
    } else {
      // no rows returned; we infer that means 0 matches.
      return 0;
    }
  }

by Simon Leblanc on 2009-09-20, tagged culture data database i18n object propel symfony12

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:

a Helper to perform manipulations on standard Criteria
new Builders to generate slightly more functions for your object and peer-classes
a Patch for Propel with a small improvement to allow lazy resolving of the realTable name (the Propel community is somewhat harder to get into contact with, since everything gets marked as spam)

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)

Creating SQL for multiple insert

Relating to the post http://www.symfony-project.org/forum/index.php/m/32510/

I've created tools to generate SQL.

PS. Done with haste and for postgresSQL.

Can be edited to take in array of objects and form a whole SQL but I don't want to keep a large array of objects.

CREATED_AT, UPDATED_AT forced as NOW()

  /**
  * To generate sql values for insert statement
  * 
  * @param  $object           object      the object with relavant data
  * 
  * @return $valueSql         string     the object values string
  */
  public static function getObjectInsertValue($object)
  {
    if(!$object->isNew())
    {
        throw new Exception('Toolkit::getObjectInsertValue($object) Only usable with new object.');
      return '';
    }
 
    $className = get_class($object);
    $classPeer = get_class($object->getPeer());
    $tableName = "";
    eval('$tableName = '.$classPeer.'::TABLE_NAME;');
    //For postgres insert (postgres need define Primary Key)
    $nextId = 'nextval(\''.$tableName.'_seq\')';
 
    eval('$objectKeys = '.$classPeer.'::getFieldNames(BasePeer::TYPE_PHPNAME);');
    eval('$dataKeys = '.$classPeer.'::getFieldNames(BasePeer::TYPE_FIELDNAME);');
    $phpNameMap = array_combine($dataKeys, $objectKeys);
 
    //For checking primary key
    $object->setPrimaryKey(1);
    $object->resetModified();
    $object->setPrimaryKey(2);
 
    $valueArray = array();
    foreach($phpNameMap as $dataKey=>$objectKey)
    {
      $isPrimaryKey = false;
      $columnName = "";
      eval('$columnName = '.$classPeer.'::'.$dataKey.';');
      if($object->isColumnModified($columnName))
      {
        $isPrimaryKey = true;
      }
      if(!$isPrimaryKey)
      {
        if(strtoupper($dataKey)=='CREATED_AT' || strtoupper($dataKey)=='UPDATED_AT')
        {
          $valueArray[] = 'NOW()';
        }
        else
        {
          if(is_null($object->getByName($objectKey)))
          {
            $valueArray[] = 'NULL';
          }
          else
          {
            $data = $object->getByName($objectKey);
            $wrapper = "";
            if(is_string($data))
            {
                $wrapper = "'";
              $data = pg_escape_string($data);
            }
            $valueArray[] = $wrapper.$data.$wrapper;
          }
        }
      }
      else
      {
        //For postgres insert (postgres need define Primary Key)
        eval('$valueArray[] = "'.$nextId.'";');
      }
    }
 
    $valueStr = '('.implode(", ", $valueArray).')';
    return $valueStr;
  }
 
  /**
  * To generate sql insert into for insert statement
  * 
  * @param  $classPeer        string    the object class peer string
  * 
  * @return $intoSql          string    the object insert into string
  */
  public static function getObjectInsertInto($classPeer)
  {
    $classObject = substr($classPeer, 0, -strlen('Peer'));
    $object = null;
    eval('$object = new '.$classObject.'();');
    //For checking primary key
    $object->resetModified();
    $object->setPrimaryKey(1);
 
    eval('$objectKeys = '.$classPeer.'::getFieldNames(BasePeer::TYPE_PHPNAME);');
    eval('$dataKeys = '.$classPeer.'::getFieldNames(BasePeer::TYPE_FIELDNAME);');
    $phpNameMap = array_combine($dataKeys, $objectKeys);
 
    $intoArray = array();
    foreach($phpNameMap as $dataKey=>$objectKey)
    {
      $isPrimaryKey = false;
      $columnName = "";
      eval('$columnName = '.$classPeer.'::'.$dataKey.';');
      if($object->isColumnModified($columnName))
      {
        $isPrimaryKey = true;
      }
 
      //For postgres insert (postgres need define Primary Key)        
      //if(!$isPrimaryKey)
      {
        $intoArray[] = $dataKey;
      }
    }
 
    $intoStr = '('.implode(", ", $intoArray).')';
    return $intoStr;
  }

How to use.

$inserts = array();
while($creatingObjects)
{
  $object = new ObjectClass();
  $object->setTitle("title");
  $object->setSummary("summary");
  $inserts[] = Toolkit::getObjectInsertValue($object);
}
 
$sql = sprintf('INSERT INTO %s %s', 
    ObjectPeer::TABLE_NAME, Toolkit::getObjectInsertInto('ObjectPeer')) . 
    ' VALUES ' . implode(', ', $inserts);
 
$con = Propel::getConnection();
$stmt = $con->createStatement();
$rs = $stmt->executeQuery($sql, ResultSet::FETCHMODE_NUM);

by Yi Sheng Yap on 2008-05-13, tagged database multiple propel sql

(2 comments)

exclude tables from propel-build-schema

The following patch will help you exclude the schema generation of certain tables when running propel-build-schema.

This might come in handy when you use propel-build-schema along with plugin schemas.

edit $sf_symfony_lib_dir/vendor/propel-generator/classes/propel/phing/PropelCreoleTransformTask.php

    protected function createDatabaseNode($dbInfo) {
 
        $this->log("Processing database");
 
        $node = $this->doc->createElement("database");
        $node->setAttribute("name", $dbInfo->getName());
 
        if ($vendorNode = $this->createVendorInfoNode($dbInfo->getVendorSpecificInfo())) {
            $node->appendChild($vendorNode);
        }
 
        global $schema_exclude_pattern;
        $pattern = $schema_exclude_pattern;
 
        $this->log("Exclude pattern : ".$pattern);
        // create and add table nodes
        foreach($dbInfo->getTables() as $table) {
            if (preg_match($pattern,$table->getName()))
            {
                $this->log("Skipping : ".$table->getName()." ( matches exclude pattern )");
                continue;
            }
            $tableNode = $this->createTableNode($table);
            $node->appendChild($tableNode);
        }
 
        return $node;
    }

pattern provided via the $schema_exclude_pattern variable which can be set in config.php (kinda ugly but it works)

config.php

<?php
//
// symfony directories
$sf_symfony_lib_dir  = '/bridge/lib/symfony/1.0/lib';
$sf_symfony_data_dir = '/bridge/lib/symfony/1.0/data';
 
// skips schema creation for tables which name matches the following pattern 
// when executing propel-build-schema
$schema_exclude_pattern = "/^sf_guard.*/i";

by Kostas Papadimitriou on 2007-12-15, tagged creole database generation propel propelbuildschema schema

default culture content fallback for i18n tables

By default, I18n content in database does not support fallback in default culture. This snippet allow you to enable I18n content fallback in order to always have a default value for your texts.

This snippet is sponsored by Dorigo consultants.

Fallback I18n content

To enable the fallback, edit your table object class in lib/model/TableClassName.php and add the following code. Then search and replace TableClassName by your table object class name.

  /**
   * Fetch the i18n object for this object culture.
   * 
   * @return     mixed A i18n object
   * @throws     PropelException Any exceptions caught during processing will be
   *     rethrown wrapped into a PropelException.
   */
  public function getCurrentTableClassNameI18n()
  {
    if (!isset($this->current_i18n[$this->culture]))
    {
      $obj = TableClassNameI18nPeer::retrieveByPK($this->getId(), $this->culture);
      if ($obj) // Test if there is a translation for current culture
      {
        $this->setTableClassNameI18nForCulture($obj, $this->culture);
      }
      else // Create a translation for this culture
      {
        $new_i18n = new TableClassNameI18n();
 
        $default_culture = sfConfig::get('sf_i18n_default_culture');
 
        // We try to fetch the default culture translation to initialise the new culture.
        if (!isset($this->current_i18n[$default_culture]))
        {
          $obj = TableClassNameI18nPeer::retrieveByPK($this->getId(), $default_culture);
          if ($obj) // Test if there is a translation for current culture
          {
            $this->setTableClassNameI18nForCulture($obj, $default_culture);
          }
        }
        else
        {
          $obj = $this->current_i18n[$default_culture];
        }
 
        if ($obj)
        {
          $obj->copyInto($new_i18n);
        }
 
        $new_i18n->setId($this->getId());
        $new_i18n->setCulture($this->culture);
 
        $this->setTableClassNameI18nForCulture($new_i18n, $this->culture);
      }
    }
 
    return $this->current_i18n[$this->culture];
  }

Saving fallback values at creation

You now need to add default translation when you create a new object. We do that with this doSave function.

To use this function, edit your table object class in lib/model/TableClassName.php and add the following code. Then search and replace TableClassName by your table object class name.

  /**
   * Stores the object in the database while setting default culture if necessary.
   *
   * If the object is new, it inserts it; otherwise an update is performed.
   * All related objects are also updated in this method.
   *
   * @param      Connection $con The database connection
   * @return     int The number of rows affected by this insert/update and any referring fk objects' save() operations.
   * @throws     PropelException Any exceptions caught during processing will be
   *     rethrown wrapped into a PropelException.
   * @see        save()
   */
  protected function doSave($con)
  {
 
    $default_culture = sfConfig::get('sf_i18n_default_culture');
 
    // We try to fetch the default culture translation to initialise the new culture.
    if (!isset($this->current_i18n[$default_culture]))
    {
      $obj = TableClassNameI18nPeer::retrieveByPK($this->getId(), $default_culture, $con);
      if ($obj) // Test if there is a translation for current culture
      {
        $this->setTableClassNameI18nForCulture($obj, $default_culture);
      }
    }
    else
    {
      $obj = $this->current_i18n[$default_culture];
    }
 
    if(!$obj && isset($this->current_i18n[$this->culture]))
    {
      $new_i18n = new TableClassNameI18n();
      $this->current_i18n[$this->culture]->copyInto($new_i18n);
 
      $new_i18n->setId($this->getId());
      $new_i18n->setCulture($default_culture);
 
      $this->setTableClassNameI18nForCulture($new_i18n, $default_culture);
    }
 
    return parent::doSave($con);
  }

Fetching a list with default translations

To complete this snippet, here is a fallback version of doSelectWithI18n.

  /**
   * Selects a collection of TableClassName objects pre-filled with their i18n objects.
   *
   * @param      Criteria $criteria
   * @param      string $culture The selected culture.
   * @param      Connection $con An optional database connection
   * @return     array Array of TableClassName objects.
   * @throws     PropelException Any exceptions caught during processing will be
   *     rethrown wrapped into a PropelException.
   */
  public static function doSelectWithI18n(Criteria $c, $culture = null, $con = null)
  {
    if ($culture === null)
    {
      $culture = sfContext::getInstance()->getUser()->getCulture();
    }
 
    $default_culture = sfConfig::get('sf_i18n_default_culture');
 
    // Set the correct dbName if it has not been overridden
    if ($c->getDbName() == Propel::getDefaultDB())
    {
      $c->setDbName(self::DATABASE_NAME);
    }
 
    TableClassNamePeer::addSelectColumns($c);
    $startcol = (TableClassNamePeer::NUM_COLUMNS - TableClassNamePeer::NUM_LAZY_LOAD_COLUMNS) + 1;
 
    TableClassNameI18nPeer::addSelectColumns($c);
 
    $c->addJoin(TableClassNamePeer::ID, TableClassNameI18nPeer::ID);
    $criterion = $c->getNewCriterion(TableClassNameI18nPeer::CULTURE, $culture);
    $criterion->addOr($c->getNewCriterion(TableClassNameI18nPeer::CULTURE, $default_culture));
    $c->add($criterion);
 
    $rs = BasePeer::doSelect($c, $con);
    $results = array();
    $uncultured_results = array();
 
    while($rs->next()) {
 
      $omClass = TableClassNamePeer::getOMClass();
 
      $cls = Propel::import($omClass);
      $obj1 = new $cls();
      $obj1->hydrate($rs);
      $obj1->setCulture($culture);
 
      if(isset($results[$obj1->getId()]))
      {
        $obj1 = $results[$obj1->getId()];
      }
 
      $omClass = TableClassNameI18nPeer::getOMClass($rs, $startcol);
 
      $cls = Propel::import($omClass);
      $obj2 = new $cls();
      $obj2->hydrate($rs, $startcol);
 
      $obj1->setTableClassNameI18nForCulture($obj2, $obj2->getCulture());
      $obj2->setTableClassName($obj1);
 
      if(!isset($uncultured_results[$obj1->getId()]))
      {
        $uncultured_results[$obj1->getId()] = $obj1;
      }
 
      if($obj2->getCulture() == $culture)
      {
        $uncultured_results[$obj1->getId()] = false;
      }
 
      if(!isset($results[$obj1->getId()]))
      {
        $results[$obj1->getId()] = $obj1;
      }
      elseif($obj2->getCulture() == $culture)
      {
        // Move result to the end of results array to fit eventual sort
        // criteria (ugly fix).
        unset($results[$obj1->getId()]);
        $results[$obj1->getId()] = $obj1;
      }
 
    }
 
    foreach($uncultured_results as $obj1)
    {
      if($obj1)
      {
        $obj1->setCulture($default_culture);
        $default_culture_object = $obj1->getCurrentTableClassNameI18n();
        if($default_culture_object)
        {
          $obj2 = new TableClassNameI18n();
          $default_culture_object->copyInto($obj2);
          $obj2->setCulture($culture);
          $obj2->setTableClassName($obj1);
          $obj1->setTableClassNameI18nForCulture($obj2, $obj2->getCulture());
        }
        $obj1->setCulture($culture);
      }
    }
 
    return array_values($results);
  }

Counting results filtered on translations

If you want to use a pager with a filter on translations, you will need this count method. Once this method present, the magic is done by :

    $criteria->setDistinct();
    $pager->setCriteria($criteria);
    $pager->setPeerMethod('doSelectWithI18n');
    $pager->setPeerCountMethod('doCountWithI18n');

Note : The setDistinct is very important for this snippet to work. It should not falsify your results, and without it, the doCountWithI18n method could return bad results.

  /**
   * Returns the number of rows matching criteria with I18N criteria.
   *
   * @param      Criteria $criteria
   * @param      boolean $distinct Whether to select only distinct columns (You can also set DISTINCT modifier in Criteria).
   * @param      Connection $con An optional database connection
   * @param      string $culture The selected culture.
   * @return     int Number of matching rows.
   */
  public static function doCountWithI18n(Criteria $criteria, $distinct = false, $con = null, $culture = null)
  {
    // we're going to modify criteria, so copy it first
    $criteria = clone $criteria;
 
    $default_culture = sfConfig::get('sf_i18n_default_culture');
 
    if ($culture === null)
    {
      // We use current user culture.
      $culture = sfContext::getInstance()->getUser()->getCulture();
    }
 
    // clear out anything that might confuse the ORDER BY clause
    $criteria->clearSelectColumns()->clearOrderByColumns();
    $criteria->addSelectColumn(TableClassNamePeer::COUNT_DISTINCT);
 
    // just in case we're grouping: add those columns to the select statement
    foreach($criteria->getGroupByColumns() as $column)
    {
      $criteria->addSelectColumn($column);
    }
 
    $criteria->addJoin(TableClassNamePeer::ID, TableClassNameI18nPeer::ID);
    $criterion = $criteria->getNewCriterion(TableClassNameI18nPeer::CULTURE, $culture);
    $criterion->addOr($criteria->getNewCriterion(TableClassNameI18nPeer::CULTURE, $default_culture));
    $criteria->add($criterion);
 
    $rs = TableClassNamePeer::doSelectRS($criteria, $con);
    if ($rs->next()) {
      return $rs->getInt(1);
    } else {
      // no rows returned; we infer that means 0 matches.
      return 0;
    }
  }

by Pierre-Yves Landuré on 2007-10-10, tagged culture data database i18n object propel

(4 comments)

Unit testing with Propel

Even though the book is quite good, the part in which unit testing is explained could use some better information. Especially the testing with a database connection (Propel in this case) needs some fixing.

Below you find an example unit test in which the database is accessed.

It tests a fictional class 'testClass' with the method 'load()'. That method accesses the database ... for which Propel needs to be running. The Symfony application that is loaded is called 'myapp';

if (!@constant('SF_APP')) { // Only load constants in not done before (group tests)
    define('SF_APP', 'myapp');
    define('SF_ENVIRONMENT', 'dev');
    define('SF_DEBUG', TRUE);
}
 
if (!@constant('SF_ROOT_DIR')) { // Only load constants in not done before (group tests)
    include(dirname(__FILE__).'/../bootstrap/unit.php');
}
 
sfCore::initSimpleAutoload(array(SF_ROOT_DIR.'/lib/model' // DB model classes
                                ,$sf_symfony_lib_dir // Symfony itself
                                ,dirname(__FILE__).'/../../lib' // Location class to be tested
                                ,dirname(__FILE__).'/../../apps/stageselect/lib' // Location myapp application
                                ,SF_ROOT_DIR.'/plugins')); // Location plugins
 
set_include_path($sf_symfony_lib_dir . '/vendor' . PATH_SEPARATOR . SF_ROOT_DIR . PATH_SEPARATOR . get_include_path());
 
/*
 * Start database connection and Symfony core
 */
sfCore::bootstrap($sf_symfony_lib_dir, $sf_symfony_data_dir);
sfContext::getInstance();
Propel::setConfiguration(sfPropelDatabase::getConfiguration());
Propel::initialize();
 
/*
 * Test
 */
// Init
$oTest = new lime_test(1, new lime_output_color());
 
// Print head
$oTest->diag('testClass');
$oTest->diag('----');
 
// Does the method load() exist in class 'testClass'
$oTest->can_ok('testClass', 'load', 'testClass has method load()');

by Jordi Backx on 2007-08-07, tagged database propel test unit

(1 comment)

Tutorial: How to make symfony/propel use both Sqlite and Mysql or many database handles

Multiple databases is not really the strongest area of Propel when using symfony, for this it is better recomanded the use of sfDoctrine plugin.

Still, some propel-symfony based projects may require that one or many modules will have to use different database handles, and to do that, first you need to define each database into your database.yml file.

You can have as many dbs you like and on any combination you want, but for this example we will have one mysql db and one sqlite db.

Your database.yml file should look like this:

all:

  database1:
    class:                sfPropelDatabase
    param:
      phptype:            mysql
      hostspec:           localhost
      database:           mysql_databse
      username:           my_username

  database2:
    class:                sfPropelDatabase
    param:
      phptype:            sqlite
      database:           ../data/sqlite_database.db

Once you have the databases defined you need to write the schema for each database you have defined and with this step you can decide which table (model) will use which handle.

In our case we'll have two schema.yml files because we have two dbs and they will look like this:

database1.schema.yml

---
database1:

  table_1:
    _attributes: 
      idMethod: native
    id: 
      type: INTEGER
      required: true
      autoIncrement: true
      primaryKey: true
    value: 
      type: VARCHAR
      size: 32
      required: true

  table_2: 
    _attributes: 
      idMethod: native
    id: 
      type: INTEGER
      required: true
      autoIncrement: true
      primaryKey: true
    value: 
      type: VARCHAR
      size: 32
      required: true

database2.schema.yml

---
database2:

  table_3:
    _attributes: 
      idMethod: native
    id: 
      type: INTEGER
      required: true
      autoIncrement: true
      primaryKey: true
    value: 
      type: VARCHAR
      size: 32
      required: true

Now is time to build the models

$ symfony propel-build-model

If you want to use other symfony commands that uses the database directly like:

$ symfony propel-build-sql
$ symfony propel-insert-sql

You'll have to configure the dbs connection into propel.ini.

Here we have a problem because you can't use both databases with propel.ini, so you'll have to do this one at a time.

database1 on propel.ini

propel.database            = mysql
propel.database.createUrl  = mysql://localhost/
propel.database.url        = mysql://localhost/mysql_database

database2 on propel.ini

propel.database            = sqlite
propel.database.createUrl  = sqlite://./../data/sqlite_database.db
propel.database.url        = sqlite://./../data/sqlite_database.db

Once you have the models generated and the data loaded into your databases, you can play with your databases.yml file.

If you generate the databases for every handle you'll like, you can switch a model from a handle to another.

by Dorin Mirulescu on 2007-06-19, tagged database propel

(8 comments)

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 }

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)

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)

Select a database dynamically

In app/lib/ create a file named myDBConnectionFilter.class.php and add:

class myDBConnectionFilter 
{
  public function initialize($filterChain) 
  {
    $db = sfContext::getInstance()->getDatabaseManager()->getDatabase('myschema');
    $db->setConnectionParameter('username', 'myusername');
    $db->setConnectionParameter('password', 'mypassword');
    $db->setConnectionParameter('hostspec', 'localhost');
    $db->setConnectionParameter('database', 'mydatabase');
 
    // The below line is optional - Symfony will connect anyway if no connection is present
    $db->connect();
 
  }
}

Then declare this filter to run on every page load by adding this to app/config/filters.yml:

myDBConnectionFilter:
  class: myDBConnectionFilter

Finally, Symfony will fail at the call above

sfContext::getInstance()->getDatabaseManager()->getDatabase('myschema');

because it does not know what type of database driver 'myschema' requires (MySQL/SQLite/etc.). You must add the following to app/config/databases.yml:

all:
  propel:
    class:          sfPropelDatabase
    param:
      phptype:            mysql

You can now dynamically select databases from the above class.

by Darren Schreiber on 2006-06-22, tagged connection database dynamic multiple propel select

(1 comment)

Change database connection settings dynamically

To modify the settings of a connection named 'propel':

$con = sfContext::getInstance()->getDatabaseConnection('propel');
$con->setConnectionParameter('username', 'foo');
$con->setConnectionParameter('password', 'bar');

This works for all the settings that can be defined in the databases.yml (more info in the sfPropelDatabase class source).

You must execute this code before the first query to the database. If you do that after a first query, it fails. This means that the best way to implement it is in a filter.

by Francois Zaninotto on 2006-06-20, tagged connection database propel

(1 comment)

Little trick to randomize results

More a PHP trick than symfony's one, but as you can't with propel randomize order of results, just do:

$c = new Criteria()
... fill your criteria there ...
$result = MyTablePeer::doSelect($c);
shuffle($result);

obvious? sorry, seen someone asking on IRC once :D

by Romain Dorgueil on 2006-05-29, tagged column criteria database order propel random

(8 comments)

Fetch database connection

$connection = sfContext::getInstance()->getDatabaseConnection('propel');

by Dustin Whittle on 2006-05-22, tagged creole database propel

(2 comments)

Snippets

Navigation

Refine Tags

Snippets tagged "propel database"

Default culture content for i18n table

Fallback I18n content

Saving fallback values at creation

Fetching a list with default translations

Counting results filtered on translations

performing custom joins and hydrations with Propel

Creating SQL for multiple insert

exclude tables from propel-build-schema

default culture content fallback for i18n tables

Fallback I18n content

Saving fallback values at creation

Fetching a list with default translations

Counting results filtered on translations

Unit testing with Propel

Tutorial: How to make symfony/propel use both Sqlite and Mysql or many database handles

foreignkey to sf_guard_user

Using multiple databases from Propel

Select a database dynamically

Change database connection settings dynamically

Little trick to randomize results

Fetch database connection

	About Installation Documentation Plugins Community Blog Development

Snippets