Code snippets for symfony 1.x

Navigation

Refine Tags

Snippets tagged "sql"

nonHydratingPager class (a pager for when you don't want to hydrate your Doctrine object)

This is just a version of the nonHydratingPager for Propel, adapted for Doctrine.

<?php
/* Designed to be compatible with sfDoctrinePager only accept raw sql queries instead
 * of a Doctrine_Query object and spit back a resultset which you most likely want to
 * fill an array with instead of the standard array of hydrated objects.
 *
 * This solution is based on Propel's by Noel Tarnoff, Oz Basarir, dev AT (NOSPAM) naturalcapitalDOTorg
 *
 * @author  David Morales, davidmoralesmojica AT (NOSPAM) gmailDOTcom
 *
 * Typical usage scenario:
 * 1) build 2 query strings with common WHERE clause, one for count one for selecting the rows
 * 2) pass the queries in with page and maxPerPage into the constructor ( no need to ->init() )
 * 3) iterate through your result set (array of values)
 * 4) sit back and watch the fun
 *
 * ex.
 *  $results = new nonHydratingPager($query_select, $query_count, $page, $max);
 *
 *  foreach( $results as $result )
 *  {
 *    ...
 *  }
 */
 
 
class nonHydratingPager extends sfPager
{
  private $resultSet = null;
  private $query = null;
  private $query_count = null;
 
  public function __construct($query, $query_count, $page = 1, $maxPerPage = 25)
  {
    $this->setPage($page);
    $this->setMaxPerPage($maxPerPage);
    $this->query = $query;
    $this->query_count = $query_count;
  }
 
  public function init()
  {
    $rs = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAssoc($this->query_count);
 
    $this->setNbResults($rs[0]['count']);
 
    $this->setLastPage(ceil($this->getNbResults() / $this->getMaxPerPage()));
 
    $startIndex = (($this->getPage()) - 1) * $this->getMaxPerPage();
 
 
    $this->query .= ' LIMIT ' . $this->getMaxPerPage() . ' OFFSET ' . $startIndex;
 
    $this->resultSet = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAssoc($this->query);
  }
 
  public function getResults() {
    return $this->resultSet;
  }
 
  protected function retrieveObject($offset) {}
}
 
by David Morales on 2009-04-02, tagged doctrine  nohydrate  nonhydrating  nonhydratingpager  pager  pagination  rawsql  sql 

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 
(2 comments)

Executing a MySQL stored procedure

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.

by Marcel van Leeuwen on 2008-04-18, tagged model  mysqli  propel  sql 
(1 comment)

subqueries with criteria

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

by noname noname on 2008-01-26, tagged criteria  order  propel  sql  subquery 
(2 comments)

Using SQL aggregate functions

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;
 
by Georg Sorst on 2007-12-18, tagged aggregate  count  criteria  manytomany  propel  sql 
(2 comments)

Retrieve model objects with custom SQL query

$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 );
by Yuriy Smirnov on 2007-08-07, tagged custom  model  object  propel  sql 

Complement complex SQL in SYMFONY

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
by William Duan on 2007-01-07, tagged model  propel  sql 

pager for custom SQL querys

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;
      }
    }
  }
}
 
?>
by Timu EREN on 2006-08-17, tagged pager  pagination  sql 
(4 comments)

Sub-selects using Propel

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
  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);
by Greg Militello on 2006-08-17, tagged criteria  propel  query  sql 
(4 comments)

How to get a propel pager working with a custom SQL query

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
}
by Olivier Verdier on 2006-05-25, tagged custom  pager  pagination  propel  query  raw  sql