This was asked on the forum, so I thought I would place it here too.

Problem

Suppose you have a model that resembles the following:

  range:
    id:                                   
    start_date: {type: date}
    end_date: {type: date}

And you want to know the following: are there any records where either $date1 or $date2 is between start_date and end_date?

Consider that $date2 could be some fixed $offset from $date1, so that the question becomes: are there any records which partially cover the $offset period following $date1?

Using Criterion Objects

Since the BETWEEN construct is unavailable through Propel (at least not as an object or constant), we have to use the knowledge that:

if
  a >= x
and
  a <= y
then
  x <= a <= y

Which gives us:

$c = new Criteria();
 
$date1 = '2007-08-20';
$date2 = '2008-08-20';
 
// test against date1
$date1Criterion = $c->getNewCriterion(RangePeer::START_DATE, $date1, Criteria::LESS_EQUAL);
 
// test against date2
$date2Criterion = $c->getNewCriterion(RangePeer::START_DATE, $date2, Criteria::LESS_EQUAL);
 
// conjunction
$date1Criterion->addAnd($c->getNewCriterion(RangePeer::END_DATE, $date1, Criteria::GREATER_EQUAL));
$date2Criterion->addAnd($c->getNewCriterion(RangePeer::END_DATE, $date2, Criteria::GREATER_EQUAL));
 
// disjunction
$date1Criterion->addOr($date2Criterion);
$c->add($date1Criterion);

Using Criteria::CUSTOM

Or if you absolutely must have your BETWEEN, try this:

$c = new Criteria();
 
$date1 = '2007-08-20';
$date2 = '2008-08-20';
 
$c->add(RangePeer::START_DATE, "'{$date1}' between ".RangePeer::START_DATE." and ".RangePeer::END_DATE, Criteria::CUSTOM);
$c->addOr(RangePeer::START_DATE, "'{$date2}' between ".RangePeer::START_DATE." and ".RangePeer::END_DATE, Criteria::CUSTOM);

Note that the first arguments to Criteria::add() and Criteria::addOr() can be any column, but they must be the same column.

Note also that when providing the argument, {$dateN} appears between single quotes. It must appear quoted (and escaped) in order for this query to return correct (or any) results.

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

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
}

You might've noticed that the default behavior of propel is to use numeric retrieval when grabbing rows from the database, and it is hard to override this action.

Sure you can do a custom query, but if you want to use criteria methods instead, this class will be helpful in grabbing a a result set via associative (thus preserving your associative keys and making coding easier on you).

I'd consider this beta level code. It's worked twice for me, both when I had a complex join that I wanted to use criteria for but failed once on a simpler query.

I haven't been able to decode why exactly the failure occured yet.

<?php
/*
*
*  @author: Kum Sackey
* 
*  Defines extension to propel that tweak its core behavior
* 
*/
class sfPropelExtension
{
    /**
    * @desc Return a result select from a criteria object. Unlike the implementation in BasePeer::doSelect(), this version returns the result set in associative mode
    */
    public static function getRSFromCriteria($criteria)
    {
        if(!($criteria instanceOf Criteria))
            throw new sfException('Passed parameter must be an instance of criteria, instead is of type: '.gettype($c));
 
        $dbMap = Propel::getDatabaseMap($criteria->getDbName());
        $con = Propel::getConnection();            
 
        $params = array();
 
        $sql = BasePeer::createSelectSql($criteria, $params);
        $stmt = $con->prepareStatement($sql);            
        $stmt->setLimit($criteria->getLimit());
        $stmt->setOffset($criteria->getOffset());            
 
        self::populateStmtValues($stmt, $params, $dbMap);                        
 
        $rs = $stmt->executeQuery($sql, ResultSet::FETCHMODE_ASSOC);  
 
        return $rs;        
    }
 
    /**
     * Populates values in a prepared statement.
     *
     * @param PreparedStatement $stmt
     * @param array $params array('column' => ..., 'table' => ..., 'value' => ...)
     * @param DatabaseMap $dbMap
     * @return int The number of params replaced.
     */
    private static function populateStmtValues($stmt, $params, DatabaseMap $dbMap)
    {
        $i = 1;
        foreach($params as $param) {
            $tableName = $param['table'];
            $columnName = $param['column'];
            $value = $param['value'];
 
            if ($value === null) {
                $stmt->setNull($i++);
            } else {
                $cMap = $dbMap->getTable($tableName)->getColumn($columnName);
                $setter = 'set' . CreoleTypes::getAffix($cMap->getCreoleType());
                $stmt->$setter($i++, $value);
            }
        }
    } 
}