CODEWITHSUNDEEP
ruby-on-railsrubyruby-on-rails-3
Gandalf StormCrow
Gandalf StormCrow

Reputation: 26192

ActiveRecord count and group by statement

I have a Part object which has Supplier and part has field supplier_id.

So I have a number of records and I want to determine how many parts came from how many suppliers. So let's say I have parts arranged like this. Number of parts = 100

25 came from supplier_id => 3
25 came from supplier_id => 1
50 came from supplier_id => 5

So here is how I made my query :

Part.where(:order_id => 30146).select('count(id) as count, supplier_id').group('supplier_id').order('count DESC')

And it seems to produce the correct query as well :

SELECT count(id) as count, supplier_id FROM "parts" WHERE "parts"."order_id" = 30146 AND ("parts"."deleted_at" IS NULL) GROUP BY supplier_id ORDER BY count DESC

But my result is not like I intended to :

[#<Part supplier_id: 3>, #<Part supplier_id: 1>, #<Part supplier_id: 5>]

I get just the supplier ids but not the count. I wanted to get output like this :

25 => 3
25 => 1
50 => 5

What am I missing here?

Upvotes: 1

Views: 1283

Answers (2)

Tahsin Yurtseven
Tahsin Yurtseven

Reputation: 63

I think the keyword count is reserved in SQL-Query, try countA for example.

SELECT count(id) as countA, ...

Upvotes: 0

Pete
Pete

Reputation: 18075

The count method takes a hash which you can supply it to get a hash of ids to counts. For you, it might be something like:

Part.where(order_id: 123).count(group: "supplier_id")

Upvotes: 2

Related Questions