Rails - Mar 2012 - getting find_by_sql to return the numeric value rather than an object that contains the value as an attribute

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

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On 6 March 2012 15:08, John Merlino
<stoicism1-YDxpq3io04c@public.gmane.org> wrote:
>
> 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.
    value  = Report.get_sum_for_range(unit.id,report1,report2).sum

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On 6 March 2012 15:08, John Merlino
<stoicism1-YDxpq3io04c@public.gmane.org> wrote:
> 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.
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

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On 6 March 2012 15:28, Colin Law
<clanlaw-gM/Ye1E23mwN+BqQ9rBEUg@public.gmane.org>
wrote:
> Note the .sum on the end of find_by_sql
Ah, yeah. Do it in the method.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On 6 March 2012 15:38, Michael Pavling
<pavling-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> On 6 March 2012 15:28, Colin Law
<clanlaw-gM/Ye1E23mwN+BqQ9rBEUg@public.gmane.org> wrote:
>> Note the .sum on the end of find_by_sql
>
> 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

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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

On Mar 6, 2012, at 8:08 AM, John Merlino wrote:
> 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
> 
> -- 
> You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
> To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
> To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
> For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
> 
> 

-- 
Scott Ribe
scott_ribe-ZCQMRMivIIdUL8GK/JU1Wg@public.gmane.org
http://www.elevated-dev.com/
(303) 722-0567 voice




-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On 6 March 2012 15:46, Colin Law
<clanlaw-gM/Ye1E23mwN+BqQ9rBEUg@public.gmane.org>
wrote:
>> 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)

...but I think this is straying from the topic :-)

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On 6 March 2012 16:12, Michael Pavling
<pavling-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> On 6 March 2012 15:46, Colin Law
<clanlaw-gM/Ye1E23mwN+BqQ9rBEUg@public.gmane.org> wrote:
>>> 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


>
> --
> You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
> To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
> To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
> For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
>


-- 
gplus.to/clanlaw

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On 6 March 2012 16:23, Colin Law
<clanlaw-gM/Ye1E23mwN+BqQ9rBEUg@public.gmane.org>
wrote:
>> 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
>
> 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'')
+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?!)
:-)

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On 6 March 2012 15:08, John Merlino
<stoicism1-YDxpq3io04c@public.gmane.org> wrote:
> 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])
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

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.