Ruby China

新手问题 数据库报错，请高手帮忙！

kai209209 · 2014年08月06日 · 最后由 ane 回复于 2014年08月07日 · 3609 次阅读

这段时间做项目遇到一个问题，google 了很久，也没能找到办法解决，现在希望请社区里的高手们帮忙看一下。 我将项目遇到的问题进行了简化，如下： 我有 2 个 model：user、account。 user 有 name、email 等字段，account 有 money、user_id

class User < ActiveRecord::Base
  has_many :accounts

  def self.find_account_money
    users = self.select("id, name, (#{Account.select("SUM(money)").group("user_id").where("accounts.user_id = users.id").to_sql}) AS user_money")
    users
  end
end

class Account < ActiveRecord::Base
  belongs_to :user
end

在控制台 rails c 运行代码

2.1.2p95 :001 > users = User.find_account_money
  User Load (1.5ms)  SELECT id, name, (SELECT SUM(money) FROM `accounts`  WHERE (accounts.user_id = users.id) GROUP BY user_id) AS user_money FROM `users`
 => #<ActiveRecord::Relation [#<User id: 1, name: "zky">, #<User id: 2, name: "test">]> 
2.1.2p95 :002 >

这里是能找到 user 的，并且可以获取到，然后可以找到 User 的所有 account 里面的 money 汇总起来得出来的 user_money

2.1.2p95 :003 > users.last.user_money
 => 600.0 
2.1.2p95 :004 >

这个时候我想获取 users 的对象个数

2.1.2p95 :004 > users.length
 => 2 
2.1.2p95 :005 > users.size
 => 2 
2.1.2p95 :006 >

使用 ruby 的语法是能正确计算出来的，但是我想通过数据库的 count 来进行计算

