Relating to the post http://www.symfony-project.org/forum/index.php/m/32510/?srch=insert+values+propel

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('ScToolkit::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\')';
    $phpNameMap = array();
    eval('$phpNameMap = array_flip('.$classPeer.'::getPhpNameMap());');
 
    //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($dataKey=='CREATED_AT' || $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);
 
    $phpNameMap = array();
    eval('$phpNameMap = array_flip('.$classPeer.'::getPhpNameMap());');
 
    $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);
 

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.

Hi!

I am using a lot of hand written jscript codes due to the required behavior not found in any javascript framework. I've created my Objects in distinct files, although some of them are quite small ( <1kb ). So to make it fast, I've written a filter class that actually parses the files found in the /js directories having *.js suffix. It does not only parse them together but removes whitespaces, comments, indentations so the sum filesize gets a bit compressed. The filter also includes the final file into the response so there is no need to include any *.js ( inside /js ) in a template.

<?php
 
    class JavascriptParser extends sfFilter
    {
      public function execute($filterChain)
      {
        $filterChain->execute();
 
 
        $fp = fopen( "js/compiled.js", "w" );
 
        JavascriptParser::getJSFiles( "js", $fp );
 
        fclose ( $fp );
 
        $response = $this->getContext()->getResponse();
        $content = $response->getContent();
        if (false !== ($pos = strpos($content, '</head>')))
        {
          $html = "<script type='text/javascript' src='/js/compiled.js'></script>";
 
          if ($html)
          {
            $response->setContent(substr($content, 0, $pos).$html.substr($content, $pos));
          }
        }
 
      }
 
      public function getJSFiles( $dir, &$fp )
      {
        $hDir = opendir( $dir );
        while( ( $filename = readdir( $hDir ) ) !== false )
        {
            if ( is_dir( $filename ) )
            {
            }
            else if ( is_dir( $dir."/".$filename ) )
                JavascriptParser::getJSFiles( $dir."/".$filename, $fp );                
            else if ( strpos( $filename, ".js" ) !== false && $filename != "compiled.js" )
            {
                $tmpFile = fopen( $dir."/".$filename, "r" );
                $data = fread( $tmpFile, filesize( $dir."/".$filename ) );
 
                $data = preg_replace( "'\/\*.*?\*\/'si", "", $data  );
                $data = preg_replace( "'//.*?\n'si", "", $data );
                $data = preg_replace( "'[ \t]+'", " ", $data );
 
                fwrite( $fp, " \n".$data );
                fclose( $tmpFile );
            }
        }
        closedir( $hDir );  
      }
    }
 
?>
 

The same should be done with *.css files, since they are even smaller and loading many small files takes much more time then loading one big.

Best Regards

This system will allow the inclusion of css automaticaly. You must create a folder structure in the web/css folder matching one or more of these naming patern.

web/css/[module_name].css

web/css/[module_name]/main.css

web/css/[module_name]/[action_name].css

class myPHPView extends sfPHPView
{
  /**
   * Executes any presentation logic for this view.
   */
  public function execute()
  {
    $this->addDefaultStylesheet();
  }
 
  public function addDefaultStylesheet()
  {  
      $response = $this->getContext()->getResponse();
 
      $sf_web_dir = sfConfig::get('sf_web_dir') . '/css/';
 
      $module_file = $this->moduleName . '.css';
      $main_module_file =  $this->moduleName . '/main.css';
      $action_file = $this->moduleName . '/' . $this->actionName . '.css';
 
      if( is_readable( $sf_web_dir . $module_file ) ){
         $response->addStylesheet($module_file);
      }
      if( is_readable( $sf_web_dir . $main_module_file ) ){
         $response->addStylesheet($main_module_file);
      }
      if( is_readable( $sf_web_dir . $action_file ) ){
         $response->addStylesheet($action_file);
      }
  }
}
 

To change this you also need to change the module.yml and put this

all:
  view_class:       myPHP
 

I made a small more general modification to the edit_in_place update action, you can use that in any action

class myTools {
 
 
  /**
   * performs update on any single column for ajax actions
   *
   * @param     string  $peer
   * @param     integer $id
   * @param     string  $field
   * @return    object
   */
  public static function updateField($peer, $id, $field, $value) {
    if (!class_exists($peer)) {
        throw new InvalidArgumentException($peer.' does not exist');
    }
 
    $method = new ReflectionMethod($peer, 'retrieveByPk');
    $object = $method->invoke(NULL, $id);
 
    $object->setByName($field, $value, BasePeer::TYPE_FIELDNAME);
    $object->save();
    return $object;
  }
 

I haven't found a way to use subqueries as alias with criteria in the book.

Here is a small piece of code that works very well:

the subquery is in an alias defined by Criteria::addAsColumn($alias, $expr)

it can be used to order the result, or in case you have to deal with foreign keys, etc.

$c = new Criteria();
 
$c->addAsColumn('brandname', '(SELECT brand.name FROM brand WHERE brand.id=brand_id)');
 
$c->addAscendingOrderByColumn($c->getColumnForAs('brandname'));
 
$this->products = ProductPeer::doSelect($c);
 

Product has a brand_id that references the id key of the brand table that contains the 'name' field which is used to order the result.

Patrice Blanchardie

Hello everyone,

i accidently found a neat feature of MYSQL on the web, which isn't even documented in the official MySQL 5.x manual (original blog entry to be found at http://www.cfdan.com/posts/Handy_MySQL_-_ORDER_BY_FIELD.cfm">original Blog Posting)

Basicly i needed a way to create some custom order for my records, so i started to build a raw SQL query. Then i ran into troubles when i tried to get this working with a pager.. all in all, i tried it to solve it with Criteria.

I created a new class, myCriteria which inherits from the original Criteria class.

/**
 * Add order by column name with a custom Order for a given Field
 *
 * @param unknown_type $propertyBracket
 * @return unknown
 */
public function addCustomOrderByColum($propertyField, $propertyList) {
    $this->orderByColumns[] = 'FIELD('.$propertyField.', ' . $propertyList .' )';
    return $this;
}
 

An example could look like:

$c->addCustomOrderByColum(CustomPeer::TYPE, " 'Sold', 'Pending', 'Announced' ");
 

This way you can create ORDER BY Statements which are not bound to common ASC/DESC rules.

When using fillin filter, after validation success (all form fields are ok) if you want display page without form, fillin filter is still enabled and throws exception "Exception - No form found in this page". Use this snippet to prevent it.

in sfFillInFormFilter.class.php file

