Symfony app - how to add calculated fields to Propel objects?

Question

What is the best way of working with calculated fields of Propel objects?

Say I have an object "Customer" that has a corresponding table "customers" and each column corresponds to an attribute of my object. What I would like to do is: add a calculated attribute "Number of completed orders" to my object when using it on View A but not on Views B and C.

The calculated attribute is a COUNT() of "Order" objects linked to my "Customer" object via ID.

What I can do now is to first select all Customer objects, then iteratively count Orders for all of them, but I'd think doing it in a single query would improve performance. But I cannot properly "hydrate" my Propel object since it does not contain the definition of the calculated field(s).

How would you approach it?

Answer 1

There are several choices. First, is to create a view in your DB that will do the counts for you, similar to my answer here. I do this for a current Symfony project I work on where the read-only attributes for a given table are actually much, much wider than the table itself. This is my recommendation since grouping columns (max(), count(), etc) are read-only anyway.

The other options are to actually build this functionality into your model. You absolutely CAN do this hydration yourself, but it's a bit complicated. Here's the rough steps

1. Add the columns to your Table class as protected data members.
2. Write the appropriate getters and setters for these columns
3. Override the hydrate method and within, populate your new columns with the data from other queries. Make sure to call parent::hydrate() as the first line

However, this isn't much better than what you're talking about already. You'll still need N + 1 queries to retrieve a single record set. However, you can get creative in step #3 so that N is the number of calculated columns, not the number of rows returned.

Another option is to create a custom selection method on your TablePeer class.

1. Do steps 1 and 2 from above.
2. Write custom SQL that you will query manually via the Propel::getConnection() process.
3. Create the dataset manually by iterating over the result set, and handle custom hydration at this point as to not break hydration when use by the doSelect processes.

Here's an example of this approach

<?php

class TablePeer extends BaseTablePeer
{
    public static function selectWithCalculatedColumns()
    {
        //  Do our custom selection, still using propel's column data constants
        $sql = "
            SELECT " . implode( ', ', self::getFieldNames( BasePeer::TYPE_COLNAME ) ) . "
                 , count(" . JoinedTablePeer::ID . ") AS calc_col
              FROM " . self::TABLE_NAME . "
              LEFT JOIN " . JoinedTablePeer::TABLE_NAME . "
                ON " . JoinedTablePeer::ID . " = " . self::FKEY_COLUMN
        ;

        //  Get the result set
        $conn   = Propel::getConnection();
        $stmt   = $conn->prepareStatement( $sql );
        $rs = $stmt->executeQuery( array(), ResultSet::FETCHMODE_NUM );

        //  Create an empty rowset
        $rowset = array();

        //  Iterate over the result set
        while ( $rs->next() )
        {
            //  Create each row individually
            $row = new Table();
            $startcol = $row->hydrate( $rs );

            //  Use our custom setter to populate the new column
            $row->setCalcCol( $row->get( $startcol ) );
            $rowset[] = $row;
        }
        return $rowset;
    }
}

There may be other solutions to your problem, but they are beyond my knowledge. Best of luck!