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

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
}