CODEWITHSUNDEEP
ruby-on-rails
krunal shah
krunal shah

Reputation: 16339

subquery with finder_sql in rails

Code

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)'

I am getting this error.

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

Upvotes: 0

Views: 463

Answers (2)

nateborr
nateborr

Reputation: 29

The issue I see in ActiveRecord 3.2.12 is in CollectionAssociation#custom_counter_sql, which replaces the SELECT clause with COUNT(...) by matching the :finder_sql option against this regex:

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

That matches everything from "SELECT" on through to the final occurrence of "FROM" in the subquery, not the first "FROM" as you'd want.

The easiest fix is to pass a custom :counter_sql option into has_many, as documented.

Upvotes: 1

tadman
tadman

Reputation: 211610

It looks like it's trying to re-interpret your finder_sql into counter_sql and doing a very bad job of it. The solution is to probably write your own counter_sql that works.

Upvotes: 1

Related Questions