Interesting. I don't think AR will ever get the right data types since it has
no idea what "sum_quantity" should translate to. In Rails 3, Postgresql 9.x I
have User and BlogPost. Users have many blog posts. Use also has an
'old_accounts_id' which is an integer and we'll use for summing... I think this
matches your setup...
ruby-1.8.7-p330 :023 > BlogPost.sum(:old_accounts_id, :joins => :user, :group
=> 'blog_posts.id').first
SQL (3.8ms) SELECT SUM(old_accounts_id) AS sum_old_accounts_id,
blog_posts.id AS blog_posts_id FROM "blog_posts" INNER JOIN "users" ON
"users"."id" = "blog_posts"."user_id" GROUP BY blog_posts.id
=> [5603, "0"]
ruby-1.8.7-p330 :024 > User.sum(:old_accounts_id, :joins => :blog_posts, :group
=> 'blog_posts.id').first
SQL (5.0ms) SELECT SUM("users"."old_accounts_id") AS sum_old_accounts_id,
blog_posts.id AS blog_posts_id FROM "users" INNER JOIN "blog_posts" ON
"blog_posts"."user_id" = "users"."id" GROUP BY blog_posts.id
=> [5603, 0]
Note that by switching the query around so I run the query on the model I'm
summing and join the other one I get back integers.
The only difference that I can think of is that in the former AR knows about
'BlogPost' and 'sum_old_accounts_id' and even with it's magic can't relate the
two. In the latter (i'm guessing, haven't checked the code) that it knows
'User' and 'sum_old_accounts_id'. It knows that it called 'sum' to turns it
into 'old_accounts_id' which it knows is a field of User so type casts it.
Maybe. Would be interesting to dig into the source and see....
But perhaps you can flip your query around the other way and get it to work....
-philip
On Jan 13, 2011, at 5:11 AM, IAmNan 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.
>
--
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.
