Active record order_by the result of multiplying two fields

Hi all,

I have the following active record query and it's working fine all except for the ordering. The query retrieves construction projects from one table and gets the minimum price by doing a select_min on the project units table and thus establishing the 'from' price for each project. Just to make things interesting, different projects have different base currencies, so we retrieve the exchange rate as part of the query.

The problem i'm having is that i can't get it to order correctly by price. The strange thing is that it kinda works, but there are a few results misplaced. It's sorting about 80 percent fine with a few that just don't get sorted correctly whatever i do.

If we want to order correctly, we have to multiply the from price by the exchange rate and order on the result. So basically we are ordering on the calculated result of two fields multiplied together. I don't know if this is a syntax issue here or if it may have something to do with the group_by statement. I suspect it could be the latter because when I remove the exchange rate multiplier and just select projects of the same base currency, it is still sorting badly. Anyway, here's the code:

Code:

function getProjects()
    {
        $suffix = '_' . $this->lang->lang();
        $this->load->library('pagination');
        
        $this->db->select("projects.id,
                            projects.subtitle{$suffix} as subtitle,
                            projects.description{$suffix} as description,
                            projects.project_name,
                            projects.project_slug,
                            projects.units,
                            projects.floors,
                            projects.developer,
                            projects.currency_id,
                            currency_rates.rate as rate");
                    $this->db->select_min("project_units.price_min")
                    ->select_max("project_units.price_max")
                    ->join('project_units', 'projects.id = project_units.project_id && project_units.status_id = 1 && project_units.price_min != 0')
                    ->join('currency_rates', "projects.currency_id = currency_rates.base_currency_id && currency_rates.view_currency_id = {$this->session->userdata('view_currency')}")
                    ->join('currencies', 'projects.currency_id = currencies.id');
                    $this->db->order_by("project_units.price_min * rate", "asc");
                    $this->db->group_by('projects.id');
                    
        $result = $this->db->get('projects');

        return $result->result();
    }