title: How to include coalesce() in ActiveRecord statement
body: <p>I want to turn this raw sql statement into an ActiveRecord statement:</p>
  
  <pre><code>def self.get_new_entry_code(table_id)
    sql = "SELECT coalesce(max(code_id) + 1, 1) FROM configentries WHERE configtable_id = " + table_id.to_s
    connection.execute(sql)
  end
  </code></pre>
  
  <p>I'm struggling with the <code>coalesce()</code>. I tried the following:</p>
  
  <pre><code>def self.get_new_entry_code(table_id)
    result = Configentry.select("coalesce(max(code_id) + 1, 1").where("configtable_id = " + table_id.to_s).first
    result.id
  end
  </code></pre>
  
  <p>But this is not working and too "raw" as well. How can I accomplish this?</p>
  
  <p>What I noticed now is, it's only giving an error when I put <code>puts result</code>. How can this be?</p>
  
  <p>Error:</p>
  
  <pre><code>E, [2017-05-10T12:30:04.604953 #93397] ERROR -- : PG::SyntaxError: ERROR:  syntax error at or near "FROM"
  LINE 1: ...e(max(code_id) + 1, 1) WHERE configtable_id = 106 FROM "conf...
                                                               ^
  : SELECT  coalesce(max(code_id) + 1, 1) WHERE configtable_id = 106 FROM "configentries"   ORDER BY "configentries"."id" ASC LIMIT 1
  2017-05-10 12:30:04 - ActiveRecord::StatementInvalid - PG::SyntaxError: ERROR:  syntax error at or near "FROM"
  LINE 1: ...e(max(code_id) + 1, 1) WHERE configtable_id = 106 FROM "conf...
                                                               ^
  : SELECT  coalesce(max(code_id) + 1, 1) WHERE configtable_id = 106 FROM "configentries"   ORDER BY "configentries"."id" ASC LIMIT 1:
          /Library/Ruby/Gems/2.0.0/gems/activerecord-4.1.16/lib/active_record/connection_adapters/postgresql_adapter.rb:822:in `async_exec'
          /Library/Ruby/Gems/2.0.0/gems/activerecord-4.1.16/lib/active_record/connection_adapters/postgresql_adapter.rb:822:in `block in exec_no_cache'
          /Library/Ruby/Gems/2.0.0/gems/activerecord-4.1.16/lib/active_record/connection_adapters/abstract_adapter.rb:378:in `block in log'
          /Library/Ruby/Gems/2.0.0/gems/activesupport-4.1.16/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
          /Library/Ruby/Gems/2.0.0/gems/activerecord-4.1.16/lib/active_record/connection_adapters/abstract_adapter.rb:372:in `log'
          /Library/Ruby/Gems/2.0.0/gems/activerecord-4.1.16/lib/active_record/connection_adapters/postgresql_adapter.rb:822:in `exec_no_cache'
          /Library/Ruby/Gems/2.0.0/gems/activerecord-4.1.16/lib/active_record/connection_adapters/postgresql/database_statements.rb:137:in `exec_query'
          /Library/Ruby/Gems/2.0.0/gems/activerecord-4.1.16/lib/active_record/connection_adapters/postgresql_adapter.rb:954:in `select'
          /Library/Ruby/Gems/2.0.0/gems/activerecord-4.1.16/lib/active_record/connection_adapters/abstract/database_statements.rb:24:in `select_all'
          /Library/Ruby/Gems/2.0.0/gems/activerecord-4.1.16/lib/active_record/connection_adapters/abstract/query_cache.rb:70:in `select_all'
          /Library/Ruby/Gems/2.0.0/gems/activerecord-4.1.16/lib/active_record/querying.rb:39:in `find_by_sql'
          /Library/Ruby/Gems/2.0.0/gems/activerecord-4.1.16/lib/active_record/relation.rb:611:in `exec_queries'
          /Library/Ruby/Gems/2.0.0/gems/activerecord-4.1.16/lib/active_record/relation.rb:493:in `load'
          /Library/Ruby/Gems/2.0.0/gems/activerecord-4.1.16/lib/active_record/relation.rb:238:in `to_a'
          /Library/Ruby/Gems/2.0.0/gems/activerecord-4.1.16/lib/active_record/relation/finder_methods.rb:474:in `find_nth_with_limit'
          /Library/Ruby/Gems/2.0.0/gems/activerecord-4.1.16/lib/active_record/relation/finder_methods.rb:468:in `find_nth'
          /Library/Ruby/Gems/2.0.0/gems/activerecord-4.1.16/lib/active_record/relation/finder_methods.rb:132:in `first'
          /Users/ndinatale/leanlogic-qa-prototype/server/models/device_configentry.rb:92:in `get_new_entry_code'
          /Users/ndinatale/leanlogic-qa-prototype/server/lib/models_base.rb:82:in `merge_data'
          /Users/ndinatale/leanlogic-qa-prototype/server/routes/02_rest_misc.rb:298:in `block (2 levels) in &lt;top (required)&gt;'
          /Library/Ruby/Gems/2.0.0/gems/activerecord-4.1.16/lib/active_record/connection_adapters/abstract/database_statements.rb:201:in `block in transaction'
          /Library/Ruby/Gems/2.0.0/gems/activerecord-4.1.16/lib/active_record/connection_adapters/abstract/database_statements.rb:209:in `within_new_transaction'
          /Library/Ruby/Gems/2.0.0/gems/activerecord-4.1.16/lib/active_record/connection_adapters/abstract/database_statements.rb:201:in `transaction'
          /Library/Ruby/Gems/2.0.0/gems/activerecord-4.1.16/lib/active_record/transactions.rb:208:in `transaction'
          /Users/ndinatale/leanlogic-qa-prototype/server/routes/02_rest_misc.rb:297:in `block in &lt;top (required)&gt;'
          /Library/Ruby/Gems/2.0.0/gems/sinatra-1.4.8/lib/sinatra/base.rb:1611:in `call'
          /Library/Ruby/Gems/2.0.0/gems/sinatra-1.4.8/lib/sinatra/base.rb:1611:in `block in compile!'
          /Library/Ruby/Gems/2.0.0/gems/sinatra-1.4.8/lib/sinatra/base.rb:975:in `[]'
          /Library/Ruby/Gems/2.0.0/gems/sinatra-1.4.8/lib/sinatra/base.rb:975:in `block (3 levels) in route!'
          /Library/Ruby/Gems/2.0.0/gems/sinatra-1.4.8/lib/sinatra/base.rb:994:in `route_eval'
          /Library/Ruby/Gems/2.0.0/gems/sinatra-1.4.8/lib/sinatra/base.rb:975:in `block (2 levels) in route!'
          /Library/Ruby/Gems/2.0.0/gems/sinatra-1.4.8/lib/sinatra/base.rb:1015:in `block in process_route'
          /Library/Ruby/Gems/2.0.0/gems/sinatra-1.4.8/lib/sinatra/base.rb:1013:in `catch'
          /Library/Ruby/Gems/2.0.0/gems/sinatra-1.4.8/lib/sinatra/base.rb:1013:in `process_route'
          /Library/Ruby/Gems/2.0.0/gems/sinatra-1.4.8/lib/sinatra/base.rb:973:in `block in route!'
          /Library/Ruby/Gems/2.0.0/gems/sinatra-1.4.8/lib/sinatra/base.rb:972:in `each'
          /Library/Ruby/Gems/2.0.0/gems/sinatra-1.4.8/lib/sinatra/base.rb:972:in `route!'
          /Library/Ruby/Gems/2.0.0/gems/sinatra-1.4.8/lib/sinatra/base.rb:1085:in `block in dispatch!'
          /Library/Ruby/Gems/2.0.0/gems/sinatra-1.4.8/lib/sinatra/base.rb:1067:in `block in invoke'
          /Library/Ruby/Gems/2.0.0/gems/sinatra-1.4.8/lib/sinatra/base.rb:1067:in `catch'
          /Library/Ruby/Gems/2.0.0/gems/sinatra-1.4.8/lib/sinatra/base.rb:1067:in `invoke'
          /Library/Ruby/Gems/2.0.0/gems/sinatra-1.4.8/lib/sinatra/base.rb:1082:in `dispatch!'
          /Library/Ruby/Gems/2.0.0/gems/sinatra-1.4.8/lib/sinatra/base.rb:907:in `block in call!'
          /Library/Ruby/Gems/2.0.0/gems/sinatra-1.4.8/lib/sinatra/base.rb:1067:in `block in invoke'
          /Library/Ruby/Gems/2.0.0/gems/sinatra-1.4.8/lib/sinatra/base.rb:1067:in `catch'
          /Library/Ruby/Gems/2.0.0/gems/sinatra-1.4.8/lib/sinatra/base.rb:1067:in `invoke'
          /Library/Ruby/Gems/2.0.0/gems/sinatra-1.4.8/lib/sinatra/base.rb:907:in `call!'
          /Library/Ruby/Gems/2.0.0/gems/sinatra-1.4.8/lib/sinatra/base.rb:895:in `call'
          /Library/Ruby/Gems/2.0.0/gems/rack-1.6.5/lib/rack/session/abstract/id.rb:225:in `context'
          /Library/Ruby/Gems/2.0.0/gems/rack-1.6.5/lib/rack/session/abstract/id.rb:220:in `call'
          /Library/Ruby/Gems/2.0.0/gems/activerecord-4.1.16/lib/active_record/connection_adapters/abstract/connection_pool.rb:621:in `call'
          /Library/Ruby/Gems/2.0.0/gems/rack-protection-1.5.3/lib/rack/protection/xss_header.rb:18:in `call'
          /Library/Ruby/Gems/2.0.0/gems/rack-protection-1.5.3/lib/rack/protection/path_traversal.rb:16:in `call'
          /Library/Ruby/Gems/2.0.0/gems/rack-protection-1.5.3/lib/rack/protection/json_csrf.rb:18:in `call'
          /Library/Ruby/Gems/2.0.0/gems/rack-protection-1.5.3/lib/rack/protection/base.rb:49:in `call'
          /Library/Ruby/Gems/2.0.0/gems/rack-protection-1.5.3/lib/rack/protection/base.rb:49:in `call'
          /Library/Ruby/Gems/2.0.0/gems/rack-protection-1.5.3/lib/rack/protection/frame_options.rb:31:in `call'
          /Library/Ruby/Gems/2.0.0/gems/rack-1.6.5/lib/rack/nulllogger.rb:9:in `call'
          /Library/Ruby/Gems/2.0.0/gems/rack-1.6.5/lib/rack/head.rb:13:in `call'
          /Library/Ruby/Gems/2.0.0/gems/rack-1.6.5/lib/rack/methodoverride.rb:22:in `call'
          /Library/Ruby/Gems/2.0.0/gems/sinatra-1.4.8/lib/sinatra/show_exceptions.rb:25:in `call'
          /Library/Ruby/Gems/2.0.0/gems/sinatra-1.4.8/lib/sinatra/base.rb:182:in `call'
          /Library/Ruby/Gems/2.0.0/gems/sinatra-1.4.8/lib/sinatra/base.rb:2013:in `call'
          /Library/Ruby/Gems/2.0.0/gems/sinatra-1.4.8/lib/sinatra/base.rb:1487:in `block in call'
          /Library/Ruby/Gems/2.0.0/gems/sinatra-1.4.8/lib/sinatra/base.rb:1785:in `synchronize'
          /Library/Ruby/Gems/2.0.0/gems/sinatra-1.4.8/lib/sinatra/base.rb:1785:in `synchronize'
          /Library/Ruby/Gems/2.0.0/gems/sinatra-1.4.8/lib/sinatra/base.rb:1487:in `call'
          /Library/Ruby/Gems/2.0.0/gems/puma-3.8.2/lib/puma/configuration.rb:224:in `call'
          /Library/Ruby/Gems/2.0.0/gems/puma-3.8.2/lib/puma/server.rb:600:in `handle_request'
          /Library/Ruby/Gems/2.0.0/gems/puma-3.8.2/lib/puma/server.rb:435:in `process_client'
          /Library/Ruby/Gems/2.0.0/gems/puma-3.8.2/lib/puma/server.rb:299:in `block in run'
          /Library/Ruby/Gems/2.0.0/gems/puma-3.8.2/lib/puma/thread_pool.rb:120:in `call'
          /Library/Ruby/Gems/2.0.0/gems/puma-3.8.2/lib/puma/thread_pool.rb:120:in `block in spawn_thread'
  </code></pre>