Как включить coalesce() в оператор ActiveRecord

Question

Я хочу превратить этот необработанный SQL-оператор в оператор ActiveRecord:

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

Я борюсь с coalesce(), Я попробовал следующее:

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

Но это не работает и слишком "сыро". Как я могу сделать это?

Что я заметил сейчас, это только дает ошибку, когда я ставлю puts result, Как это может быть?

Ошибка:

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 <top (required)>'
        /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 <top (required)>'
        /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'

Answer 1

У вас есть синтаксическая ошибка

Вы пропустили одну закрывающую скобку после coalesce(max(code_id) + 1, 1)

Configentry.select("coalesce(max(code_id) + 1, 1)").find_by(configtable_id: table_id)

Также предпочитаю find_by над where().first

Answer 2

Более Ruby-ish/менее сырой способ сделать это, еслиconfigentriesтаблица имеет класс модели ActiveRecord, может быть:

      def self.get_new_entry_code(table_id)
  Configentries.where(configtable_id: table_id).maximum(code_id) || 1
end

Если этот метод класса находится вConfigentriesкласс тогда:

      def self.get_new_entry_code(table_id)
  where(configtable_id: table_id).maximum(code_id) || 1
end