Symfony - search table by month, year for datetime fields

Question

I am trying to create an search filter on a frontend module, that will filter by 2 fields, which are months and years

I have items in my db table, that have a datetime field.

I'd like to be able to create a search, where If I select January and 2010 from the 2 drop downs, then all items that have datetimes like:

2010-01-24 10:50:52

2010-01-25 10:50:52

Will be listed

I am using symfony 1.4 and Propel ORM

Thank you

Answer 1

Why dont you try to create 2 date and check if that date is between them, for example (2010-01-01 >= $date && 2010-01-31 <= $date ). If so, then your in range and all coincidences will appear.

If you absolutely have to check for month and year i recommend using functions like YEAR(date) = $yourDate and Month(date) = $yourMonth, this functions should go along with a CUSTOM criteria and could look like:

$criterias->addCriteria(TablePeer::DATE_ATTRIBUTE,"YEAR(". TablePeer::DATE_ATTRIBUTE .") = $year",Criteria::CUSTOM);
$criterias->addCriteria(TablePeer::DATE_ATTRIBUTE,"MONTH(". TablePeer::DATE_ATTRIBUTE .") = $month",Criteria::CUSTOM);

Here is a link to MysqlDateFunctions

Answer 2

I've got something very similar in an app. I'm using the sfFormExtraPlugin for fancy date widgets.

My model is "Complaint" and my action is "Explore".

lib/form/ExploreForm.php:

class ExploreForm extends BaseForm
{
  public function configure()
  {

    $this->setWidgets
      (array(
             'explore_range' => new sfWidgetFormDateRange
             (array(
                    'from_date'   => new sfWidgetFormJQueryDate(),
                    'to_date'   =>  new sfWidgetFormJQueryDate(),
                    'label'   =>  'Date of Service ranging ',
                    )
              )
             )
       );

    $this->setValidators(array(
                               'explore_range'       => new sfValidatorDateRange
                               (array(
                                      'required' => true,
                                      'from_date' => new sfValidatorDate(array('required' => false)),
                                      'to_date' => new sfValidatorDate(array('required' => false))
                                      )),
                               'from'   =>  new sfValidatorPass(),
                               'to'   =>  new sfValidatorPass()
                               )
                         );

  }
}

apps/frontend/modules/complaint/templates/exploreSuccess.php:

<form action="<?php echo url_for('complaint/explore') ?>" method="GET">
  <input type="submit" value="Change date range" style="float:right" />
  <ul>
<?php echo $form->renderUsing('list')  ?>
  </ul>
</form>

In apps/frontend/modules/complaint/actions/actions.class.php: public function executeExplore($request) { // default: the first day of this month - 1 year

  $this->form = new ExploreForm(array(
                'explore_range'   =>  array (
                             'from'   =>  $a_year_ago,
                             'to'   =>  $last_of_last_month
                             )
                  ));

  if ($request->hasParameter('explore_range') ) {
$this->form->bind( array('explore_range' => $request->getParameter('explore_range')) );
$this->logMessage("bound", "debug");
if ($this->form->isValid()) {
  $this->form_values = $this->form->getValues(); # cleaned
  $this->logMessage("validation WIN", "debug");
}
else {
  $this->logMessage("validation FAIL", "debug");
  $this->form_values = $this->form->getDefaults();
}

  }
  else {
$this->logMessage("no explore_range param", "debug");
$this->form_values = $this->form->getDefaults();
  }

  $this->from = $this->form_values['explore_range']['from'];
  $this->to = $this->form_values['explore_range']['to'];


  /* complaints per month */
  $this->complaints_by_month = ComplaintTable::getMonthCounts($this->from, $this->to);


  // ...

}

And the actual query is in the model, lib/model/doctrine/ComplaintTable.class.php:

public static function getMonthCounts($from, $to) {

  $connection = Doctrine_Manager::connection();
  $query = <<<ENDSQL
    SELECT year(`date`) as y, month(`date`) as m, count(*) as c
    FROM `complaints`.`complaint`
    WHERE `date` BETWEEN ? AND ?
    GROUP BY year(`date`), month(`date`)
ENDSQL;

  $statement = $connection->execute($query, array($from, $to));

  $result = array();
  while ($row = $statement->fetch()) {
    $result[ sprintf("%04d-%02d",$row[0], $row[1]) ] = $row[2];
  }

  return self::addZeroRows($result, $from, $to);
}

public static function addZeroRows($set, $from, $to) {
  /* insert zero counts for months with no count */
  $from_fields = date_parse($from);
  $to_fields = date_parse($to);
  $start_y = $from_fields['year'];
  $end_y = $to_fields['year'];
  $start_m = $from_fields['month'];
  $end_m = $to_fields['month'];

  $i = 0;
  for ( $y = $start_y;  $y <= $end_y;  $y++ ) {
    for (   $m = ($y == $start_y ? $start_m : 1) ;
            ($y == $end_y && $m <= $end_m) || ($y < $end_y && $m <= 12);
            $m++
            ) {
      $y_m = sprintf("%04d-%02d",$y,$m);
      if ( !isset( $set[$y_m] ) ) {
        $set[$y_m] = 0;
      }
      if ($i++ > 100) {  // don't infinitely loop... you did it wrong
        return $set;
      }
    }
  }


  ksort($set);
  return $set;
}

Now, I'm using Doctrine so you'll have to do a bit of translation into Propelese in the model part, and you may not be doing the "statistics broken down by month" thing I'm doing here, but it should help you get going. Good luck!