PageRenderTime 52ms CodeModel.GetById 25ms RepoModel.GetById 1ms app.codeStats 0ms

/activerecord/test/cases/calculations_test.rb

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