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

The problem seems to arise quite frequently: a page contains one Ajax link, but the remote function must update several divs on the page. Consider, for instance, the following template:

<h1>First zone to update</h1>
<div id="first_zone">
  Hello there
</div>
 
<h1>Second zone to update</h1>
<div id="second_zone">
  <p>How do you do, <strong>mate</strong>?
</div>
 
<h1>Ajax link</h1>
<?php echo use_helper('Javascript') ?>
<?php echo link_to_remote('click me', array(
  'url'    => 'test/ajax',
  'update' => 'result',
  'script' => true,
)) ?>
<div id="result">
</div>

What would the test/ajax action look like to update both the first_zone and the second_zone?

For the code of the action itself (executeAjax()), we'll ignore it since it really depends on what logic you put in your Ajax interaction. For this example, it will be empty.

The code of the template (ajaxSuccess.php) can be as follows:

<?php echo use_helper('Javascript') ?>
 
<?php slot('first_update') ?>
  So you like clicking, uh?
<?php end_slot() ?>
 
<?php slot('second_update') ?>
  <p>I'd like to test quotes (like "). </p>
  <p>And <strong>tags</strong>, too.</p>
<?php end_slot() ?>
 
<?php echo javascript_tag(
 
  update_element_function('first_zone', array(
    'content' => get_slot('first_update'),
  ))
  .
  update_element_function('second_zone', array(
    'content' => get_slot('second_update'),
  )) 
 
) ?>
 

Once rendered, the HTML code sent to the user will look like this:

<script type="text/javascript">
//<![CDATA[
$('first_zone').innerHTML = '  So you like clicking, uh?\n';
$('second_zone').innerHTML = '  <p>I\'d like to test quotes (like \"). </p>\n  <p>And <strong>tags</strong>, too.</p>\n';
//]]>
</script>

And this will do exactly what we wanted: update both zones with different content, in a single remote call. The interest of using the slot helpers is that you don't need to worry about escaping the content passed to the JavaScript function, and it looks really nice in your favorite syntax-highlighting text editor.

If you happen to do this a lot, maybe you will want to package the multiple updater into a helper. That's quite easy:

function update_elements_function($updates)
{
  $res = "";
  foreach($updates as $zoneName => $slotName)
  {
    $res .= update_element_function($zoneName, array('content' => get_slot($slotName)));
  }
  return javascript_tag($res);
}

Then you could replace the call to the javascript_tag() in the ajaxSuccess.php template by a simpler:

<?php echo update_elements_function(array(
  'first_zone'  => 'first_update',
  'second_zone' => 'second_update',
)) ?>

Performancewise, if the Ajax response is small (below 512 Bytes), you'd better use the JSON approach.

Quentin Garnier, in this snippet: http://www.symfony-project.com/snippets/snippet/57, suggested a way to have a link call multiple remote functions (with "in order" calls being enforced by a new 'wait' parameter). With a slightly modified version of his code, here are a few more functions to be added to you Javascript.php function in the symfony core:

function periodically_call_remotes($options = array())
{
  $frequency = isset($options[0]['frequency']) ? $options[0]['frequency'] : 10; // every ten seconds by default
  $code = 'new PeriodicalExecuter(function() {'.remote_functions($options).'}, '.$frequency.')';
 
  return javascript_tag($code);
}
 
function link_to_remotes($name, $options = array(), $html_options = array())
{
  return link_to_function($name, remote_functions($options), $html_options);
}
 
function remote_functions($options)
{
  // Multi ajax call
  $multi_function = "";
  // First pass (wait)
  for ($i = 0; isset($options[$i]); $i++) {
    if (isset($options[$i]['wait']) && $options[$i]['wait'] != $i && isset($options[$options[$i]['wait']])) {
      if (isset($options[$options[$i]['wait']]['complete'])) {
          $options[$options[$i]['wait']]['complete'] .= '; ' . remote_function($options[$i]);
      } else {
          $options[$options[$i]['wait']]['complete'] = remote_function($options[$i]);
      }
    }
  }
 
  // Second pass
  for ($i = 0; isset($options[$i]); $i++) {
    if (!(isset($options[$i]['wait']) && $options[$i]['wait'] != $i && isset($options[$options[$i]['wait']]))) {
      $multi_function .=  remote_function($options[$i]) . ';';
    }
  }
  return $multi_function;
}

