Hey all,
So I have a custom query that I embedded into a class method
definition:
def self.get_sum_for_range(unit_id,report1,report2)
find_by_sql(["SELECT SUM(distance * 0.000621371192) as sum FROM
reports WHERE unit_id=? AND id >= ? AND id <= ?", unit_id, report1,
report2])
end
value = Report.get_sum_for_range(unit.id,report1,report2)
The problem is value will be a report object that contains an
attribute called sum with the value, but all I want is just the value
itself returned into value.
thanks for response
value = Report.get_sum_for_range(unit.id,report1,report2).sum
def self.get_sum_for_range(unit_id,report1,report2)
find_by_sql(["SELECT SUM(distance * 0.000621371192) as sum FROM
reports WHERE unit_id=? AND id >= ? AND id <= ?", unit_id, report1,
report2]).sum
end
Note the .sum on the end of find_by_sql
Colin
Ah, yeah. Do it in the method.
Is there not a more railsy way of doing what the op wants rather than
using find_by_sql? At least it would be more efficient I think to do
the * 0.006... after the find.
find_by_sql(["SELECT SUM(distance) as sum FROM
reports WHERE unit_id=? AND id >= ? AND id <= ?", unit_id, report1,
report2]).sum * 0.000621371192
Colin
def self.get_sum_for_range(unit_id,report1,report2)
find_by_sql(["SELECT SUM(distance * 0.000621371192) as sum FROM
reports WHERE unit_id=? AND id >= ? AND id <= ?", unit_id, report1,
report2]).sum
end
Hmmm... yes, I wonder whether the method is doing too much. That's a
conversion to meters from miles, but there's a "unit_id" being passed
(and I wonder what the intention of that is)... so it might be better
to have a couple of more reusable methods:
def self.get_sum_for_range(unit_id,report1,report2)
find_by_sql(["SELECT SUM(distance) as sum FROM reports WHERE
unit_id=? AND id >= ? AND id <= ?", unit_id, report1, report2]).sum
end
def self.get_sum_of miles_in_meters(report1,report2)
Report.get_sum_for_range(unit.id,report1,report2) * 0.000621371192
end
value = Report.get_sum_of miles_in_meters(report1, report2)
...but I think this is straying from the topic 
Ah, yeah. Do it in the method.
Is there not a more railsy way of doing what the op wants rather than
using find_by_sql? At least it would be more efficient I think to do
the * 0.006... after the find.
find_by_sql(["SELECT SUM(distance) as sum FROM
reports WHERE unit_id=? AND id >= ? AND id <= ?", unit_id, report1,
report2]).sum * 0.000621371192
Hmmm... yes, I wonder whether the method is doing too much. That's a
conversion to meters from miles, but there's a "unit_id" being passed
(and I wonder what the intention of that is)... so it might be better
to have a couple of more reusable methods:
def self.get_sum_for_range(unit_id,report1,report2)
find_by_sql(["SELECT SUM(distance) as sum FROM reports WHERE
unit_id=? AND id >= ? AND id <= ?", unit_id, report1, report2]).sum
end
def self.get_sum_of miles_in_meters(report1,report2)
Report.get_sum_for_range(unit.id,report1,report2) * 0.000621371192
end
value = Report.get_sum_of miles_in_meters(report1, report2)
I was thinking more along the lines of removing find_by_sql, something like
Report.where("unit_id=? AND id >= ? AND id <= ?", unit_id, report1,
report2).sum('distance')
...but I think this is straying from the topic
True, but may still be helping the OP.
Colin
+1
I tend to assume that there's some reason they've written the sql that
way (like it's to a view, legacy structure that doesn't map to a
model, or there's something more to the query and they've just posted
a minimum code sample) - and that's probably wrong of me. It the query
is *exactly* as posted, then yes, FTLOG change it to a chain of AR
clauses/scopes (is that the term for it?!)
Not related directly to your question but I feel I should point out
that there is almost certainly something wrong with your database
design if you are using id values to select a range of records. It is
not a good idea to use id values for anything other than being a
unique value for each record. If you need something like a
report_number to be used in the way you are doing here it is generally
better to add an extra column for that. Suppose for example that
while adding reports you missed one out, using the id there would be
no way of going back and inserting one in the range later.
Colin
Using this technique:
Report.where("unit_id=? AND id >= ? AND id <= ?", unit_id, report1,
report2).sum('distance')
was ultimately the best solution for me, because using find_by_sql and
then calling sum on it would behave unexpectedly, like sometimes the
sum would have to be invoked twice: find_by_sql(...).sum.sum
And I replaced id with time to select within time frame