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).
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 at http://groups.google.com/group/rubyonrails-talk?hl=en.
