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.
