Custom SQL on Symfony
$c = new Criteria();
$c->addAsColumn(’total_views’, ‘SUM(’ . self::VIEWS . ‘)’);
$c->addAsColumn(’yearAndMonth’, ‘SUBSTR(’ . self::TIME . ‘, 1, 7)’);
$c->add(self::OFFER_ID, $offer->getId());
$c->addGroupByColumn(’yearAndMonth’);
$c->addDescendingOrderByColumn(’yearAndMonth’);
$viewsForMonths = array();
$stmt = BasePeer::doSelect($c);
while($result = $stmt->fetch(PDO::FETCH_ASSOC))
{
list($year, $month) = explode(’-', $result[’yearAndMonth’]);
$monthString = date(’F', mktime(0, 0, 0, $month, 1, $year));
$viewsForMonths[”$monthString $year”] = $result[’total_views’];
}
return $viewsForMonths;