ruby-on-rails 如何在ActiveRecord语句中包含coalesce()

f3temu5u  于 2023-05-19  发布在  Ruby
关注(0)|答案(1)|浏览(143)

我想把这个原始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'
ruby-on-rails

1条答案

按热度按时间
cig3rfwq

cig3rfwq1#

您有语法错误
您在coalesce(max(code_id) + 1, 1)后缺少一个右括号

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

此外，find_by优于where().first

赞(0）分享  回复(0）举报  2023-05-19
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com