mysql - ActiveRecord SQL 语句未返回正确的数据

Question

我的 Ruby on Rails 应用程序(Ruby 2.1、Rails 4.0.2)中的以下代码返回

a = Order.select('if(currency1_id=3,unitprice,1/unitprice) as uu,sum(if(currency1_id=3,open_quantity,open_quantity*unitprice)) as qq').where("((currency1_id=? and currency2_id=? and otype = 0) or (otype = 1 and currency1_id=? and currency2_id=?))",c1.id,c2.id,c2.id,c1.id).group('uu').order('uu desc').limit(20)
=> #<ActiveRecord::Relation [#<Order id: nil>, #<Order id: nil>, #<Order id: nil>, #<Order id: nil>, #<Order id: nil>, #<Order id: nil>, #<Order id: nil>, #<Order id: nil>, #<Order id: nil>, #<Order id: nil>, ...]> 

所以我跑了

a.to_sql

这给了我

=> "SELECT  if(currency1_id=3,unitprice,1/unitprice) as uu,sum(if(currency1_id=3,open_quantity,open_quantity*unitprice)) as qq FROM `orders`  WHERE (((currency1_id=3 and currency2_id=1 and otype = 0) or (otype = 1 and currency1_id=1 and currency2_id=3))) GROUP BY uu  ORDER BY uu desc LIMIT 20" 

我使用该 SQL 语句并直接在 MySQL 中运行它，令我惊讶的是，它返回的数据正是我期望的数据，这与 Rails 不同。

mysql> SELECT  if(currency1_id=3,unitprice,1/unitprice) as uu,sum(if(currency1_id=3,open_quantity,open_quantity*unitprice)) as qq FROM `orders`  WHERE (((currency1_id=3 and currency2_id=1 and otype = 0) or (otype = 1 and currency1_id=1 and currency2_id=3))) GROUP BY uu  ORDER BY uu desc LIMIT 20;

    +------------+----------------------+
    | uu         | qq                   |
    +------------+----------------------+
    | 0.02638201 |   0.2751620500000000 |
    | 0.02638200 |   0.5000000000000000 |
    | 0.02616701 |  13.7539900000000000 |
    | 0.02616700 |   1.0000000000000000 |
    | 0.02610030 |   0.3421014700000000 |
    | 0.02610000 |   2.0000000000000000 |
    | 0.02600000 |  10.1530000000000000 |
    | 0.02597364 |   7.9000000000000000 |
    | 0.02596814 |   0.2747080000000000 |
    | 0.02591992 |   0.3999690000000000 |
    | 0.02591991 |   1.9183083000000000 |
    | 0.02591900 |  11.0000000000000000 |
    | 0.02591002 |  90.0000000000000000 |
    | 0.02591001 |   0.3667208155574714 |
    | 0.02551036 |   1.0000000000000000 |
    | 0.02550001 |  42.0000000000000000 |
    | 0.02550000 | 108.0606277900000000 |
    | 0.02540107 |   3.0000000000000000 |
    | 0.02540000 |   0.0500000000000000 |
    | 0.02520000 |  10.0000000000000000 |
    +------------+----------------------+
    20 rows in set (0.00 sec)

为什么 ActiveRecord 没有返回预期结果？错误还是限制？

Answer 1

ActiveRecord 正是按照您的要求执行的。它从数据库中获取与使用 SQL 时相同的数据。唯一的区别在于它返回的方式。

运行 ActiveRecord 查询后尝试以下操作

a.map {|order| [order.uu, order.qq] }

这应该给你一个数组数组，代表 SQL 中的值

[[0.02638201, 0.2751620500000000],
 [0.02638200, 0.5000000000000000],
 [0.02616701, 13.7539900000000000],
 .....]

ActiveRecord 基本上返回一个仅包含 uu 和 pp 属性的 Orders 数组。确切的解释在文档中 http://guides.rubyonrails.org/active_record_querying.html#selecting-specific-fields