Welcome Guest, Not a member yet? Register   Sign In
how to do multiple selects and arithmetic using Active Record
#1

[eluser]ginger_tosser[/eluser]
hi,

not sure if this is possible in AR but I'm trying to convert the following SQL into AR

SELECT value_SI, description from `values` where value_SI < (SELECT 1/(SELECT convert2SI FROM `units` WHERE unit='hands')) ORDER BY value_SI DESC LIMIT 4;

I'm using the arithmetic functions within MySQL so maybe trying to use the AR abstraction is just wrong because I suppose not all DB's support maths.

Anyone???
#2

[eluser]Seppo[/eluser]
Code:
$this->db->select('value_SI, description');
$this->db->from('values');
$this->db->where("value_SI < (SELECT 1/(SELECT convert2SI FROM `units` WHERE unit='hands'))");
$this->db->order_by("value_SI", "DESC");
$this->db->limit(4);

That generates, in mysql driver,
Quote:SELECT `value_SI`, `description` FROM (`values`) WHERE `value_SI` < (SELECT 1/(SELECT convert2SI FROM `units` WHERE unit='hands')) ORDER BY `value_SI` DESC LIMIT 4

it is not as good as AR could be, because it probably won't work on all db systems, but it's the best you can do...
#3

[eluser]ginger_tosser[/eluser]
Seppo,

I reckon I was just being lazy and trying to do too much in one go. I've now broken the problem up into 2 separate requests with the maths in the middle.
It works nicely and is all AR compatible and lets PHP do the arithmetic.

Thanks
#4

[eluser]m4rw3r[/eluser]
If you want to circumvent the escaping that the where() method does and assign the value directly:
Code:
$this->db->ar_from[] = "(SELECT id FROM products ... ) as prod";
Then you can make subselects and similar operations.
I don't know if it is recommended to do like this, but I think it improves performance of the query (otherwise you have to do some of the work in PHP).




Theme © iAndrew 2016 - Forum software by © MyBB