how to do multiple selects and arithmetic using Active Record

how to do multiple selects and arithmetic using Active Record - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: how to do multiple selects and arithmetic using Active Record (/showthread.php?tid=6269)

how to do multiple selects and arithmetic using Active Record - El Forum - 02-20-2008

[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???

how to do multiple selects and arithmetic using Active Record - El Forum - 02-20-2008

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

how to do multiple selects and arithmetic using Active Record - El Forum - 02-21-2008

[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

how to do multiple selects and arithmetic using Active Record - El Forum - 02-21-2008

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