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.

Sometimes you may want to give the user the possibility to select a certain range of numbers in a numeric field in the admin generator. An example would be a rating between 1 and 5. This is where this snippet comes in handy.

Add the helper code below to one of your helper groups, that preferably is included in your standard_helpers. Then you can use it like in your generator.yml like follows:

generator: 
  ...
  param:
    ...
    edit:
      display: [..., percentage, ...]
      fields:
        ...
        percentage: { type: select_range_tag, params: min=0 max=100 step=5 }

Supplying a step is optional, it defaults to 1.

Helper code:

function select_range_tag($name, $selected, $options)
{
  $options = _parse_attributes($options);
 
  $select_options = range(_get_option($options, 'min', 1), _get_option($options, 'max', 5), _get_option($options, 'step', 1));
 
  return select_tag($name, options_for_select(array_combine($select_options, $select_options), $selected));
}
 
function object_select_range_tag($object, $method, $options = array(), $default_value = null)
{
  $options = _parse_attributes($options);
 
  $value = _get_object_value($object, $method, $default_value);
 
  return select_range_tag(_convert_method_to_name($method, $options), $value, $options);
}