SUM() with Propel?

Prev

http://www.symfony-project.org/forum/index.php/m/20554/

> How might I go about using the SUM() function with Propel?

Here is a custom method using direc SQL in a TablePeer class:

public function getMostRecentFiles($iPresentationId) {
    /*
     * For custom/direct SQL, the connection object itself is reqiured.
     * See http://propel.phpdb.org/docs/user_guide/chapters/FindingObjects.
     * html#WritingSQL for more info on the subject
     */
    $oCon = Propel::getConnection();

// Prepare statement
$oQuery = $oCon->createStatement();

// Query
$sQuery = "SELECT  pf.*
           FROM    presentation_file AS pf
                  ,(SELECT  filename
                           ,MAX(timestamp) as uploadtime
                    FROM    presentation_file AS pf2
                    GROUP BY filename
                    ) AS pf3
                   ,mime_type AS mt
           WHERE pf.timestamp = uploadtime
           AND   pf.filename = pf3.filename
           AND   pf.presentation_id = " .$iPresentationId. "
           AND   pf.mime_type_id = mt.id";

// Get the data
$rRes = $oQuery->executeQuery($sQuery, ResultSet::FETCHMODE_NUM);

    // Turn resultset into array of objects ... and return
    return parent::populateObjects($rRes);
}

documented on: 17 January 2007, snowkrash

SUM() with Propel?

It'd be cleaner to use the following, rather than full custom SQL.

<?php
$c = new Criteria();
$c->clearSelectColumns();
$c->addSelectColumn('SUM(' . TablePeer::COLUMN . ')');
$c->addGroupByColumn(TablePeer::COLUMN);
?>

Read the Criteria API for more details.

documented on: 17 January 2007, halfer

SUM() with Propel?

> $this->data = TablePeer::doSelect($c);

doSelect gets you a Propel object - try doSelectRS instead. This should then return you an associative array, which you can initially analyse using print_r($data). If you want to tidy it up use Criteria::addAlias() to give the sum column a nice name.

documented on: 17 January 2007, halfer

SUM() with Propel?

The confusion here probably comes from the fact that the doSelectRS() method on Propel peer classes doesn't return an array, but a ResultSet object (part of the Creole package). I don't think passing that to print_r() will output the actual rows resulting from the query.

The ResultSet object has its own internal pointer, along with 'scrolling' methods, and getters for each datatype: see details here. The typical use will be to iterate over the rows this way:

<?php
while($result_set->next()) {
  // Access the current row's attributes with ResultSet  methods, for example:
  $id = $result_set->getInt('id');
  // Alternatively, if you do want to work with an array,  this will return the current row as an associative array:
  $row = $result_set->getRow();
  $id = $row['id'];
}
?>

This, by the way, is exactly how Propel populates objects. :-)

documented on: 29 January 2007, jfcaouette