Rails group by column and select column

Question

I have a table DinnerItem with columns id, name, project_id, client_id, item_id and item_quantity.

I want to fetch data group_by item_id column and the value should only have the item_quantity column value in the format

{ item_id1 => [ {item_quantity from row1}, {item_quantity from row2}],
  item_id2 => [ {item_quantity from row3}, {item_quantity from row4} ]
}

How can I achieve it in one single query?

OfferServiceModels::DinnerItem.all.select('item_id, item_quantity').group_by(&:item_id)

But this has the format

 {1=>[#<DinnerItem id: nil, item_id: 1, item_quantity: nil>, #<DinnerItem id: nil, item_id: 1, item_quantity: {"50"=>30, "100"=>10}>], 4=>[#<DinnerItem id: nil, item_id: 4, item_quantity: {"100"=>5, "1000"=>2}>}

Answer 1

Something like this should do the job:

result = OfferServiceModels::DinnerItem
  .pluck(:item_id, :item_quantity)
  .group_by(&:shift)
  .transform_values(&:flatten)
#=> {1 => [10, 20], 2 => [30, 40]}
#    ^ item id            ^^  ^^ item quantity

A step by step explanation:

# retrieve the item_id and item_quantity for each record
result = OfferServiceModels::DinnerItem.pluck(:item_id, :item_quantity)
#=> [[1, 10] [1, 20], [2, 30], [2, 40]]
#     ^ item id           ^^ item quantity

# group the records by item id, removing the item id from the array
result = result.group_by(&:shift)
#=> {1 => [[10], [20]], 2 => [[30], [40]]}
#    ^ item id                 ^^    ^^ item quantity

# flatten the groups since we don't want double nested arrays
result = result.transform_values(&:flatten)
#=> {1 => [10, 20], 2 => [30, 40]}
#    ^ item id            ^^  ^^ item quantity

references:

• pluck
• group_by
• shift
• transform_values
• flatten

Answer 2

You can keep the query and the grouping, but append as_json to the operation:

DinnerItem.select(:item_id, :item_quantity).group_by(&:item_id).as_json
# {"1"=>[{"id"=>nil, "item_id"=>1, "item_quantity"=>1}, {"id"=>nil, "item_id"=>1, "item_quantity"=>2}],
#  "2"=>[{"id"=>nil, "item_id"=>2, "item_quantity"=>1}, {"id"=>nil, "item_id"=>2, "item_quantity"=>2}]}

Notice as_json will add the id of each row which will have a nil value.