CODEWITHSUNDEEP
mysqlruby-on-railsactiverecord
KKK
KKK

Reputation: 1085

ActiveRecord SQL statement not returning the correct data

The following code in my Ruby on Rails application (Ruby 2.1, Rails 4.0.2) returns 

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>, ...]>

so I run

a.to_sql

which gives me

=> "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"

I take that SQL statement and run it in MySQL directly, and to my surprise it reeturns exactly the data I am expecting it to return, unlike 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)

Why is ActiveRecord not returning the expected results? Bug or a limitation?

Upvotes: 0

Views: 52

Answers (1)

Christian Sommerauer
Christian Sommerauer

Reputation: 431

ActiveRecord is exactly doing what you are asking it to do. It fetches the same data from the database as when you are using SQL. The only difference is how it returns it.

try the following with a after runnning your ActiveRecord query

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

this should give you an array of arrays, representing the values as in SQL

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

ActiveRecord basically returns an array of Orders with only uu and pp attributes. The exact explanation is in the docs http://guides.rubyonrails.org/active_record_querying.html#selecting-specific-fields

Upvotes: 1

Related Questions