rails database records issue

Question

This is my query method in model file:

def self.sum_by_brand_category
  result = Product.joins(:brand, :category)
           .select("brands.id as brand_id, categories.id as category_id, sum(products.quantity) as count")
           .group("brands.id, categories.id")
  return result
end

Here is the sample database query result I get:

[
  {
    "id":null,
    "brand_id":43,
    "category_id":1,
    "count":2
  },
  {
    "id":null,
    "brand_id":43,
    "category_id":2,
    "count":5
  },
  {
    "id":null,
    "brand_id":43,
    "category_id":3,
    "count":4
  },
  ....
]

I would expect the final JSON result to be used in views should be like this:

[
  {
    "id":null,
    "brand_id":43,
    "quantity": [
      {
        "category_id": 1,
        "count": 2
      },
      {
        "category_id": 2,
        "count": 5
      },
      {
        "category_id": 3,
        "count": 4
      }
    ]
  },
  ....
]

How can I achieve it? Change the model method? Rebuild the result in the controller before sending it to the view? and how?

Any suggestions will be appreciated. Thank you.

Updated:

Based on @cmrichards 's answer, I come up with this private method to be called in controller and then used in views. I am including my work here, although these are not so DRY codes:

private
  def get_sum_by_brand_category
    query_results = Product.sum_by_brand_category

    results = []

    query_results.group_by(&:brand_id).each do |brand_id, query_result|
      result = {}
      result[:id] = nil
      result[:brand_id] = brand_id
      quantity_array = []
      query_result.each do |data|
        quantity_block = {}
        quantity_block[:category_id] = data.category_id
        quantity_block[:count] = data.count
        quantity_array.push(quantity_block)
      end
      result[:quantity] = quantity_array

      results.push(result)
    end

    return results
  end

Please DRYing them out if you'd like to, by editing my question. ;)

@Gabbar, they don't. But a brand can have different categories of products, likewise, a category can contain different brands of products. I am not really sure if I wrote the expected JSON result correct or not. I am trying to display these stats in a stacked bar chart, or perhaps a grouped bar chart. I can get results per brand and per category separately, but I want to combine them together. — lyonsun
– lyonsun, Commented Jun 6, 2018 at 13:32

cmrichards · Accepted Answer · 2018-06-06 13:48:28Z

1

One option is to use jBuilder, a popular gem maintained by the Rails team that provides a simple DSL (domain-specific language) allowing you to define JSON structures in your views.

https://github.com/rails/jbuilder

Using your existing method you could do it like this

controller

def index
    @data = Product.sum_by_brand_category    
end

views/products/index.json.jbuilder

json.array! @data.group_by(&:brand_id) do |grouped|
  brand_id, category_data = grouped
  json.id nil
  json.brand_id brand_id
  json.quantity category_data, :category_id, :count
end

answered Jun 6, 2018 at 13:48

cmrichards

1,7721 gold badge20 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

lyonsun Over a year ago

Works like a charm! Based on your suggestion, I have come up with another option as a private method to generate this data in controller so that it can be called in actions and then be used in views. I will include my workout in updates soon. Really, thank you very much, I will mark your answer as accepted.

Jagdish Narayandasani Over a year ago

You can try Serializers api.rubyonrails.org/classes/ActiveModel/Serializers/JSON.html

Collectives™ on Stack Overflow

rails database records issue

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related