Welcome Guest, Not a member yet? Register   Sign In
Active record order_by the result of multiplying two fields
#1

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();
    }
Reply
#2

(This post was last modified: 11-18-2014, 04:41 PM by twpmarketing.)

$this->db->order_by("project_units.price_min * rate", "asc");

From the 3.0 User Guide
"Lets you set an ORDER BY clause.

The first parameter contains the name of the column you would like to order by.
The second parameter lets you set the direction of the result. Options are ASC, DESC AND RANDOM."


Use the field name itself, not the computed value.
$this->db->order_by("project_units.price_min", "asc");
CI 3.1 Kubuntu 19.04 Apache 5.x  Mysql 5.x PHP 5.x PHP 7.x
Remember: Obfuscation is a bad thing.
Clarity is desirable over Brevity every time.
Reply
#3

Hi,

Thanks for your reply. I'm not using version 3.0 yet. Also, the sort value has to be computed otherwise we won't be able to sort projects in multiple currencies. Eg:

From price in first project is 300,000,000 pesos. From price in second project is 400,000 US Dollars. The first project in pesos has lower entry level pricing, but would appear after the project with a base currency of Dollars if we only work on the field without first multiplying it by its exchange rate.

(11-18-2014, 04:38 PM)twpmarketing Wrote: From the 3.0 User Guide
"Lets you set an ORDER BY clause.

The first parameter contains the name of the column you would like to order by.
The second parameter lets you set the direction of the result. Options are ASC, DESC AND RANDOM."


Use the field name itself, not the computed value.
Reply
#4

UPDATE

This does not work but does partially order results

Code:
$this->db->order_by("project_units.price_min * rate", "asc");

This also does not work but does partially order results (removed computed field but same outcome)

Code:
$this->db->order_by("project_units.price_min", "asc");

This does work (removed reference to the table)

Code:
$this->db->order_by("price_min", "asc");

At least now I know where the problem was with the semi-sorted results. I still need to resolve the other problem which is that i must oder the results by a computed value - Price * Exchange rate.

Any ideas? Cheers!
Reply
#5

I believe you should be able to order by the result of a calculation.  To debug it, log $this->db->last_query() to make sure Active Record is giving you what you want.

Or you could select the calculation,
PHP Code:
$this->db->select('(project_units.price_min * rate) as adjusted_price'); 
and order by the name you give it (adjusted_price in this case)
Reply
#6

I like your idea of running the calculation in the select and then ordering on the new named field. It would be a nice clean way to do it. Perhaps you know how I could get this to work with select_min, which when translated into SQL already has the 'as [field_name]' included. This is the value that needs to be multiplied by the rate.

Code:
$this->db->select_min("project_units.price_min")
becomes
Code:
MIN(`project_units`.`price_min`) AS price_min

I it possible to nest active record queries?

Thanks

(11-18-2014, 05:13 PM)bclinton Wrote: I believe you should be able to order by the result of a calculation.  To debug it, log $this->db->last_query() to make sure Active Record is giving you what you want.

Or you could select the calculation,


PHP Code:
$this->db->select('(project_units.price_min * rate) as adjusted_price'); 
and order by the name you give it (adjusted_price in this case)
Reply
#7

Got it working now! Here is the select query that returns the converted price:

Code:
select('(SELECT MIN(`project_units`.`price_min`)) * rate as converted_price')

(11-18-2014, 05:22 PM)andycansdale Wrote: I like your idea of running the calculation in the select and then ordering on the new named field. It would be a nice clean way to do it. Perhaps you know how I could get this to work with select_min, which when translated into SQL already has the 'as [field_name]' included. This is the value that needs to be multiplied by the rate.


Code:
$this->db->select_min("project_units.price_min")
becomes

Code:
MIN(`project_units`.`price_min`) AS price_min

I it possible to nest active record queries?

Thanks


(11-18-2014, 05:13 PM)bclinton Wrote: I believe you should be able to order by the result of a calculation.  To debug it, log $this->db->last_query() to make sure Active Record is giving you what you want.

Or you could select the calculation,



PHP Code:
$this->db->select('(project_units.price_min * rate) as adjusted_price'); 
and order by the name you give it (adjusted_price in this case)
Reply
#8

(This post was last modified: 11-18-2014, 06:37 PM by bclinton.)

(11-18-2014, 05:22 PM)andycansdale Wrote: I like your idea of running the calculation in the select and then ordering on the new named field. It would be a nice clean way to do it. Perhaps you know how I could get this to work with select_min, which when translated into SQL already has the 'as [field_name]' included. This is the value that needs to be multiplied by the rate.



Code:
$this->db->select_min("project_units.price_min")
becomes


Code:
MIN(`project_units`.`price_min`) AS price_min

can't you just use price_min? 

Also, you don't necessarily have to use select_min, you could use

PHP Code:
$this->db->select("MIN(`project_units`.`price_min`) AS whatever_you_want_to_call_it"
Reply
#9

Thanks Bill. There seems to be a delay in my replies getting posted. I can't use just price min because the prices are in different currencies. 200,000 USD is more expensive than 50,000,000 Pesos. It's working now though... See me previous post above. Thanks for pointing me in the right direction though! Give my regards to Hillary Wink

(11-18-2014, 06:33 PM)bclinton Wrote:
(11-18-2014, 05:22 PM)andycansdale Wrote: I like your idea of running the calculation in the select and then ordering on the new named field. It would be a nice clean way to do it. Perhaps you know how I could get this to work with select_min, which when translated into SQL already has the 'as [field_name]' included. This is the value that needs to be multiplied by the rate.




Code:
$this->db->select_min("project_units.price_min")
becomes



Code:
MIN(`project_units`.`price_min`) AS price_min

can't you just use price_min? 

Also, you don't necessary have to use select_min, you could use


PHP Code:
$this->db->select("MIN(`project_units`.`price_min`) AS whatever_you_want_to_call_it"
Reply




Theme © iAndrew 2016 - Forum software by © MyBB