CODEWITHSUNDEEP
ruby-on-railsarraysactiverecord
willfore
willfore

Reputation: 170

Combine ActiveRecord::Result in .each loop

I have this working currently but there must be a better way. I have a model where I query an external oracle DB view. In this particular case I have to run one query per id value and combine these results into an array.

This works but, it requires quite a bit of maintenance on the Array to get the information in an easier to use format. Hopefully there is a way to simplify this:

def self.count_by_year(ids, year)
    start_date = DateTime.strptime(year.to_s, "%Y").strftime("%Y%m%d")
    end_date = DateTime.strptime(year.to_s, "%Y").end_of_year.strftime("%Y%m%d")
    bulk_issues = []
    if ids.size > 1
      ids.each do |uc|
        bulk_issues << (External.connection.select_all "SELECT COUNT(*) FROM DB WHERE ID='#{uc.id}' AND GROUP_NAME != 'CA' AND STATUS != 'Cancelled' AND (DATE_OPENED BETWEEN '#{start_date}' AND '#{end_date}')")
      end
    else
      bulk_issues << (External.connection.select_all "SELECT COUNT(*) FROM DB WHERE ID='#{ids.first.id}' AND GROUP_NAME != 'CA' AND STATUS != 'Cancelled' AND (DATE_OPENED BETWEEN '#{start_date}' AND '#{end_date}')")
    end
    return bulk_issues
  end

When called this returns the following:

[#<ActiveRecord::Result:0x007fdafd95ec20 @columns=["count(*)"], @rows=[[51]], @hash_rows=nil, @column_types={}>, #<ActiveRecord::Result:0x007fdafd95d320 @columns=["count(*)"], @rows=[[19]], @hash_rows=nil, @column_types={}>]

When flattened it returns:

[{"count(*)"=>51}, {"count(*)"=>19}]

Which is a pain to pull the data out of since I have to pull the values out and put them into a new array for it to be formatted for what I am using the values for.

What I am trying to do is get the function to return the data in a array as follows:

[51, 19]

Any help on this would be greatly appreciated!

Upvotes: 1

Views: 294

Answers (1)

smathy
smathy

Reputation: 27961

Well, for a start, Oracle supports IN expressions, so instead of your ids.each... just do:

bulk_issues = External.connection.select_all "SELECT COUNT(*) FROM DB WHERE ID IN (#{ids.map{|id| "'#{id.id}'"}.join(",")}) AND GROUP_NAME != 'CA' AND STATUS != 'Cancelled' AND (DATE_OPENED BETWEEN '#{start_date}' AND '#{end_date}')"

So then this won't be an array, it'll just be a hash like {"count(*)"=>70}, so to get just 70 out of that call:

*, count = bulk_issues.first

So adding an AS to your query so we can be sure what our hash will return, your whole method becomes:

def self.count_by_year(ids, year)
  start_date = DateTime.strptime(year.to_s, "%Y").strftime("%Y%m%d")
  end_date = DateTime.strptime(year.to_s, "%Y").end_of_year.strftime("%Y%m%d")

  res = External.connection.select_all("SELECT COUNT(*) AS count FROM DB WHERE ID IN (#{ids.map{|id| "'#{id.id}'"}.join(",")}) AND GROUP_NAME != 'CA' AND STATUS != 'Cancelled' AND (DATE_OPENED BETWEEN '#{start_date}' AND '#{end_date}')")

  return res.to_hash.first['count'].to_i
end

Upvotes: 1

Related Questions