2.1.2p95 :006 > users.count
   (1.1ms)  SELECT COUNT(id, name, (SELECT SUM(money) FROM `accounts`  WHERE (accounts.user_id = users.id) GROUP BY user_id) AS user_money) FROM `users`
Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' name, (SELECT SUM(money) FROM `accounts`  WHERE (accounts.user_id = users.id) G' at line 1: SELECT COUNT(id, name, (SELECT SUM(money) FROM `accounts`  WHERE (accounts.user_id = users.id) GROUP BY user_id) AS user_money) FROM `users`
ActiveRecord::StatementInvalid: Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' name, (SELECT SUM(money) FROM `accounts`  WHERE (accounts.user_id = users.id) G' at line 1: SELECT COUNT(id, name, (SELECT SUM(money) FROM `accounts`  WHERE (accounts.user_id = users.id) GROUP BY user_id) AS user_money) FROM `users`
    from /home/zky/.rvm/gems/ruby-2.1.2/gems/activerecord-4.1.4/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:303:in `query'
    from /home/zky/.rvm/gems/ruby-2.1.2/gems/activerecord-4.1.4/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:303:in `block in execute'
    from /home/zky/.rvm/gems/ruby-2.1.2/gems/activerecord-4.1.4/lib/active_record/connection_adapters/abstract_adapter.rb:373:in `block in log'
    from /home/zky/.rvm/gems/ruby-2.1.2/gems/activesupport-4.1.4/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
    from /home/zky/.rvm/gems/ruby-2.1.2/gems/activerecord-4.1.4/lib/active_record/connection_adapters/abstract_adapter.rb:367:in `log'
    from /home/zky/.rvm/gems/ruby-2.1.2/gems/activerecord-4.1.4/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:303:in `execute'
    from /home/zky/.rvm/gems/ruby-2.1.2/gems/activerecord-4.1.4/lib/active_record/connection_adapters/mysql2_adapter.rb:228:in `execute'
    from /home/zky/.rvm/gems/ruby-2.1.2/gems/activerecord-4.1.4/lib/active_record/connection_adapters/mysql2_adapter.rb:232:in `exec_query'
    from /home/zky/.rvm/gems/ruby-2.1.2/gems/activerecord-4.1.4/lib/active_record/connection_adapters/mysql2_adapter.rb:240:in `select'
    from /home/zky/.rvm/gems/ruby-2.1.2/gems/activerecord-4.1.4/lib/active_record/connection_adapters/abstract/database_statements.rb:24:in `select_all'
    from /home/zky/.rvm/gems/ruby-2.1.2/gems/activerecord-4.1.4/lib/active_record/connection_adapters/abstract/query_cache.rb:70:in `select_all'
    from /home/zky/.rvm/gems/ruby-2.1.2/gems/activerecord-4.1.4/lib/active_record/relation/calculations.rb:262:in `execute_simple_calculation'
    from /home/zky/.rvm/gems/ruby-2.1.2/gems/activerecord-4.1.4/lib/active_record/relation/calculations.rb:224:in `perform_calculation'
    from /home/zky/.rvm/gems/ruby-2.1.2/gems/activerecord-4.1.4/lib/active_record/relation/calculations.rb:119:in `calculate'
    from /home/zky/.rvm/gems/ruby-2.1.2/gems/activerecord-4.1.4/lib/active_record/relation/calculations.rb:34:in `count'
    from (irb):6
    from /home/zky/.rvm/gems/ruby-2.1.2/gems/railties-4.1.4/lib/rails/commands/console.rb:90:in `start'
    from /home/zky/.rvm/gems/ruby-2.1.2/gems/railties-4.1.4/lib/rails/commands/console.rb:9:in `start'
    from /home/zky/.rvm/gems/ruby-2.1.2/gems/railties-4.1.4/lib/rails/commands/commands_tasks.rb:69:in `console'
    from /home/zky/.rvm/gems/ruby-2.1.2/gems/railties-4.1.4/lib/rails/commands/commands_tasks.rb:40:in `run_command!'
    from /home/zky/.rvm/gems/ruby-2.1.2/gems/railties-4.1.4/lib/rails/commands.rb:17:in `<top (required)>'
    from bin/rails:4:in `require'
    from bin/rails:4:in `<main>'2.1.2p95 :007 >

数据库提示错误，我想知道这错误的本质是什么，如果我想获得数量通过 count 计算出来，应该怎么修改呢？请高手们帮忙看看！先在此谢谢了！

vincent #1 2014年08月07日 
def self.find_account_money
  users = self.select("id, name, (#{Account.select("SUM(money)").group("user_id").where("accounts.user_id = users.id").to_sql}) AS user_money")
  users
end

这个写法太丑陋了，错误的本质是硬凑 sql 导致语法错误了。 完全可以通过

user_accounts = Account.select("user_id, sum(money) as user_money").group("user_id")

得到你想要的分组汇总数据。

如果想用 count，可以这样

# 全部 user count
User.count
# 有 money 的 user count
Account.count(:user_id, distinct: true)
kai209209 #2 2014年08月07日

#1 楼 @vincent

凑子查询是为了能将数据合并起来方便使用

这个 select 里面只要超过一个就会发生报错

2.1.2 :016 > User.select("id, name")
  User Load (0.6ms)  SELECT id, name FROM `users`
 => #<ActiveRecord::Relation [#<User id: 5, name: "admin">, #<User id: 6, name: "test">]> 
2.1.2 :017 > User.select("id, name").count
   (0.6ms)  SELECT COUNT(id, name) FROM `users`
Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' name) FROM `users`' at line 1: SELECT COUNT(id, name) FROM `users`
ActiveRecord::StatementInvalid: Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' name) FROM `users`' at line 1: SELECT COUNT(id, name) FROM `users`

当 select 里面只有一个的时候

2.1.2 :021 >   User.select("id").count
   (0.6ms)  SELECT COUNT(`users`.`id`) FROM `users`
 => 2 
2.1.2 :022 >

这就没问题，我想，不应该是多了子查询而发生错误！

ane #3 2014年08月07日

你把 user_money 去掉，保证没事。sql 里哪有 count 中放其他 sum 的

ane #4 2014年08月07日

而且我没见过使用 select count(id,name...) from table 这样的 sql 这叫什么，汇总 count(id)+count(name)?

ane #5 2014年08月07日

select count(user.*) , ( SELECT id, name, (SELECT SUM(money) FROM accounts WHERE (accounts.user_id = users.id) GROUP BY user_id) AS user_money FROM users) as user_account where user.id =user_account.id

需要 登录 后方可回复, 如果你还没有账号请 注册新账号