RoR native sql

gistfile1.txt

def monthly_count_of_ranch_fields(ranch_id, start_date, end_date)
      sql ="with parcel_ids as (select id from parcels where ranch_id = ?),
    zone_ids as (select id, parcel_id from zones where parcel_id in (select id from parcel_ids)),
    parcel_events as (select date, category, sub_category, area_id as parcel_id from events where area_type = 'parcel' and area_id in (select id from parcel_ids)),
    zone_events as (select e.date, e.category, e.sub_category, z.parcel_id from events e, (select id, parcel_id from zone_ids) z where e.area_id = z.id and e.area_type = 'zone'),
    all_events as (select * from parcel_events union select * from zone_events)
select extract(month from date) as month, category, sub_category, parcel_id,  count(*) from all_events where date >= ? and date <= ?
    group by extract(month from date), category, sub_category, parcel_id"

      field_event_summaries = Hash.new

      ActiveRecord::Base.connection.select_all(ActiveRecord::Base.send(:sanitize_sql_array, [sql, ranch_id, start_date, end_date])).each do |record|
          month = record['month'].to_i
          category = record['category']
          sub_category = record['sub_category']
          field_id = record['parcel_id'].to_i
          month_hash = get_or_create_hash(field_event_summaries, month)
          field_hash = get_or_create_hash(month_hash, field_id)
          category_hash = get_or_create_hash(field_hash, category)
          label = sub_category || category
          category_hash["#{label}"] = record['count'].to_i
      end

      field_event_summaries
  end