Sql Query in rake file not showing result

Question

I am trying to run this SQL query in a rake task but it does show any result. But I have tried running this query in Dbeaver and it runs perfectly fine. The first puts just show # in the terminal and second puts doesn't show any value. Kindly help me find the problem.

namespace :expense do
  desc "send emails"
  task send_reminder: :environment do
  sql = <<-SQL
  SELECT
    c.id             as corporation_id
  , cs_i.id          as issue_id
  , MAX(i.id)        as expense_id
  , MAX(i."date")    as expense_date
  FROM companies c
  JOIN expenses i on i.corporation_id = c.id
  JOIN issues cs_i on i.issue_id = cs_i.id
  WHERE c.amount > 0 and 
        cs_i.amount > 0 and 
        i."date" < (select (current_date at time zone 'UTC' - interval '1 week')) and i.amount_type = 0 
  GROUP BY c.id, cs_i.id
  SQL
    scope = Expense.connection.execute(sql)
      puts "#{scope}"
    scope.each do |expense|
      puts "#{expense}"
    end    
  end
end

Answer 1

If you are trying to run raw SQL, you probably should not use connection.execute. See the documentation for the execute method:

Executes the SQL statement in the context of this connection and returns the raw result from the connection adapter. Note: depending on your database connector, the result returned by this method may be manually memory managed. Consider using the exec_query wrapper instead.

execute returns raw results from your connection adapter. eg for the PostgreSQL it is PG::Result object.

You probably don't want to use exec_query either because it will also return some kind of raw results.

From what I see in your code, you probably want to use find_by_sql. It executes the query and returns standard ActiveRecord objects:

namespace :expense do
  desc "send emails"
  task send_reminder: :environment do
    sql = <<-SQL
      sql truncated...
    SQL

    scope = Expense.find_by_sql(sql)
      puts "#{scope}"
    scope.each do |expense|
      puts "#{expense}"
    end    
  end
end