PageRenderTime 47ms CodeModel.GetById 16ms RepoModel.GetById 1ms app.codeStats 0ms

/activerecord/test/cases/calculations_test.rb

https://bitbucket.org/mirror/rails
Ruby | 566 lines | 452 code | 108 blank | 6 comment | 8 complexity | 321cfa7a24fc9e9060179a4716300ac6 MD5 | raw file
  1. require "cases/helper"
  2. require 'models/club'
  3. require 'models/company'
  4. require "models/contract"
  5. require 'models/edge'
  6. require 'models/organization'
  7. require 'models/possession'
  8. require 'models/topic'
  9. require 'models/minivan'
  10. require 'models/speedometer'
  11. require 'models/ship_part'
  12. Company.has_many :accounts
  13. class NumericData < ActiveRecord::Base
  14. self.table_name = 'numeric_data'
  15. end
  16. class CalculationsTest < ActiveRecord::TestCase
  17. fixtures :companies, :accounts, :topics
  18. def test_should_sum_field
  19. assert_equal 318, Account.sum(:credit_limit)
  20. end
  21. def test_should_average_field
  22. value = Account.average(:credit_limit)
  23. assert_equal 53.0, value
  24. end
  25. def test_should_return_decimal_average_of_integer_field
  26. value = Account.average(:id)
  27. assert_equal 3.5, value
  28. end
  29. def test_should_return_integer_average_if_db_returns_such
  30. ShipPart.delete_all
  31. ShipPart.create!(:id => 3, :name => 'foo')
  32. value = ShipPart.average(:id)
  33. assert_equal 3, value
  34. end
  35. def test_should_return_nil_as_average
  36. assert_nil NumericData.average(:bank_balance)
  37. end
  38. def test_type_cast_calculated_value_should_convert_db_averages_of_fixnum_class_to_decimal
  39. assert_equal 0, NumericData.all.send(:type_cast_calculated_value, 0, nil, 'avg')
  40. assert_equal 53.0, NumericData.all.send(:type_cast_calculated_value, 53, nil, 'avg')
  41. end
  42. def test_should_get_maximum_of_field
  43. assert_equal 60, Account.maximum(:credit_limit)
  44. end
  45. def test_should_get_maximum_of_field_with_include
  46. assert_equal 55, Account.where("companies.name != 'Summit'").references(:companies).includes(:firm).maximum(:credit_limit)
  47. end
  48. def test_should_get_minimum_of_field
  49. assert_equal 50, Account.minimum(:credit_limit)
  50. end
  51. def test_should_group_by_field
  52. c = Account.group(:firm_id).sum(:credit_limit)
  53. [1,6,2].each do |firm_id|
  54. assert c.keys.include?(firm_id), "Group #{c.inspect} does not contain firm_id #{firm_id}"
  55. end
  56. end
  57. def test_should_group_by_arel_attribute
  58. c = Account.group(Account.arel_table[:firm_id]).sum(:credit_limit)
  59. [1,6,2].each do |firm_id|
  60. assert c.keys.include?(firm_id), "Group #{c.inspect} does not contain firm_id #{firm_id}"
  61. end
  62. end
  63. def test_should_group_by_multiple_fields
  64. c = Account.group('firm_id', :credit_limit).count(:all)
  65. [ [nil, 50], [1, 50], [6, 50], [6, 55], [9, 53], [2, 60] ].each { |firm_and_limit| assert c.keys.include?(firm_and_limit) }
  66. end
  67. def test_should_group_by_multiple_fields_having_functions
  68. c = Topic.group(:author_name, 'COALESCE(type, title)').count(:all)
  69. assert_equal 1, c[["Carl", "The Third Topic of the day"]]
  70. assert_equal 1, c[["Mary", "Reply"]]
  71. assert_equal 1, c[["David", "The First Topic"]]
  72. assert_equal 1, c[["Carl", "Reply"]]
  73. end
  74. def test_should_group_by_summed_field
  75. c = Account.group(:firm_id).sum(:credit_limit)
  76. assert_equal 50, c[1]
  77. assert_equal 105, c[6]
  78. assert_equal 60, c[2]
  79. end
  80. def test_should_order_by_grouped_field
  81. c = Account.all.merge!(:group => :firm_id, :order => "firm_id").sum(:credit_limit)
  82. assert_equal [1, 2, 6, 9], c.keys.compact
  83. end
  84. def test_should_order_by_calculation
  85. c = Account.all.merge!(:group => :firm_id, :order => "sum_credit_limit desc, firm_id").sum(:credit_limit)
  86. assert_equal [105, 60, 53, 50, 50], c.keys.collect { |k| c[k] }
  87. assert_equal [6, 2, 9, 1], c.keys.compact
  88. end
  89. def test_should_limit_calculation
  90. c = Account.all.merge!(:where => "firm_id IS NOT NULL",
  91. :group => :firm_id, :order => "firm_id", :limit => 2).sum(:credit_limit)
  92. assert_equal [1, 2], c.keys.compact
  93. end
  94. def test_should_limit_calculation_with_offset
  95. c = Account.all.merge!(:where => "firm_id IS NOT NULL", :group => :firm_id,
  96. :order => "firm_id", :limit => 2, :offset => 1).sum(:credit_limit)
  97. assert_equal [2, 6], c.keys.compact
  98. end
  99. def test_limit_should_apply_before_count
  100. accounts = Account.limit(3).where('firm_id IS NOT NULL')
  101. assert_equal 3, accounts.count(:firm_id)
  102. assert_equal 3, accounts.select(:firm_id).count
  103. end
  104. def test_count_should_shortcut_with_limit_zero
  105. accounts = Account.limit(0)
  106. assert_no_queries { assert_equal 0, accounts.count }
  107. end
  108. def test_limit_is_kept
  109. return if current_adapter?(:OracleAdapter)
  110. queries = assert_sql { Account.limit(1).count }
  111. assert_equal 1, queries.length
  112. assert_match(/LIMIT/, queries.first)
  113. end
  114. def test_offset_is_kept
  115. return if current_adapter?(:OracleAdapter)
  116. queries = assert_sql { Account.offset(1).count }
  117. assert_equal 1, queries.length
  118. assert_match(/OFFSET/, queries.first)
  119. end
  120. def test_limit_with_offset_is_kept
  121. return if current_adapter?(:OracleAdapter)
  122. queries = assert_sql { Account.limit(1).offset(1).count }
  123. assert_equal 1, queries.length
  124. assert_match(/LIMIT/, queries.first)
  125. assert_match(/OFFSET/, queries.first)
  126. end
  127. def test_no_limit_no_offset
  128. queries = assert_sql { Account.count }
  129. assert_equal 1, queries.length
  130. assert_no_match(/LIMIT/, queries.first)
  131. assert_no_match(/OFFSET/, queries.first)
  132. end
  133. def test_should_group_by_summed_field_having_condition
  134. c = Account.all.merge!(:group => :firm_id,
  135. :having => 'sum(credit_limit) > 50').sum(:credit_limit)
  136. assert_nil c[1]
  137. assert_equal 105, c[6]
  138. assert_equal 60, c[2]
  139. end
  140. def test_should_group_by_summed_field_having_condition_from_select
  141. c = Account.select("MIN(credit_limit) AS min_credit_limit").group(:firm_id).having("MIN(credit_limit) > 50").sum(:credit_limit)
  142. assert_nil c[1]
  143. assert_equal 60, c[2]
  144. assert_equal 53, c[9]
  145. end
  146. def test_should_group_by_summed_association
  147. c = Account.group(:firm).sum(:credit_limit)
  148. assert_equal 50, c[companies(:first_firm)]
  149. assert_equal 105, c[companies(:rails_core)]
  150. assert_equal 60, c[companies(:first_client)]
  151. end
  152. def test_should_sum_field_with_conditions
  153. assert_equal 105, Account.where('firm_id = 6').sum(:credit_limit)
  154. end
  155. def test_should_return_zero_if_sum_conditions_return_nothing
  156. assert_equal 0, Account.where('1 = 2').sum(:credit_limit)
  157. assert_equal 0, companies(:rails_core).companies.where('1 = 2').sum(:id)
  158. end
  159. def test_sum_should_return_valid_values_for_decimals
  160. NumericData.create(:bank_balance => 19.83)
  161. assert_equal 19.83, NumericData.sum(:bank_balance)
  162. end
  163. def test_should_group_by_summed_field_with_conditions
  164. c = Account.all.merge!(:where => 'firm_id > 1',
  165. :group => :firm_id).sum(:credit_limit)
  166. assert_nil c[1]
  167. assert_equal 105, c[6]
  168. assert_equal 60, c[2]
  169. end
  170. def test_should_group_by_summed_field_with_conditions_and_having
  171. c = Account.all.merge!(:where => 'firm_id > 1',
  172. :group => :firm_id,
  173. :having => 'sum(credit_limit) > 60').sum(:credit_limit)
  174. assert_nil c[1]
  175. assert_equal 105, c[6]
  176. assert_nil c[2]
  177. end
  178. def test_should_group_by_fields_with_table_alias
  179. c = Account.group('accounts.firm_id').sum(:credit_limit)
  180. assert_equal 50, c[1]
  181. assert_equal 105, c[6]
  182. assert_equal 60, c[2]
  183. end
  184. def test_should_calculate_with_invalid_field
  185. assert_equal 6, Account.calculate(:count, '*')
  186. assert_equal 6, Account.calculate(:count, :all)
  187. end
  188. def test_should_calculate_grouped_with_invalid_field
  189. c = Account.group('accounts.firm_id').count(:all)
  190. assert_equal 1, c[1]
  191. assert_equal 2, c[6]
  192. assert_equal 1, c[2]
  193. end
  194. def test_should_calculate_grouped_association_with_invalid_field
  195. c = Account.group(:firm).count(:all)
  196. assert_equal 1, c[companies(:first_firm)]
  197. assert_equal 2, c[companies(:rails_core)]
  198. assert_equal 1, c[companies(:first_client)]
  199. end
  200. def test_should_group_by_association_with_non_numeric_foreign_key
  201. Speedometer.create! id: 'ABC'
  202. Minivan.create! id: 'OMG', speedometer_id: 'ABC'
  203. c = Minivan.group(:speedometer).count(:all)
  204. first_key = c.keys.first
  205. assert_equal Speedometer, first_key.class
  206. assert_equal 1, c[first_key]
  207. end
  208. def test_should_calculate_grouped_association_with_foreign_key_option
  209. Account.belongs_to :another_firm, :class_name => 'Firm', :foreign_key => 'firm_id'
  210. c = Account.group(:another_firm).count(:all)
  211. assert_equal 1, c[companies(:first_firm)]
  212. assert_equal 2, c[companies(:rails_core)]
  213. assert_equal 1, c[companies(:first_client)]
  214. end
  215. def test_should_calculate_grouped_by_function
  216. c = Company.group("UPPER(#{QUOTED_TYPE})").count(:all)
  217. assert_equal 2, c[nil]
  218. assert_equal 1, c['DEPENDENTFIRM']
  219. assert_equal 4, c['CLIENT']
  220. assert_equal 2, c['FIRM']
  221. end
  222. def test_should_calculate_grouped_by_function_with_table_alias
  223. c = Company.group("UPPER(companies.#{QUOTED_TYPE})").count(:all)
  224. assert_equal 2, c[nil]
  225. assert_equal 1, c['DEPENDENTFIRM']
  226. assert_equal 4, c['CLIENT']
  227. assert_equal 2, c['FIRM']
  228. end
  229. def test_should_not_overshadow_enumerable_sum
  230. assert_equal 6, [1, 2, 3].sum(&:abs)
  231. end
  232. def test_should_sum_scoped_field
  233. assert_equal 15, companies(:rails_core).companies.sum(:id)
  234. end
  235. def test_should_sum_scoped_field_with_from
  236. assert_equal Club.count, Organization.clubs.count
  237. end
  238. def test_should_sum_scoped_field_with_conditions
  239. assert_equal 8, companies(:rails_core).companies.where('id > 7').sum(:id)
  240. end
  241. def test_should_group_by_scoped_field
  242. c = companies(:rails_core).companies.group(:name).sum(:id)
  243. assert_equal 7, c['Leetsoft']
  244. assert_equal 8, c['Jadedpixel']
  245. end
  246. def test_should_group_by_summed_field_through_association_and_having
  247. c = companies(:rails_core).companies.group(:name).having('sum(id) > 7').sum(:id)
  248. assert_nil c['Leetsoft']
  249. assert_equal 8, c['Jadedpixel']
  250. end
  251. def test_should_count_selected_field_with_include
  252. assert_equal 6, Account.includes(:firm).count(:distinct => true)
  253. assert_equal 4, Account.includes(:firm).select(:credit_limit).count(:distinct => true)
  254. end
  255. def test_should_not_perform_joined_include_by_default
  256. assert_equal Account.count, Account.includes(:firm).count
  257. queries = assert_sql { Account.includes(:firm).count }
  258. assert_no_match(/join/i, queries.last)
  259. end
  260. def test_should_perform_joined_include_when_referencing_included_tables
  261. joined_count = Account.includes(:firm).where(:companies => {:name => '37signals'}).count
  262. assert_equal 1, joined_count
  263. end
  264. def test_should_count_scoped_select
  265. Account.update_all("credit_limit = NULL")
  266. assert_equal 0, Account.all.merge!(:select => "credit_limit").count
  267. end
  268. def test_should_count_scoped_select_with_options
  269. Account.update_all("credit_limit = NULL")
  270. Account.last.update_columns('credit_limit' => 49)
  271. Account.first.update_columns('credit_limit' => 51)
  272. assert_equal 1, Account.all.merge!(:select => "credit_limit").where('credit_limit >= 50').count
  273. end
  274. def test_should_count_manual_select_with_include
  275. assert_equal 6, Account.all.merge!(:select => "DISTINCT accounts.id", :includes => :firm).count
  276. end
  277. def test_count_with_column_parameter
  278. assert_equal 5, Account.count(:firm_id)
  279. end
  280. def test_count_with_uniq
  281. assert_equal 4, Account.select(:credit_limit).uniq.count
  282. end
  283. def test_count_with_column_and_options_parameter
  284. assert_equal 2, Account.where("credit_limit = 50 AND firm_id IS NOT NULL").count(:firm_id)
  285. end
  286. def test_should_count_field_in_joined_table
  287. assert_equal 5, Account.joins(:firm).count('companies.id')
  288. assert_equal 4, Account.joins(:firm).count('companies.id', :distinct => true)
  289. end
  290. def test_should_count_field_in_joined_table_with_group_by
  291. c = Account.all.merge!(:group => 'accounts.firm_id', :joins => :firm).count('companies.id')
  292. [1,6,2,9].each { |firm_id| assert c.keys.include?(firm_id) }
  293. end
  294. def test_count_with_no_parameters_isnt_deprecated
  295. assert_not_deprecated { Account.count }
  296. end
  297. def test_count_with_too_many_parameters_raises
  298. assert_raise(ArgumentError) { Account.count(1, 2, 3) }
  299. end
  300. def test_should_sum_expression
  301. # Oracle adapter returns floating point value 636.0 after SUM
  302. if current_adapter?(:OracleAdapter)
  303. assert_equal 636, Account.sum("2 * credit_limit")
  304. else
  305. assert_equal 636, Account.sum("2 * credit_limit").to_i
  306. end
  307. end
  308. def test_sum_expression_returns_zero_when_no_records_to_sum
  309. assert_equal 0, Account.where('1 = 2').sum("2 * credit_limit")
  310. end
  311. def test_count_with_from_option
  312. assert_equal Company.count(:all), Company.from('companies').count(:all)
  313. assert_equal Account.where("credit_limit = 50").count(:all),
  314. Account.from('accounts').where("credit_limit = 50").count(:all)
  315. assert_equal Company.where(:type => "Firm").count(:type),
  316. Company.where(:type => "Firm").from('companies').count(:type)
  317. end
  318. def test_sum_with_from_option
  319. assert_equal Account.sum(:credit_limit), Account.from('accounts').sum(:credit_limit)
  320. assert_equal Account.where("credit_limit > 50").sum(:credit_limit),
  321. Account.where("credit_limit > 50").from('accounts').sum(:credit_limit)
  322. end
  323. def test_sum_array_compatibility_deprecation
  324. assert_deprecated do
  325. assert_equal Account.sum(:credit_limit), Account.sum(&:credit_limit)
  326. end
  327. end
  328. def test_average_with_from_option
  329. assert_equal Account.average(:credit_limit), Account.from('accounts').average(:credit_limit)
  330. assert_equal Account.where("credit_limit > 50").average(:credit_limit),
  331. Account.where("credit_limit > 50").from('accounts').average(:credit_limit)
  332. end
  333. def test_minimum_with_from_option
  334. assert_equal Account.minimum(:credit_limit), Account.from('accounts').minimum(:credit_limit)
  335. assert_equal Account.where("credit_limit > 50").minimum(:credit_limit),
  336. Account.where("credit_limit > 50").from('accounts').minimum(:credit_limit)
  337. end
  338. def test_maximum_with_from_option
  339. assert_equal Account.maximum(:credit_limit), Account.from('accounts').maximum(:credit_limit)
  340. assert_equal Account.where("credit_limit > 50").maximum(:credit_limit),
  341. Account.where("credit_limit > 50").from('accounts').maximum(:credit_limit)
  342. end
  343. def test_maximum_with_not_auto_table_name_prefix_if_column_included
  344. Company.create!(:name => "test", :contracts => [Contract.new(:developer_id => 7)])
  345. assert_equal 7, Company.includes(:contracts).maximum(:developer_id)
  346. end
  347. def test_minimum_with_not_auto_table_name_prefix_if_column_included
  348. Company.create!(:name => "test", :contracts => [Contract.new(:developer_id => 7)])
  349. assert_equal 7, Company.includes(:contracts).minimum(:developer_id)
  350. end
  351. def test_sum_with_not_auto_table_name_prefix_if_column_included
  352. Company.create!(:name => "test", :contracts => [Contract.new(:developer_id => 7)])
  353. assert_equal 7, Company.includes(:contracts).sum(:developer_id)
  354. end
  355. def test_from_option_with_specified_index
  356. if Edge.connection.adapter_name == 'MySQL' or Edge.connection.adapter_name == 'Mysql2'
  357. assert_equal Edge.count(:all), Edge.from('edges USE INDEX(unique_edge_index)').count(:all)
  358. assert_equal Edge.where('sink_id < 5').count(:all),
  359. Edge.from('edges USE INDEX(unique_edge_index)').where('sink_id < 5').count(:all)
  360. end
  361. end
  362. def test_from_option_with_table_different_than_class
  363. assert_equal Account.count(:all), Company.from('accounts').count(:all)
  364. end
  365. def test_distinct_is_honored_when_used_with_count_operation_after_group
  366. # Count the number of authors for approved topics
  367. approved_topics_count = Topic.group(:approved).count(:author_name)[true]
  368. assert_equal approved_topics_count, 3
  369. # Count the number of distinct authors for approved Topics
  370. distinct_authors_for_approved_count = Topic.group(:approved).count(:author_name, :distinct => true)[true]
  371. assert_equal distinct_authors_for_approved_count, 2
  372. end
  373. def test_pluck
  374. assert_equal [1,2,3,4], Topic.order(:id).pluck(:id)
  375. end
  376. def test_pluck_type_cast
  377. topic = topics(:first)
  378. relation = Topic.where(:id => topic.id)
  379. assert_equal [ topic.approved ], relation.pluck(:approved)
  380. assert_equal [ topic.last_read ], relation.pluck(:last_read)
  381. assert_equal [ topic.written_on ], relation.pluck(:written_on)
  382. end
  383. def test_pluck_and_uniq
  384. assert_equal [50, 53, 55, 60], Account.order(:credit_limit).uniq.pluck(:credit_limit)
  385. end
  386. def test_pluck_in_relation
  387. company = Company.first
  388. contract = company.contracts.create!
  389. assert_equal [contract.id], company.contracts.pluck(:id)
  390. end
  391. def test_pluck_with_serialization
  392. t = Topic.create!(:content => { :foo => :bar })
  393. assert_equal [{:foo => :bar}], Topic.where(:id => t.id).pluck(:content)
  394. end
  395. def test_pluck_with_qualified_column_name
  396. assert_equal [1,2,3,4], Topic.order(:id).pluck("topics.id")
  397. end
  398. def test_pluck_auto_table_name_prefix
  399. c = Company.create!(:name => "test", :contracts => [Contract.new])
  400. assert_equal [c.id], Company.joins(:contracts).pluck(:id)
  401. end
  402. def test_pluck_if_table_included
  403. c = Company.create!(:name => "test", :contracts => [Contract.new(:developer_id => 7)])
  404. assert_equal [c.id], Company.includes(:contracts).where("contracts.id" => c.contracts.first).pluck(:id)
  405. end
  406. def test_pluck_not_auto_table_name_prefix_if_column_joined
  407. Company.create!(:name => "test", :contracts => [Contract.new(:developer_id => 7)])
  408. assert_equal [7], Company.joins(:contracts).pluck(:developer_id)
  409. end
  410. def test_pluck_with_selection_clause
  411. assert_equal [50, 53, 55, 60], Account.pluck('DISTINCT credit_limit').sort
  412. assert_equal [50, 53, 55, 60], Account.pluck('DISTINCT accounts.credit_limit').sort
  413. assert_equal [50, 53, 55, 60], Account.pluck('DISTINCT(credit_limit)').sort
  414. # MySQL returns "SUM(DISTINCT(credit_limit))" as the column name unless
  415. # an alias is provided. Without the alias, the column cannot be found
  416. # and properly typecast.
  417. assert_equal [50 + 53 + 55 + 60], Account.pluck('SUM(DISTINCT(credit_limit)) as credit_limit')
  418. end
  419. def test_plucks_with_ids
  420. assert_equal Company.all.map(&:id).sort, Company.ids.sort
  421. end
  422. def test_pluck_not_auto_table_name_prefix_if_column_included
  423. Company.create!(:name => "test", :contracts => [Contract.new(:developer_id => 7)])
  424. ids = Company.includes(:contracts).pluck(:developer_id)
  425. assert_equal Company.count, ids.length
  426. assert_equal [7], ids.compact
  427. end
  428. def test_pluck_multiple_columns
  429. assert_equal [
  430. [1, "The First Topic"], [2, "The Second Topic of the day"],
  431. [3, "The Third Topic of the day"], [4, "The Fourth Topic of the day"]
  432. ], Topic.order(:id).pluck(:id, :title)
  433. assert_equal [
  434. [1, "The First Topic", "David"], [2, "The Second Topic of the day", "Mary"],
  435. [3, "The Third Topic of the day", "Carl"], [4, "The Fourth Topic of the day", "Carl"]
  436. ], Topic.order(:id).pluck(:id, :title, :author_name)
  437. end
  438. def test_pluck_with_multiple_columns_and_selection_clause
  439. assert_equal [[1, 50], [2, 50], [3, 50], [4, 60], [5, 55], [6, 53]],
  440. Account.pluck('id, credit_limit')
  441. end
  442. def test_pluck_with_multiple_columns_and_includes
  443. Company.create!(:name => "test", :contracts => [Contract.new(:developer_id => 7)])
  444. companies_and_developers = Company.order('companies.id').includes(:contracts).pluck(:name, :developer_id)
  445. assert_equal Company.count, companies_and_developers.length
  446. assert_equal ["37signals", nil], companies_and_developers.first
  447. assert_equal ["test", 7], companies_and_developers.last
  448. end
  449. def test_pluck_with_reserved_words
  450. Possession.create!(:where => "Over There")
  451. assert_equal ["Over There"], Possession.pluck(:where)
  452. end
  453. def test_pluck_replaces_select_clause
  454. taks_relation = Topic.select(:approved, :id).order(:id)
  455. assert_equal [1,2,3,4], taks_relation.pluck(:id)
  456. assert_equal [false, true, true, true], taks_relation.pluck(:approved)
  457. end
  458. end