math functions in active record sql queries? (pow, sqrt etc)

Question

Active Record is not recognizing common SQL functions like POW and SQRT in the "find" method or "find_by_sql." How do I work around this? There seems to be no literature out there :-(

Answer 1

ActiveRecord supports the most common calculation functionalities where it makes sense. For any other advanced usage, you can always customize the value of the select statement via :select option.

find(:all, :select => "COUNT(*)")

Personally, I haven't been able to find an example where it would make sense to use ActiveRecord (and SQL) for one of the following statements.

SELECT POW(9, 6)
531441
SELECT POW(2, -3)
0.125
SELECT SQRT(65536)
256

IMHO, Ruby Math library it's probably a better choice.

Do you have a real world usage example of POW or SQRT query you can't create with ActiveRecord?

Answer 2

I don't think ActiveRecord has direct support for scalar functions at all. Group functions, sure, since way back, in the main because it makes a lot of sense to ensure the query submitted to the database is concise and results in the smallest possible recordset.

Scalars, on the other hand, don't offer much benefit and there's currently no AR syntax that I know of that can supply it. There may be an actual benefit to performing the calculation within your model, on demand, as it would reduce load on the database server (albeit only by a tiny amount most likely).

It's also tricky to establish a common set across the set of platforms: if your DBMS doesn't implement POW(), say (which I believe in itself is not the ANSI-preferred function name) what would you expect AR to do?

I think if you really want to have your DBMS do your scalar functions for you, you're going to have to supply them as part of a SQL string in, as you mention, a find_by_sql, specified select list or something similar. But I'd go for putting the functions in your models.