Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Rails mulitple AR operations (min, max, avg.. ) on same query object

I want to perform multiple calculations using one query to price_histories table, and finally render some statistics using those prices like average, minimum and maximum etc.

price_histories_controller.rb

price_stats = PriceHistory.where('created_at >= ? AND cast(item_id as integer) = ?', 1.day.ago, params['item_id'])

avg_array = price_stats.group(:day).average(:price).to_a
min_array = price_stats.group(:day).min(:price).to_a
max_array = price_stats.group(:day).max(:price).to_a
count_array = price_stats.group(:day).count(:price).to_a

This is the relevant code that causes the error, i'd like to perform some calculations on a set of grouped data but after the first calculation is done, I keep getting

TypeError (no implicit conversion of Symbol into Integer)

Ideally I would end up with an object like this one to be rendered:

@all_stats = {
  average: avg_array,
  min: min_array,
  max: max_array,
  count: count_array
}

render json: @all_stats

This sums up my intentions pretty well, I'm new to ruby and I'd like a solution or a better approach which I'm sure there are.

The following code works fine and I'd like anyone to point me in the right direction to finding out why this works fine and when adding and extra calculation it doesn't:

    price_stats = PriceHistory.where('created_at >= ? AND cast(item_id as integer) = ?', 1.day.ago, params['item_id'])

    avg_array = price_stats.group(:day).average(:price).to_a

and leads to:

{
  "average": [
      [
         null,
         "11666.666666666667"
      ],
      [
         "24/4/2019",
         "11666.666666666667"
      ],
      [
          "24",
          "11666.6666666666666667"
      ],
      [
          "2051",
          "11666.6666666666666667"
      ]
  ],
  "min": [],
  "max": [],
  "count": []
}

Other approach:

      PriceHistory.select(
        "AVG(price) AS average_score, 
        MIN(price) AS average_min, 
        MAX(price) AS average_max, 
        COUNT(*) AS price_count"
      ).where(
        'created_at >= ? AND cast(item_id as integer) = ?', 
        1.day.ago, params['item_id']
      ).group(:day)

Error:

ArgumentError (Call `select' with at least one field):

like image 888
Alejandro Camba Avatar asked Mar 05 '23 02:03

Alejandro Camba


1 Answers

I think this should work:

PriceHistory.where(
  'created_at >= ? AND cast(item_id as integer) = ?',
  1.day.ago,
  params['item_id']
).group(:day).select(
  "SUM(price) AS sum_price",
  "MAX(price) AS max_price",
  "MIN(price) AS min_price",
  "AVG(price) AS avg_price",
  "day"
)

This will return you an array of records, each which has methods day, sum_price, max_price, min_price, and avg_price.

Note that the names of the SQL functions might be different based on your db

like image 84
max pleaner Avatar answered Mar 07 '23 04:03

max pleaner