sql_helper.rb 4.6 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203
  1. # Copyright (C) 2012-2022 Zammad Foundation, https://zammad-foundation.org/
  2. class SqlHelper
  3. def initialize(object:)
  4. @object = object
  5. end
  6. def db_column(column)
  7. "#{ActiveRecord::Base.connection.quote_table_name(@object.table_name)}.#{ActiveRecord::Base.connection.quote_column_name(column)}"
  8. end
  9. def db_value(value)
  10. ActiveRecord::Base.connection.quote_string(value)
  11. end
  12. def get_param_key(key, params)
  13. sort_by = []
  14. if params[key].present? && params[key].is_a?(String)
  15. params[key] = params[key].split(%r{\s*,\s*})
  16. elsif params[key].blank?
  17. params[key] = []
  18. end
  19. sort_by
  20. end
  21. =begin
  22. This function will check the params for the "sort_by" attribute
  23. and validate its values.
  24. sql_helper = SqlHelper.new(object: Ticket)
  25. sort_by = sql_helper.get_sort_by(params, default)
  26. returns
  27. sort_by = [
  28. 'created_at',
  29. 'updated_at',
  30. ]
  31. =end
  32. def get_sort_by(params, default = nil)
  33. sort_by = get_param_key(:sort_by, params)
  34. # check order
  35. params[:sort_by].each do |value|
  36. # only accept values which are set for the db schema
  37. raise "Found invalid column '#{value}' for sorting." if @object.columns_hash[value].blank?
  38. sort_by.push(value)
  39. end
  40. if sort_by.blank? && default.present?
  41. if default.is_a?(Array)
  42. sort_by = default
  43. else
  44. sort_by.push(default)
  45. end
  46. end
  47. sort_by
  48. end
  49. =begin
  50. This function will check the params for the "order_by" attribute
  51. and validate its values.
  52. sql_helper = SqlHelper.new(object: Ticket)
  53. order_by = sql_helper.get_order_by(params, default)
  54. returns
  55. order_by = [
  56. 'asc',
  57. 'desc',
  58. ]
  59. =end
  60. def get_order_by(params, default = nil)
  61. order_by = get_param_key(:order_by, params)
  62. # check order
  63. params[:order_by].each do |value|
  64. raise "Found invalid order by value #{value}. Please use 'asc' or 'desc'." if !value.match?(%r{\A(asc|desc)\z}i)
  65. order_by.push(value.downcase)
  66. end
  67. if order_by.blank? && default.present?
  68. if default.is_a?(Array)
  69. order_by = default
  70. else
  71. order_by.push(default)
  72. end
  73. end
  74. order_by
  75. end
  76. def set_sql_order_default(sql, default)
  77. if sql.blank? && default.present?
  78. sql.push(db_column(default))
  79. end
  80. sql
  81. end
  82. =begin
  83. This function will use the evaluated values for sort_by and
  84. order_by to generate the ORDER-SELECT sql statement for the sorting
  85. of the result.
  86. sort_by = [ 'created_at', 'updated_at' ]
  87. order_by = [ 'asc', 'desc' ]
  88. default = 'tickets.created_at'
  89. sql_helper = SqlHelper.new(object: Ticket)
  90. sql = sql_helper.get_order_select(sort_by, order_by, default)
  91. returns
  92. sql = 'tickets.created_at, tickets.updated_at'
  93. =end
  94. def get_order_select(sort_by, order_by, default = nil)
  95. sql = []
  96. sort_by.each_with_index do |value, index|
  97. next if value.blank?
  98. next if order_by[index].blank?
  99. sql.push(db_column(value))
  100. end
  101. sql = set_sql_order_default(sql, default)
  102. sql.join(', ')
  103. end
  104. =begin
  105. This function will use the evaluated values for sort_by and
  106. order_by to generate the ORDER- sql statement for the sorting
  107. of the result.
  108. sort_by = [ 'created_at', 'updated_at' ]
  109. order_by = [ 'asc', 'desc' ]
  110. default = 'tickets.created_at DESC'
  111. sql_helper = SqlHelper.new(object: Ticket)
  112. sql = sql_helper.get_order(sort_by, order_by, default)
  113. returns
  114. sql = 'tickets.created_at ASC, tickets.updated_at DESC'
  115. =end
  116. def get_order(sort_by, order_by, default = nil)
  117. sql = []
  118. sort_by.each_with_index do |value, index|
  119. next if value.blank?
  120. next if order_by[index].blank?
  121. sql.push("#{db_column(value)} #{order_by[index]}")
  122. end
  123. sql = set_sql_order_default(sql, default)
  124. sql.join(', ')
  125. end
  126. def array_contains_all(attribute, value, negated: false)
  127. value = [''] if value.blank?
  128. value = Array(value)
  129. result = if Rails.application.config.db_column_array
  130. "(#{db_column(attribute)} @> ARRAY[#{value.map { |v| "'#{db_value(v)}'" }.join(',')}]::varchar[])"
  131. else
  132. "JSON_CONTAINS(#{db_column(attribute)}, '#{db_value(value.to_json)}', '$')"
  133. end
  134. negated ? "NOT(#{result})" : "(#{result})"
  135. end
  136. def array_contains_one(attribute, value, negated: false)
  137. value = [''] if value.blank?
  138. value = Array(value)
  139. result = if Rails.application.config.db_column_array
  140. "(#{db_column(attribute)} && ARRAY[#{value.map { |v| "'#{db_value(v)}'" }.join(',')}]::varchar[])"
  141. else
  142. value.map { |v| "JSON_CONTAINS(#{db_column(attribute)}, '#{db_value(v.to_json)}', '$')" }.join(' OR ')
  143. end
  144. negated ? "NOT(#{result})" : "(#{result})"
  145. end
  146. end