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.)