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.

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

I had some trouble finding information on how to use SQL aggregate functions like GROUP BY, COUNT and HAVING with Propel, so here is some info about that.

Suppose you have a system with a many-to-many relation between articles and authors, this example shows how to find out how many articles each author has worked on.

$c = new Criteria();
 
// optionally look only for certain authors whose IDs are in $results
$c->add(AuthorPeer::ID, $results, Criteria::IN);
// JOIN them with the article IDs
$c->addJoin(ArticleAuthorPeer::AUTHOR_ID, AuthorPeer::ID);
// list each author only once and count the number of articles they have worked on
$c->addGroupByColumn(AuthorPeer::ID);
$c->addAsColumn('numArticles', 'COUNT('.AuthorPeer::ID.')');
 
// optionally retrieve only those authors that have a certain number of articles (like 'numArticles=2' or 'numArticles>2')
// the first argument does not really matter since this is a custom criteria
// according to the SQL standard this cannot be done with a WHERE clause
$c->addHaving($c->getNewCriterion(AuthorPeer::ID, 'numArticles=2', Criteria::CUSTOM));
 
// order by the number of articles
$c->addDescendingOrderByColumn('numArticles');
 
// get a ResultSet and iterate over it
$rs = AuthorPeer::doSelectRS($c);
$counts = array();
$results = array();
while ($rs->next())
{
  $author = new Author();
  // hydrate the object and store how many columns it has
  $lastColumn = $author->hydrate($rs);
  $results[] = $author;
  // then retrieve the COUNT from the first column not belonging to the object
  $counts[] = $rs->getInt($lastColumn);
}
$this->results = $results;
$this->counts = $counts;
 

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

I needed a one pager for custom SQL and i read the sfPropelPager and i change like this.

Howto use: write two SQL in Peer class like this.

   public function getReportsWithPager($params)
   {
 
         $WHERE = "FROM ".self::TABLE_NAME." WHERE ".self::SOMEWHEREFIELD." = '%s'";
 
          $SQL ="SELECT SUM(".self::FIELDNAME."), ".self::WHOIS." %s";    
          $cSQL = "SELECT COUNT(id) %s"  
          $WHERE = sprintf($WHERE, $params['where']);
          $SQL = sprintf($SQL, $WHERE);
          $cSQL = sprintf($cSQL, $WHERE);
 
         $pager = new myCustomPager($cSQL, $SQL, $params['maxpage']);
         $pager->setPage($params['page']);
 
         $pager->init();
         return $pager;          
   }

And call in your action:

       $this->pager = SomePeer::getReportsWithPager($params);

in Template,

     <?php 
     $list = $pager->getResults(); 
     while($list->next()):
     ?> 
     <div><?php echo  $list->get('sum'); ?></div>  
     <?php endwhile; ?>

And creation page links same with sfPropelPager, read the sfPropelPager.

<?php
 
/*
 * For the full copyright and license information, please view the LICENSE
 * file that was distributed with this source code.
 *
 */
/**
 * @package    symfony
 * @subpackage addon
 * @author     Fabien Potencier <fabien.potencier@symfony-project.com>
 * @version    SVN: $Id: sfPropelPager.class.php 1415 2006-06-11 08:33:51Z fabien $
 */
 
/**
 *
 * myCustomPager class.
 *
 * @package    symfony
 * @subpackage addon
 * @author     Fabien Potencier <fabien.potencier@symfony-project.com>
 *   
 * Changed: sfPropelPager.class.php 
 * Changed by: Timu EREN
 * Last Changed: 12 / 08 /2006(dd /mm/ YYYY)
 */
class myCustomPager
{
  private
    $page                   = 1,
    $maxPerPage             = 0,
    $lastPage               = 1,
    $nbResults              = 0,
    $query               = null,
    $countQuery     = null,
    $objects                = null,
    $cursor                 = 1,
    $parameters             = array(),
    $currentMaxLink         = 1;
 
 
  public function __construct($cSQL,$SQL, $defaultMaxPerPage = 10)
  {
        $con = Propel::getConnection();
        $SQL = $con->prepareStatement($SQL);
        $cSQL = $con->prepareStatement($cSQL);
        $this->setQuery($SQL);
        $this->setCountQuery($cSQL);
        $this->setMaxPerPage($defaultMaxPerPage);
        $this->setPage(1);
  }
 
