I dug into the source code and think I have a workaround. First, there is a more succinct syntax for aggregate calculations, in this case: Sale.sum(:quantity, :joins => :order, :group => :product_id)
This returns the decimals like I want, instead of the strings I don't. Second, you'll notice this starts on the Sale model, not the Order model. I think that's where AR is getting confused: Sale is query table's primary model and the model with the column to be summed. If you reverse it into: Order.sum(:quantity, :joins => :sales, group => :product_id) You'll get strings again. Same results, except stringified. That's satisfies my dilema. I hope it can help someone else. And thanks Frederick and Philip for your help (and Ryan Bates for the railscast 239 on reading rails source that just happened to use ActiveRecord as the example -- he's always doing that!). On Jan 13, 9:11 am, IAmNan <[email protected]> wrote: > Right about count() being an integer and sum() needing to be smarter. > That makes sense. > > The answers to your questions are, AR generated this SQL: > SELECT SUM(quantity) AS sum_quantity, product_id AS product_id FROM > "organizations" INNER JOIN "orders" ON "orders"."organization_id" = > "organizations"."id" INNER JOIN "line_items" ON > "line_items"."order_id" = "orders"."id" GROUP BY product_id; > > (It's also curious that product_id comes back as a string too.) > > Second, includes() returns strings (for both id and sum) as well. The > sql for that is, predictably: > SELECT SUM(quantity) AS sum_quantity, product_id AS product_id FROM > "organizations" LEFT OUTER JOIN "orders" ON "orders"."organization_id" > = "organizations"."id" LEFT OUTER JOIN "line_items" ON > "line_items"."order_id" = "orders"."id" GROUP BY product_id; > > On Jan 12, 10:46 pm, Philip Hallstrom <[email protected]> wrote: > > > > > > > > > On Jan 12, 2011, at 5:09 PM, IAmNan wrote: > > > > You're not too late and I appreciate your feedback. > > > > But it gives the same stringified results. So, I don't think AR is > > > tries to interpret the types at all. It always thought it did in order > > > to provide a consistent interface. The fact that count works as > > > expected confuses me though. I'd think count and sum would behave > > > identically (except for the result, obviously). > > > Well, count() is just counting rows... it has to be an integer. SUM() > > might be counting other things (decimals say) so maybe AR can't decide. > > > Still, it should be able to do it.. > > > What's the SQL being generated? > > > Any difference if instead of joins() you includes() ? > > > > On Jan 12, 8:42 pm, Philip Hallstrom <[email protected]> wrote: > > >> 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 > > >>> athttp://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 > > > athttp://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.
