On Jan 12, 2011, at 4:23 PM, IAmNan wrote:
> Okay, I understand what you are saying about :quantity not being on
> the Order table. (Interesting, though... I just tried replacing "sum"
> with "count" and guess what: numeric values come back.)
>
> So two possible solutions: use ruby (in the model) to "fix" the hash
> after the query, or use hardcoded SQL that explicitly declares the
> return type instead of letting AR construct the SQL. Sounds about
> right?
I'm coming into this way late, but what happens if you...
....sum('sales.quantity')
Would that give AR enough of a hint to figure out what table/type to cast it to?
Can you post the actual SQL being generated? I didn't see it in the archives...
> On Jan 12, 7:46 pm, Frederick Cheung <[email protected]>
> wrote:
>> On Jan 12, 7:22 pm, IAmNan <[email protected]> wrote:> As always, Fred,
>> thanks for your reply.
>>
>>> The example you give works until you include a join, then you get
>>> strings again.
>>
>>> Order.joins(:sales).group(:product_id).sum(:quantity)
>>
>> Quantity isn't on the model actually being queried so this doesn't
>> surprise me. It does suck though. It looks like the sqlite3 driver is
>> just smarter about asking the db for the types of the columns (I think
>> that with sqlite3 you sort of don't have a choice the way the api is
>> written, whereas with mysql you get all the columns as strings "for
>> free". I could be wrong though. I don't know what the postgres api is
>> like at all).
>>
>> Fred
>>
>>
>>
>>
>>
>>
>>
>>> That returns strings again. I don't think I did anything AR shouldn't
>>> be aware of. BTW, the product_id is returned as a string too. I've
>>> verified that SQLite3 returns numbers for both. This really seems
>>> broken to me.
>>
>>> Order has_many :sales
>>> Sale belongs_to :order
>>> Order has a ordered_at datetime and the seller_id, Sale has the
>>> product_id and quantity. This is why I need the join. (Oh, and Sale is
>>> actually LineItem/line_item, although I doubt that makes a
>>> difference.)
>>
>>> d.
>>
>>> On Jan 11, 1:14 pm, Frederick Cheung <[email protected]>
>>> wrote:
>>
>>>> On Jan 11, 4:47 pm, IAmNan <[email protected]> wrote:
>>
>>>>> I wrote this question on RoRTalk back in August but haven't heard back
>>>>> yet:http://tinyurl.com/4ohxdnf. So I think I must've been unclear.
>>
>>>>> Assume you have a Sale model with just a product Id and a quantity
>>>>> sold. You want to see a total number of sales for each product.
>>
>>>>> Product.group(:product_id).select("product_id, sum(quantity) as
>>>>> total_quantity")
>>
>>>>> Let's collect just the totals to see what they look like in irb:
>>>>> Product.group(:product_id).select("product_id, sum(quantity) as
>>>>> total_quantity").map(&:total_quantity)
>>
>>>>> In SQLite (and MySQL I think) I get the following:
>>>>> => [293.00, 4.00, 76.00, 9.00, 370.25, 71.00]
>>
>>>>> BUT! PostgreSQL returns this:
>>>>> => ["293.00", "4.00", "76.00", "9.00", "370.25", "71.00"]
>>
>>>>> Strings! Why strings!? Am I doing something wrong? Why is this
>>>>> happening, how do I fix it, and why doesn't ActiveRecord protect poor
>>>>> little me from the mean world of db inconsistencies? ;)
>>
>>>> In general AR doesn't know the type of non column expressions.
>>>> If you did something like Product..group(:product_id).sum(:quantity)
>>>> then AR knows you're doing a sum, and it knows that the sum of
>>>> decimals should be decimals so it would cast what it got back from the
>>>> db to the appropriate type
>>
>>>> Fred
>>
>>>>> Thank in advance.
>>>>> PS Quantity is a decimal in the schema.
>
> --
> 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 [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> For more options, visit this group at
> http://groups.google.com/group/rubyonrails-talk?hl=en.
>
--
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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