  public function init()
  {
        $cForCount =  $this->getCountQuery();
        $c = $cForCount->executeQuery();
        $c->next();
 
        $this->setNbResults($c->get('count'));
 
        $c =  $this->getQuery();
        $c->setLimit(0);
        $c->setOffset(0);
        if (($this->getPage() == 0 || $this->getMaxPerPage() == 0))
        {
            $this->setLastPage(0);
        }
        else
        {
            $this->setLastPage(ceil($this->getNbResults() / $this->getMaxPerPage()));
            $c->setOffset(($this->getPage() - 1) * $this->getMaxPerPage());
            $c->setLimit($this->getMaxPerPage());
        }
  }
 
  public function getCurrentMaxLink()
  {
        return $this->currentMaxLink;
  }
 
  public function getLinks($nb_links = 5)
  {
    $links = array();
    $tmp   = $this->page - floor($nb_links / 2);
    $check = $this->lastPage - $nb_links + 1;
    $limit = ($check > 0) ? $check : 1;
    $begin = ($tmp > 0) ? (($tmp > $limit) ? $limit : $tmp) : 1;
 
    $i = $begin;
    while (($i < $begin + $nb_links) && ($i <= $this->lastPage))
    {
      $links[] = $i++;
    }
 
    $this->currentMaxLink = $links[count($links) - 1];
 
    return $links;
  }
  public function haveToPaginate()
  {
    return (($this->getPage() != 0) && ($this->getNbResults() > $this->getMaxPerPage()));
  }
 
  public function getCursor()
  {
    return $this->cursor;
  }
 
  public function setCursor($pos)
  {
    if ($pos < 1)
    {
      $this->cursor = 1;
    }
    else if ($pos > $this->nbResults)
    {
      $this->cursor = $this->nbResults;
    }
    else
    {
      $this->cursor = $pos;
    }
  }
 
  public function getObjectByCursor($pos)
  {
    $this->setCursor($pos);
 
    return $this->getCurrent();
  }
 
  public function getCurrent()
  {
    return $this->retrieveObject($this->cursor);
  }
 
  public function getNext()
  {
    if (($this->cursor + 1) > $this->nbResults)
    {
      return null;
    }
    else
    {
      return $this->retrieveObject($this->cursor + 1);
    }
  }
 
  public function getPrevious()
  {
    if (($this->cursor - 1) < 1)
    {
      return null;
    }
    else
    {
      return $this->retrieveObject($this->cursor - 1);
    }
  }
 
  private function retrieveObject($offset)
  {
    $cForRetrieve = $this->getQuery();
    $cForRetrieve->setOffset($offset - 1);
    $cForRetrieve->setLimit(1);
    $results = $cForRetrieve->executeQuery();
    return $results;
  }
 
  public function getResults()
  {
    $c = $this->getQuery();
    return $c->executeQuery();
  }
 
  public function getFirstIndice()
  {
    if ($this->page == 0)
    {
      return 1;
    }
    else
    {
      return ($this->page - 1) * $this->maxPerPage + 1;
    }
  }
 
  public function getLastIndice()
  {
    if ($this->page == 0)
    {
      return $this->nbResults;
    }
    else
    {
      if (($this->page * $this->maxPerPage) >= $this->nbResults)
      {
        return $this->nbResults;
      }
      else
      {
        return ($this->page * $this->maxPerPage);
      }
    }
  }
 
  public function getQuery()
  {
    return $this->query;
  }
 
  public function setQuery($c)
  {
    $this->query = $c;
  }
 
  public function getCountQuery()
  {
    return $this->countQuery;
  }
 
  public function setCountQuery($c)
  {
    $this->countQuery = $c;
  }
 
  public function getNbResults()
  {
    return $this->nbResults;
  }
 
  private function setNbResults($nb)
  {
    $this->nbResults = $nb;
  }
 
  public function getFirstPage()
  {
    return 1;
  }
 
  public function getLastPage()
  {
    return $this->lastPage;
  }
 
  private function setLastPage($page)
  {
    $this->lastPage = $page;
    if ($this->getPage() > $page)
    {
      $this->setPage($page);
    }
  }
 
  public function getPage()
  {
    return $this->page;
  }
 
  public function getNextPage()
  {
    return min($this->getPage() + 1, $this->getLastPage());
  }
 
