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

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


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)


Arturo Linares answers:

like this:

$c->addSelectColumn('SUM(' . 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);


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

I get:

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.