  public function execute($filterChain)
  {
    // execute next filter
    $filterChain->execute();
 
    $context  = $this->getContext();
    $response = $context->getResponse();
    $request  = $context->getRequest();
 
    // add these two lines
    if(! $request->hasErrors())
        return;
 

Currently i had to do different validations of data depending the data entered by the user.

The solution i found, using the symfony built-in yml validation was:

action:

$validated = true;
$validationConfig = $this->getModuleName().'/'.sfConfig::get('sf_app_module_validate_dir_name').'/'.$validationFile.'.yml';
 
if (null !== $validateFile = sfConfigCache::getInstance()->checkConfig(sfConfig::get('sf_app_module_dir_name').'/'.$validationConfig, true))
{
    $context = $this->getContext();
    $validatorManager = new  sfValidatorManager();
        $validatorManager->initialize($context);
    require($validateFile);
    $validated = $validatorManager->execute();
}
 

refactoring:

public function validateParamsYml($validationFile) {
        $validated = true;
        $validationConfig = $this->getModuleName().'/'.sfConfig::get('sf_app_module_validate_dir_name').'/'.$validationFile.'.yml';
 
        if (null !== $validateFile = sfConfigCache::getInstance()->checkConfig(sfConfig::get('sf_app_module_dir_name').'/'.$validationConfig, true))
        {
            $context = $this->getContext();
            $validatorManager = new sfValidatorManager();
            $validatorManager->initialize($context);
            require($validateFile);
            $validated = $validatorManager->execute();
        }
        return $validated;
    }
 

Then you can call any validations rules at your action:

public function executeXXX() {
  [...]
  [...]
  $this->validateParamsYml('yml_1');
  $this->validateParamsYml('yml_2');
  [...]
}
 

and finally check if everything was validated then forward/save the data:

if($this->getRequest()->hasErrors()) {
            return $this->handleErrorXXXX();
        }
 

Cheers

This was asked on the forum, so I thought I would place it here too.

Problem

Suppose you have a model that resembles the following:

  range:
    id:                                   
    start_date: {type: date}
    end_date: {type: date}

And you want to know the following: are there any records where either $date1 or $date2 is between start_date and end_date?

Consider that $date2 could be some fixed $offset from $date1, so that the question becomes: are there any records which partially cover the $offset period following $date1?

Using Criterion Objects

Since the BETWEEN construct is unavailable through Propel (at least not as an object or constant), we have to use the knowledge that:

if
  a >= x
and
  a <= y
then
  x <= a <= y

Which gives us:

$c = new Criteria();
 
$date1 = '2007-08-20';
$date2 = '2008-08-20';
 
// test against date1
$date1Criterion = $c->getNewCriterion(RangePeer::START_DATE, $date1, Criteria::LESS_EQUAL);
 
// test against date2
$date2Criterion = $c->getNewCriterion(RangePeer::START_DATE, $date2, Criteria::LESS_EQUAL);
 
// conjunction
$date1Criterion->addAnd($c->getNewCriterion(RangePeer::END_DATE, $date1, Criteria::GREATER_EQUAL));
$date2Criterion->addAnd($c->getNewCriterion(RangePeer::END_DATE, $date2, Criteria::GREATER_EQUAL));
 
// disjunction
$date1Criterion->addOr($date2Criterion);
$c->add($date1Criterion);

Using Criteria::CUSTOM

Or if you absolutely must have your BETWEEN, try this:

$c = new Criteria();
 
$date1 = '2007-08-20';
$date2 = '2008-08-20';
 
$c->add(RangePeer::START_DATE, "'{$date1}' between ".RangePeer::START_DATE." and ".RangePeer::END_DATE, Criteria::CUSTOM);
$c->addOr(RangePeer::START_DATE, "'{$date2}' between ".RangePeer::START_DATE." and ".RangePeer::END_DATE, Criteria::CUSTOM);

Note that the first arguments to Criteria::add() and Criteria::addOr() can be any column, but they must be the same column.

Note also that when providing the argument, {$dateN} appears between single quotes. It must appear quoted (and escaped) in order for this query to return correct (or any) results.