  public function getPreviousPage()
  {
    return max($this->getPage() - 1, $this->getFirstPage());
  }
 
  public function setPage($page)
  {
    $page = intval($page);
 
    $this->page = ($page <= 0) ? 1 : $page;
  }
 
  public function getMaxPerPage()
  {
    return $this->maxPerPage;
  }
 
  public function setMaxPerPage($max)
  {
    if ($max > 0)
    {
      $this->maxPerPage = $max;
      if ($this->page == 0)
      {
        $this->page = 1;
      }
    }
    else if ($max == 0)
    {
      $this->maxPerPage = 0;
      $this->page = 0;
    }
    else
    {
      $this->maxPerPage = 1;
      if ($this->page == 0)
      {
        $this->page = 1;
      }
    }
  }
}
 
?>

Let say you want to write a sub-select, and have a Propel object returned.

As an example, lets generate the following SQL:

SELECT
    orders.ID,
    orders.TYPE,
    orders.STATUS
FROM
    orders
WHERE
    orders.STATUS IN (
        SELECT
              STATUS.NAME
        FROM
              STATUS
        WHERE
             STATUS.ORDER_TYPE = 'purchase'
    )

Propel does not natively handle this sort of SQL. There are however two ways to create this SQL or to get this data set and have Propel objects returned.

Overview of the different methods

1. Use of Criteria::CUSTOM
   • PRO: Allows the use of an actual sub-select
   • CON: Code is not as readable
   • See http://www.symfony-project.com/snippets/snippet/53 for more information on Criteria::CUSTOM
2. Rewrite the SQL using JOINs
   • PRO: Very readable code
   • CON: Not using a sub-select
   • CON: You may be using a sub-select for SQL performance, so this may be slower

Way 1: Use of Criteria::CUSTOM

$c = new Criteria();
$subSelect = "orders.STATUS IN (
    SELECT
          status.NAME
    FROM
          status
    WHERE
         status.ORDER_TYPE = 'purchase'
    )";
$c->add(StatusPeer.STATUS, $subSelect, Criteria::CUSTOM);
$orders = StatusPeer::doSelect($c);

Way 2: Rewrite the SQL using JOINs

Example of the rewritten SQL:

SELECT
    orders.ID,
    orders.TYPE,
    orders.STATUS
FROM
    orders,
    STATUS
WHERE
    orders.STATUS = STATUS.NAME
    AND STATUS.ORDER_TYPE = 'purchase'

This can be written as follows in your action:

$c = new Criteria();
$c->addJoin (OrderPeer::STATUS, StatusPeer::NAME);
$c->add(StatusPeer.ORDER_TYPE, 'purchase');
$orders = StatusPeer::doSelect($c);

complement complex SQL in SYMFONY:

Example 1: SQL to be implemented:

SELECT b.id FROM article_mark a RIGHT JOIN article b ON a.article_id = b.id ORDER BY a.mark DESC,b.CREATED_AT DESC

Symfony implementation:

    $c=new Criteria();
    $c->addAlias('a', 'article_mark');              //!!!if not using alias of table, the generated sql is not correct
    $c->addAlias('b', 'article');
    $c->addSelectColumn('b.id');
    $c->addSelectColumn('a.article_id');            //!!!if one table has no column added, there's no table name after FROM clause;; actually this column is not what I need
    $c->addDescendingOrderByColumn('a.mark');
    $c->addDescendingOrderByColumn('b.CREATED_AT');
    $c->addJoin('a.ARTICLE_ID','b.ID','RIGHT JOIN');
    $art_marks=ArticlePeer::doSelectRS($c);         //!!! doSelect or doSelectOne can't be used

By this way, $art_marks is a MySQL recordset, using $art_marks[0] get value of column b.id;

Example 2: SQL to be implemented:

select SUM(score) from bury group by article_id having article_id=@ARTICLE_ID

Symfony implementation:

        $c=new Criteria();
        $c->addSelectColumn('SUM(score) as A');     //actually 'as A' has no use
        $c->addSelectColumn(BuryPeer::SCORE);              //!! without this, no table name in generated sql; actually this column is not what I need
 
        $c->addGroupByColumn('article_id');
        $crit=$c->getNewCriterion(BuryPeer::ARTICLE_ID,$article->getId());
        $c->addHaving($crit);
        $buries=BuryPeer::doSelectRS($c);       //only MySQL recordset can be used

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
}