Calculated WHERE based on database field before realising query

[eluser]Andy UK[/eluser]
I'm running a query which selects properties on max and min price. The current form SELECT options where the customer submits his or her search are in just one currency, so I'm editing these to change depending on the active view currency (held in an environment variable) and show nice round number options for each currency, whether it be USD $100,000 or EUR €100,000.

I have a method in the model that returns an array with exchange rates for all active currencies when i plug in the active view currency. For example, here is the array with Colombia Pesos as the active currency:

[1] => Array
(
[symbol] => $
[name] => Peso Colombiano
[curr_code] => COP
[exchange_rate] => 1.000000
[timestamp] => 1403809203
)

[2] => Array
(
[symbol] => $
[name] => Dolar
[curr_code] => USD
[exchange_rate] => 0.000531
[timestamp] => 1403809203
)

[3] => Array
(
[symbol] => £
[name] => Libra Esterlina
[curr_code] => GBP
[exchange_rate] => 0.000312
[timestamp] => 1403809203
)

[4] => Array
(
[symbol] => €
[name] => Euro
[curr_code] => EUR
[exchange_rate] => 0.000390
[timestamp] => 1403809203
)

Currently my active query is something like this:

Code:

$this->db->where($price . ' >=', $this->session->userdata('price_min'));
$this->db->where($price . ' <=', $this->session->userdata('price_max'));

The relevant select is:

Code:

property.currency_id,
property.{$price} as price,

with $price being either 'sale_price' or 'rental_price'

So far so good. Now what i would like to do it convert the value of the min and max search parameters to the currency of the property in a query that may draw hundreds of different properties, not all of which will have the same base currency. In quasi-code, i would like to do the following, although i'm not even sure this is possible:

Code:

$this->db->where($price . ' >=', $this->session->userdata('price_min') * $curr_array[property.currency_id]['exchange_rate']);
$this->db->where($price . ' <=', $this->session->userdata('price_max') * $curr_array[property.currency_id]['exchange_rate']);

As you can see, I want to include the currency ID of the property so as to get the correct exchange rate from the array. Can someone tell me if this is possible and if so, show me how to do this? Thanks!

[eluser]Andy UK[/eluser]
Any ideas? Anyone?

[eluser]jonez[/eluser]
I don't know how with Active Record but you can do a manual query like this:

Code:

$params = array(
    (float)$this->session->userdata('price_min'),
    (float)$this->session->userdata('price_max'),
);

$sql = "
SELECT
    p.*
FROM
    property p
WHERE
    p." . $price . " >= ( ? * p.exchange_rate ) AND
    p." . $price . " <= ( ? * p.exchange_rate )
";

$results = $this->db->query( $sql, $params )->result_array( );

I'm assuming $price is internal, if it's not validate it. If it doesn't work post your empty schema and I'll check the query I haven't tested that.

[eluser]Andy UK[/eluser]
Hi jonez, thanks for your reply.

The exchange rate is stored in an array and not directly retrieved from the property table. What the property table gives us is the base currency for that property. The currency array is retrieved beforehand and is calculated based on the current view currency.

If i were to do an SQL query and not use active records, it would be something like this:

Code:

$sql = "
SELECT
    p.*
FROM
    property p
WHERE
    p." . $price . " >= ( $price * $curr_array[p.currency_id][exchange_rate] ) AND
    p." . $price . " <= ( $price * $curr_array[p.currency_id][exchange_rate] )
";

I have to include the property.currency_id field as a parameter of multidimensional array to get the correct exchange rate for that property. This is the part i'm not sure about... Can i just plug it in like that or are there special characters like when you have to use a php variable within double quotes, eg. "code {$variable} more code".

[eluser]Andy UK[/eluser]
Can anyone help me with this? I need to use the field 'currency_id' as reference for the $curr_array array. The currency_id for the property will be a value of either 1, 2, 3 or 4... One of the four currencies the website currently uses. Eg

Code:

$price >= selected_price_min * $curr_array[p.currency_id]['exchange_rate]

Which is equivalent to something like

Code:

$price >= selected_price_min * $curr_array[1]['exchange_rate]

which in turn is like saying

Code:

$price >= selected_price_min * 0.000531

The real question is... How can i use a table field as a parameter in an array within a $this->db->where before executing the query??

$this->db->where($price_in_local_currency >= $associative_array[table.field]['exchange_rate']

I'm still stuck on this, so any help would be appreciated.

[eluser]Andy UK[/eluser]
I was going about this the wrong way. Instead of having a separate currency array, I've added a join to include the exchange rate in the results. Here is the updated where clause:

Code:

$this->db->where("rate * {$price} >=", $this->session->userdata('price_min'));
$this->db->where("rate * {$price} <=", $this->session->userdata('price_max'));

All solved!

[eluser]jonez[/eluser]
Glad you solved it. You could do it with a match table as you mentioned before, but it's not as efficient since you're doing a query to build the match table then another query to find properties. Using a JOIN is a better solution.

Here's a snippet I use sometimes, from MY_array_helper.php:

Code:

function delegate_array( $arr, $key ) {
$results = array( );

foreach ( $arr as $data ) {
  $results[ $data[ $key ] ] = $data;
}

return $results;
}

What this does is make a given key the index for the array. You use it like this:

Code:

// data retrieved by result_array, 0-n indices

$data = array(
    array( 'id' => 123, 'name' => 'Joe' ),
    array( 'id' => 456, 'name' => 'Bob' ),
);

$data = delegate_array( $data, 'id' );

var_dump( $data ) produces:

Code:

// id key as indices

array(
  '123' => array( 'id' => 123, 'name' ='Joe' ),
  '456' => array( 'id' => 456, 'name' => 'Bob' ),
)

Which lets you access items by their ID, using $data[ $id ].