To use it, you could do something like this:

<?php echo link_to_remotes('multi ajax',
    array(
        array(
            'update' => 'first_zone',
            'url' => '/module/action1',
            'loading' => "Element.show('indicator')"
        ),
        array(
            'update' => 'second_zone',
            'url' => '/module/action2',
            'wait' => 0 /* update in a second server call, after 'news_zone' returns */
        ),
        array(
            'update' => 'third_zone',
            'url' => '/module/action3',
            'wait' => 0
        ),
        array(
            'update' => 'fourth_zone',
            'url' => '/module/action4',
            'complete' => "Element.hide('indicator')",
            'wait' => 2 /* update in a third server call, after 'third_zone' returns */
        )
    )
) ?>

or this:

<?php echo periodically_call_remotes('multi ajax',
    array(
        array(
            'frequency' => 60 /* every 60 seconds, frequency only needs to be specified in first call, not subsequent calls */
            'update' => 'first_zone',
            'url' => '/module/action1',
            'loading' => "Element.show('indicator')"
        ),
        array(
            'update' => 'second_zone',
            'url' => '/module/action2',
            'wait' => 0 /* update in a second server call, after 'news_zone' returns */
        ),
        array(
            'update' => 'third_zone',
            'url' => '/module/action3',
            'wait' => 0
        ),
        array(
            'update' => 'fourth_zone',
            'url' => '/module/action4',
            'complete' => "Element.hide('indicator')",
            'wait' => 2 /* update in a third server call, after 'third_zone' returns */
        )
    )
) ?>

Notes

It should be noted that using the 'wait' parameter will cause all top level ajax remote function calls to be executed in one call to the server, and each second level to be executed in a second call, etc. Basically, multiple calls to the server are generated by the user action.

This will cause huge performance issues if you attempt to use these functions in a high-traffic scalable-sensitive system. It is mainly intended for a link which executes two or three functions at most. To do more, you should consider using another approach, like the JSON approach documented in the wiki.

The problem

I have:

<?php
 
echo link_to_remote("update news",
                     array(
                           'update'  => 'news_zone',
                           'url'     => 'news/index',
                          )
                   )
?>

The div with the id 'news_zone' will be updated if i click on the link 'update news'. But how can i update another div ?

My solution

You have to add the code in the file symfony/helper/JavascriptHelper.php:

function remote_function($options)
  {
    sfContext::getInstance()->getResponse()->addJavascript('/sf/js/prototype/prototype');
 
    // Multi ajax call
    if (isset($options[0]) && is_array($options[0])) {
        $multi_function = "";
        // First pass (wait)
        for ($i = 0; isset($options[$i]); $i++) {
                if (isset($options[$i]['wait']) && $options[$i]['wait'] != $i && isset($options[$options[$i]['wait']])) {
                        if (isset($options[$options[$i]['wait']]['complete'])) {
                                $options[$options[$i]['wait']]['complete'] .= '; ' . remote_function($options[$i]);
                        } else {
                                $options[$options[$i]['wait']]['complete'] = remote_function($options[$i]);
                        }
                }
        }
 
        // Second pass
        for ($i = 0; isset($options[$i]); $i++) {
                if (!(isset($options[$i]['wait']) && $options[$i]['wait'] != $i && isset($options[$options[$i]['wait']]))) {
                        $multi_function .=  remote_function($options[$i]) . ';';
                }
        }
        return $multi_function;
    }
 
    $javascript_options = _options_for_ajax($options);
...

Now you can use a new syntax (This patch won't affect the "old" syntax. You can use it):

<?php
 
echo link_to_remote('multi ajax', array(
                                         array('update' => 'news_zone',
                                               'url' => '/news/index'
                                              ),
                                         array('update' => 'second_zone',
                                               'url' => '/module/action'
                                               )
                                       )
                   );
 
?>

I provide a special feature: "ajax in order". Requests are asynchronous. Now, you can set an order:

<?php
 
echo link_to_remote('multi ajax', array(
                                         array('update' => 'news_zone',
                                               'url' => '/news/index'
                                              ),
                                         array('update' => 'second_zone',
                                               'url' => '/module/action',
                                               'wait' => 0 /* index table. So update after 'news_zone' */
                                               )
                                       )
                   );
 
?>

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.