symfony 1.4 propel 1.6 : sum

4
votes

I'm trying to get a sum of columns in propel. My code

$c = new Criteria();
$c->add(valuePeer::OWNER_ID, $this->getId());
$c->addSelectColumn('SUM(' . valuePeer::VALUE . ') as total');
$c->addGroupByColumn(valuePeer::VALUE);

$sum = valuePeer::DoSelect($c);

printing out $sum returns nothing (not even an empty object). all i get is Notice: Undefined offset: 1 in /.../lib/model/om/BaseValue.php on line 203 Notice: Undefined offset: 2 in /.../lib/model/om/BaseValue.php on line 204

I tried this approach with ::DoSelectRS($c) as suggested here and multiple other discussions but i get an error : Fatal error: Call to undefined method ValuePeer::DoSelectRS() in /.../lib/model/Restauracia.php on line 39.

Can anyone please tell me what is the right approach on this then?

3
phpsymfony-1.4sumpropel

3 Answers

8
votes

Why don't you use the new ModelCriteria instead of the old verbose one ?

$sum = ValueQuery::create()
  ->select(array('total'))
  ->filterByOwnerId($this->getId())
  ->withColumn('SUM(Value.Value)', 'total')
  ->find();

Will return something like:

PropelArrayCollection(
  array('total' => 25)
)
3
votes

i just found this solution. it works like i needed.

$c = new Criteria();
$c->clearSelectColumns();
$c->add(valuePeer::OWNER_ID, $this->getId());
$c->addSelectColumn('SUM(' . valuePeer::VALUE . ') as total');
//$c->addGroupByColumn(valuePeer::VALUE);

$sum = valuePeer::DoSelectStmt($c)->fetchAll(PDO::FETCH_COLUMN,0);
$sum = $sum[0];
2
votes

Try this (Don't worry about findOne, SUM returns one row anyway).

Note that getTotal() is generated from reference 'total' of withColumn.

The convention is get+Columnname ( camelcase )

$sum = ValueQuery::create()
  ->filterByOwnerId($this->getId())
  ->withColumn('SUM(Value.Value)', 'total')
  ->findOne()
  ->getTotal();