Code snippets for symfony 1.x

Navigation

Query objects between two dates with Criteria

Say you look for the objects of class Foo being created between $from_date and $to_date. This should do the trick:

$c = new Criteria();
$criterion = $c->getNewCriterion(FooPeer::CREATED_AT , date('Y-m-d', $from_date), Criteria::GREATER_EQUAL  );
$criterion->addAnd($c->getNewCriterion(FooPeer::CREATED_AT , date('Y-m-d', $to_date), Criteria::LESS_EQUAL ));
$c->add($criterion);
$shows = FooPeer::doSelect($c);
by Francois Zaninotto on 2006-05-24, tagged criteria  date  propel 

Comments on this snippet

gravatar icon
#1 Olivier Verdier on 2006-05-26 at 03:54

Can't you just write in a much simpler way:

$c = new Criteria();
$c->add(FooPeer::CREATED_AT, date('Y-m-d', $from_date), Criteria::GREATER_EQUAL);
$c->addAnd(FooPeer::CREATED_AT, date('Y-m-d', $to_date, Criteria:: LESS_EQUAL);
$shows = FooPeer::doSelect($c);

or am i missing something?

gravatar icon
#2 Francois Zaninotto on 2006-06-06 at 10:09

->addAnd() only accepts a Criterion object, so your solution doesn't work.

gravatar icon
#3 Romain Dorgueil on 2006-06-09 at 11:25

And i thought dumbly that you could simply put

$c->add(self::DATE, $date, Criteria::GREATER_EQUAL);
$c->add(self::DATE, $end_date, Criteria::LESS_EQUAL);

but now i found the case in my app i understand the point. Propel will override Criteria dor DATE by the second one, creating a request like this:

Current Query SQL (may not be complete or applicable): SELECT  FROM stat_hit_by_affiliate WHERE stat_hit_by_affiliate.DATE<=?
Parameters to replace: array (
  0 =>
  array (
    'table' => 'stat_hit_by_affiliate',
    'column' => 'DATE',
    'value' => '2006-06-10',
  ),

And by seeing Criteria's request storage it becomes logic that it can't have two keys for same table/column.

Now using Criterions we get:

Parameters to replace: array (
  0 =>
  array (
    'table' => 'stat_hit_by_affiliate',
    'column' => 'DATE',
    'value' => '2006-06-03',
  ),
  1 =>
  array (
    'table' => 'stat_hit_by_affiliate',
    'column' => 'DATE',
    'value' => '2006-06-10',
  ),

+1 Francois :p

(btw, couln't it be possible to enlarge a bit the comment edit box??? double would be way better :p)

gravatar icon
#4 Olivier Verdier on 2006-06-10 at 11:53

You are wrong, François: addAnd accepts either a Criterion object or creates one on the fly with the syntax i used.

gravatar icon
#5 Klemen Slavič on 2006-06-19 at 03:45

I concur - andAnd creates a fresh Criterion object and combines it with the AND clause.

gravatar icon
#6 Evgeniy Fedorov on 2007-07-29 at 10:07

Maybe it is just me, but I have to use complete datetime format, i.e. date('Y-m-d H:i:s'). Otherwise, the query returns no match.