One other thing... the SQL generated by AR returns the correct column types if pasted in the pgsql shell or in pgAdmin.
On Jan 12, 9:09 pm, IAmNan <[email protected]> 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). > > 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.
