propel pseudo column sorting

Question

Basically I want to make a pseudo column by which I'll sort. This is my SQL Query

SELECT I.*, ((I.width*175)/I.height) as relativeWidth
FROM Image I
order by relativeWidth asc

How can I do it in propel without writing direct SQL ? and I don't want to make a view and query it.

Answer 1

Are you using Criteria (the old way of creating a where clause)? If so you can simply do:

$c = new Criteria();
$c->addSelectColumn(
    '((' . IPeer::WIDTH . '*175)/' . IPeer::HEIGHT . ') AS relativeWidth'
);
$c->addAscendingOrderByColumn('relativeWidth');
$rows = IPeer::doSelect($c);

You will also need to override the hydrate() method in your row class (I) in order to capture the extra column (untested):

public function hydrate($row, $startcol = 0, $rehydrate = false)
{
    $startcol = parent::hydrate($row, $startcol, false);
    $this->relativeWidth = ($row[$startcol] !== null) ? (float) $row[$startcol] : null;
    $this->resetModified();

    $this->setNew(false);

    if ($rehydrate) {
        $this->ensureConsistency();
    }

    return $startcol + 1;
}

Lastly of course you will need a getter for the new value, but that's easy.

If you are using the Query system there is probably a similar way to do it with that, though I am less familiar with it.

(Edit: added return value for correctness.)

Answer 2

Such requirements are possible in version 1.5+ of Propel. You are strongly encouraged to upgrade since it is fully backwards-compatible, with a lot of new features and fixes.

You just need to learn about the new ActiveQuery api instead of the ol' good Criteria. That way you can solve your problem using "virtual columns", just look here: http://www.propelorm.org/reference/model-criteria.html#adding_columns

If you are using Propel inside symfony, just install the sfPropelORMPlugin from https://github.com/propelorm/sfPropelORMPlugin and follow the README file to get it working.

Good luck!