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