Code snippets for symfony 1.x


Simulating a BETWEEN construct

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


Suppose you have a model that resembles the following:

    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:

  a >= x
  a <= y
  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

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.

by Jesse Dhillon on 2007-08-22, tagged between  criteria  criterion  date  mysql  propel  query  range 

Comments on this snippet

gravatar icon
#1 virtualize on 2008-01-15 at 08:41

thanks, i was desperately searching for that!

gravatar icon
#2 chris mistral on 2011-03-15 at 03:37

Hi, I'm trying to implement you snippet in my symfony 1.2.7 application:

$date1 = '2010-03-01'; $date2 = '2011-03-31';

$c = new Criteria; $c->add(MenuPeer::ID, $this->contextuals->getMenuId());

$c->add(ArticlePeer::START_DATE, "'{$date1}' BETWEEN ".ArticlePeer::START_DATE." AND ".ArticlePeer::END_DATE, Criteria::CUSTOM); $c->addOr(ArticlePeer::START_DATE, "'{$date2}' BETWEEN ".ArticlePeer::START_DATE." AND ".ArticlePeer::END_DATE, Criteria::CUSTOM); $c->addAscendingOrderByColumn(ArticlePeer::START_DATE);

$this->events = ArticlePeer::doSelect($c);

Unfortunately it doesn't works as it returns nothing, even if I've some articles between theses dates. Should I rewrite some criteria to make it works? Thanks for your answer.