代码之家 › 专栏 › 技术社区 › krunal shah

rails中带finder_-sql的子查询

ruby-on-rails

krunal shah · 技术社区 · 14 年前

代码

has_many :open_invoices, :class_name => "ArInvHeader",
:finder_sql => 'SELECT ar_inv_headers.* FROM ar_inv_headers WHERE customer_id = #{id}  and
orig_amt = (select sum(amount) from ar_inv_actions,ar_inv_headers where ar_inv_actions.ar_inv_header_id = ar_inv_headers.id)'

ActiveRecord::StatementInvalid: Mysql::Error: You have an error in your SQL syntax; check the manual that corresponds to y
our MySQL server version for the right syntax to use near ')' at line 1: SELECT COUNT(*) FROM ar_inv_actions,ar_inv_header
s where ar_inv_actions.ar_inv_header_id = ar_inv_headers.id)
    from C:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.9/lib/active_record/connection_adapters/abstract_adapter.rb:2
27:in `log'
    from C:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.9/lib/active_record/connection_adapters/mysql_adapter.rb:324:
in `execute'
    from C:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.9/lib/active_record/connection_adapters/mysql_adapter.rb:639:
in `select'
    from C:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.9/lib/active_record/connection_adapters/abstract/database_sta
tements.rb:7:in `select_all_without_query_cache'
    from C:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.9/lib/active_record/connection_adapters/abstract/query_cache.
rb:62:in `select_all'
    from C:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.9/lib/active_record/connection_adapters/abstract/database_sta
tements.rb:13:in `select_one'
    from C:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.9/lib/active_record/connection_adapters/abstract/database_sta
tements.rb:19:in `select_value'
    from C:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.9/lib/active_record/base.rb:920:in `count_by_sql'
    from C:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.9/lib/active_record/associations/has_many_association.rb:34:i
n `count_records'
    from C:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.9/lib/active_record/associations/association_collection.rb:27
7:in `size'
    from C:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.9/lib/active_record/associations/association_collection.rb:29
6:in `empty?'
    from (irb):9

2 回复 | 直到 14 年前

tadman 14 年前

它似乎在试图将finder sql重新解释为counter sql,并且做得非常糟糕。解决方案可能是编写自己的有效计数器。

nateborr 11 年前

我在ActiveRecord 3.2.12中看到的问题是 CollectionAssociation#custom_counter_sql :finder_sql 针对此regex的选项:

/SELECT\b(\/\*.*?\*\/ )?(.*)\bFROM\b/im

它匹配从“SELECT”到子查询中“from”的最后一个匹配项,而不是您希望的第一个“from”。

最简单的解决方法是传递一个自定义 :counter_sql has_many ,作为 documented