Below are few rows of indicator_values database table with records of countries and indicators for repeated years:
I would like to get the value that corresponds to the most recent year for each country into an array:
[{"name"=>"Water", "data"=>[86.35, 88.34]}, #indicator2
{"name"=>"Electricity", "data"=>[68.62, 94.47]}, ....] #indicator1
I can get the recent years data for each country and indicator using a query and then apply .where() and .pluck() active record queries on the object returned by the SQL query.
[2,3,4].map { |i|
sql = "SELECT a.country_id, a.indicator_id, a.value, a.year
FROM indicator_values a
INNER JOIN (
SELECT country_id, indicator_id, MAX(year) AS year
FROM indicator_values
GROUP BY country_id, indicator_id
) b ON a.country_id = b.country_id AND a.year = b.year and a.indicator_id = b.indicator_id
ORDER BY country_id, indicator_id;"
recent_years_data = ActiveRecord::Base.connection.execute(sql)
my_array = { "name" => Indicator.find(i).name, "data" => recent_years_data.where('indicator_id = (?) and country_id IN (?)', i, selected_countries).pluck(:value) } }
The result of the query is not an ActiveRecord::Relation object and therefore it cannot work with .where() and .pluck(). I seek a way to get an ActiveRecord::Relation or a different workaround to create the desired array. Similar posts advice using Active Record Query but I could not get to how to chain such a long query as an Active Record Query instead of raw SQL.
