Ask your Symfony questions! Pay money and get answers fast! (more info)

How to use SQL functions like SUM and YEAR using Propel? Symfony

  • SOLVED

Using the old Symfony 1.0 syntax, could someone suggest how to use functions like YEAR, while still using Propel, or at least Creole?

SELECT YEAR(start_date) as YEAR from contracts ORDER BY start_date DESC

Answers (1)

2010-04-29

Arturo Linares answers:

like this:

c->clearSelectColumns();
$c->addSelectColumn('SUM(' . TablePeer::COLUMN . ')');
$c->addGroupByColumn(TablePeer::COLUMN);


marshall comments:

Thanks. And what if I also need TablePeer::COLUMN to be DISTINCT ?


marshall comments:

I call this with doSelectRS() yes?


Arturo Linares comments:

Like this :)
$c->setDistinct(true); // or $c->addSelectModifier(Criteria::DISTINCT);


Arturo Linares comments:

Yes, yo call it with doSelectRS()


marshall comments:

Thank you. One last follow-up! How do I set an alias, like "SUM(contract_value) AS total".


marshall comments:

I tried


$c->addSelectColumn('SUM(' . TablePeer::COLUMN . ') AS total');

which got me the correct SQL, yet when I try to fetch it:

$results = CaoIssuePeer::doSelectRS($c);

print_r($results);

while ($results->next()) {
echo $results->getString('total');
}

I get:

[SQLException]
Invalid resultset column: total


Arturo Linares comments:

You will have to retrieve it by the column number, like:

echo $results->getString(1);


marshall comments:

Perfect, thanks.