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