Code snippets for symfony 1.x

Navigation

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 

Comments on this snippet

gravatar icon
#1 Daniele Dore on 2009-01-22 at 04:38

This excellent snippet needs fix for working with sf 1.2, more info at: http://www.symfony-project.org/forum/index.php/m/32510/#66827

gravatar icon
#2 Yi Sheng Yap on 2009-03-04 at 05:52

Thanks Daniele for posting the link to the fix. :)

I had just updated the snippet with the fix, sorry for being so late in updating, was busy with work, health issues and trying to recall the id and password for this.