I do the following so I am able to group all LineItem's together by count and display the LineItem by count along with the vendor_name
line_items = LineItem.all
vendor_line_items = line_items.group(:vendor_name).select('COUNT(*) as count', 'vendor_name').order('count desc')
My issue is that I am only able to receive the following params: id: nil, vendor_name: "name_here"
Is there a way to accomplish the same thing but allow all params from the model to be passed?
You can't select the rest of the columns since you have different values for each coulmn inside the group (like... if you have 2 LineItem in the same group, which ID do you expect to have?)
You could apply aggregate functions (like COUNT, MAX, MIN, etc) to other columns on the SELECT to tell the database which columns you want for each column I guess.
Personally, I would first get the groups ordered by count and then do more queries when needed to fetch the actual record for the groups.
counts = LineItem.group(:vendor_name).count
# counts should be something like: {vendor_1: X, vendor_2, Y, vendor_3: Z}
# order the vendors using the count for each vendor
ordered_vendors = counts.keys.sort_by { |ven| counts[ven] }
ordered_vendors.each do |vendor|
# do something with each vendor, fetch LineItems, etc
end
The reason why you only see the count and the vendor name is because that is all you are grouping by. Suppose in the database, you have 5 different Vendor A shown below.
vendor_name | product_name
-----------------------------
Vendor A | test
Vendor A | test2
Vendor A | test3
Vendor A | test4
Vendor A | test5
...
When you run your query, SQL will not know what to display for product_name as the group_by will only show 1 row instead of 5. Have a read about it here.
To achieve this you will need to either to group by the other columns too or use a min/max select to pick a value to display. Here is an example:
vendor_line_items = LineItem.select('COUNT(*) AS count', 'vendor_name', 'MAX(product_name)').group(:vendor_name).order('count DESC')
Now each of those results, you can call the attributes method.
Which will give you the following hash:
vendor_line_items.each do |x|
result = x.attributes
# Here result will be a hash.
# {"count" => 5, "vendor_name" => "Vendor A", "product_name" => "test5"}
end
(Not accepted answer unless a better way is received)
I did:
vendor_line_items = Vendor.joins(:line_items).group(:id).order('COUNT(line_items.id) DESC')
This gives me what I want by ordering the results by vendor.line_items.count and allowing me to get all of the associations to display any param I want.
I assume this way is much slower than what I was previously doing as it fetches all records and then on the front end goes through associations to get more records.
In the original way I was doing this. It is what I want minus an extra parameter that I would want the SUM of. The parameter is a decimal attribute. In the same way I count the LineItem that have the same vendor_name, I want to sum of the LineItem.attribute that share the same vendor_name.
Better Answer:
LineItem.select(:vendor_name, 'sum(line_item_revenue) as line_item_revenue', 'COUNT(*) as count').group(:vendor_name)
This seems to get me what I want with less queries (i believe) --- correct me if I am wrong on the queries.
I am quite confused about your code and your expectation. You are selecting the COUNT but the expected result is id instead of count?
If you want to group by vendor_name and show the count of group_by you can try
line_items.group(:vendor_name).count
