Class: QueryHelper

Inherits:
Object
  • Object
show all
Defined in:
lib/query_helper.rb,
lib/query_helper/filter.rb,
lib/query_helper/version.rb,
lib/query_helper/sql_sort.rb,
lib/query_helper/column_map.rb,
lib/query_helper/sql_filter.rb,
lib/query_helper/sql_parser.rb,
lib/query_helper/associations.rb,
lib/query_helper/sql_manipulator.rb,
lib/query_helper/invalid_query_error.rb,
lib/query_helper/query_helper_concern.rb

Defined Under Namespace

Modules: QueryHelperConcern Classes: Associations, ColumnMap, Filter, InvalidQueryError, SqlFilter, SqlManipulator, SqlParser, SqlSort

Constant Summary collapse

VERSION = 
"0.2.21"

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(model: nil, query: nil, bind_variables: {}, sql_filter: SqlFilter.new(), sql_sort: SqlSort.new(), page: nil, per_page: nil, single_record: false, associations: [], as_json_options: nil, custom_mappings: {}, api_payload: false, preload: [], search_fields: [], search_string: nil, metadata: {}) ⇒ QueryHelper

Returns a new instance of QueryHelper.



19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
 
# File 'lib/query_helper.rb', line 19

def initialize(
  model: nil, # the model to run the query against
  query: nil, # a sql string or an active record query
  bind_variables: {}, # a list of bind variables to be embedded into the query
  sql_filter: SqlFilter.new(), # a SqlFilter object
  sql_sort: SqlSort.new(), # a SqlSort object
  page: nil, # define the page you want returned
  per_page: nil, # define how many results you want per page
  single_record: false, # whether or not you expect the record to return a single result, if toggled, only the first result will be returned
  associations: [], # a list of activerecord associations you'd like included in the payload
  as_json_options: nil, # a list of as_json options you'd like run before returning the payload
  custom_mappings: {}, # custom keyword => sql_expression mappings
  api_payload: false, # Return the paginated payload or simply return the result array
  preload: [], # preload activerecord associations - used instead of `associations` when you don't want them included in the payload
  search_fields: [],
  search_string: nil,
  metadata: {}
)
  @query = query.class < ActiveRecord::Relation ? query.to_sql : query
  @model = query.class < ActiveRecord::Relation ? query.base_class : model
  @bind_variables = bind_variables
  @sql_filter = sql_filter
  @sql_sort = sql_sort
  @page = determine_page(page: page, per_page: per_page)
  @per_page = determine_per_page(page: page, per_page: per_page)
  set_limit_and_offset()
  @single_record = single_record
  @associations = associations
  @as_json_options = as_json_options
  @custom_mappings = custom_mappings
  @api_payload = api_payload
  @preload = preload
  @search_fields = search_fields
  @search_string = search_string
  @metadata = metadata
end

Instance Attribute Details

#api_payload ⇒ Object

Returns the value of attribute api_payload.



16
17
18
 
# File 'lib/query_helper.rb', line 16

def api_payload
  @api_payload
end

#as_json_options ⇒ Object

Returns the value of attribute as_json_options.



16
17
18
 
# File 'lib/query_helper.rb', line 16

def as_json_options
  @as_json_options
end

#associations ⇒ Object

Returns the value of attribute associations.



16
17
18
 
# File 'lib/query_helper.rb', line 16

def associations
  @associations
end

#bind_variables ⇒ Object

Returns the value of attribute bind_variables.



16
17
18
 
# File 'lib/query_helper.rb', line 16

def bind_variables
  @bind_variables
end

#executed_query ⇒ Object

Returns the value of attribute executed_query.



16
17
18
 
# File 'lib/query_helper.rb', line 16

def executed_query
  @executed_query
end

#metadata ⇒ Object

Returns the value of attribute metadata.



16
17
18
 
# File 'lib/query_helper.rb', line 16

def metadata
  @metadata
end

#model ⇒ Object

Returns the value of attribute model.



16
17
18
 
# File 'lib/query_helper.rb', line 16

def model
  @model
end

#page ⇒ Object

Returns the value of attribute page.



16
17
18
 
# File 'lib/query_helper.rb', line 16

def page
  @page
end

#per_page ⇒ Object

Returns the value of attribute per_page.



16
17
18
 
# File 'lib/query_helper.rb', line 16

def per_page
  @per_page
end

#preload ⇒ Object

Returns the value of attribute preload.



16
17
18
 
# File 'lib/query_helper.rb', line 16

def preload
  @preload
end

#query ⇒ Object

Returns the value of attribute query.



17
18
19
 
# File 'lib/query_helper.rb', line 17

def query
  @query
end

#search_field ⇒ Object

Returns the value of attribute search_field.



16
17
18
 
# File 'lib/query_helper.rb', line 16

def search_field
  @search_field
end

#search_string ⇒ Object

Returns the value of attribute search_string.



16
17
18
 
# File 'lib/query_helper.rb', line 16

def search_string
  @search_string
end

#single_record ⇒ Object

Returns the value of attribute single_record.



16
17
18
 
# File 'lib/query_helper.rb', line 16

def single_record
  @single_record
end

#sql_filter ⇒ Object

Returns the value of attribute sql_filter.



16
17
18
 
# File 'lib/query_helper.rb', line 16

def sql_filter
  @sql_filter
end

#sql_sort ⇒ Object

Returns the value of attribute sql_sort.



16
17
18
 
# File 'lib/query_helper.rb', line 16

def sql_sort
  @sql_sort
end

Instance Method Details

#add_filter(operator_code:, criterion:, comparate:) ⇒ Object 



96
97
98
 
# File 'lib/query_helper.rb', line 96

def add_filter(operator_code:, criterion:, comparate:)
  @sql_filter.filter_values["comparate"] = { operator_code => criterion }
end

#build_query ⇒ Object 



110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
 
# File 'lib/query_helper.rb', line 110

def build_query
  # Create column maps to be used by the filter and sort objects
  column_maps = create_column_maps()

  @sql_filter.column_maps = column_maps
  @sql_sort.column_maps = column_maps

  # create the filters from the column maps
  @sql_filter.create_filters()

  having_clauses = @sql_filter.having_clauses
  where_clauses = @sql_filter.where_clauses

  if @search_string
    search_filter = search_filter(column_maps)
    if search_filter[:placement] == :where
      where_clauses << search_filter[:filter]
    else
      having_clauses << search_filter[:filter]
    end
  end


  # merge the filter bind variables into the query bind variables
  @bind_variables.merge!(@sql_filter.bind_variables)

  # Execute Sql Query
  manipulator = SqlManipulator.new(
    sql: @query,
    where_clauses: where_clauses,
    having_clauses: having_clauses,
    order_by_clauses: @sql_sort.parse_sort_string,
    include_limit_clause: @page && @per_page ? true : false,
    additional_select_clauses:  @sql_sort.select_strings
  )
  manipulator.build()
end

#execute_query ⇒ Object 



167
168
169
170
171
172
173
174
175
176
 
# File 'lib/query_helper.rb', line 167

def execute_query
  query = build_query()
  @results = @model.find_by_sql([query, @bind_variables]) # Execute Sql Query
  @results = @results.first if @single_record # Return a single result if requested

  determine_count()
  preload_associations()
  load_associations()
  clean_results()
end

#pagination_results(count = @count) ⇒ Object 



184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
 
# File 'lib/query_helper.rb', line 184

def pagination_results(count=@count)
  # Set pagination params if they aren't provided
  results_per_page = @per_page || count
  results_page = @page || 1

  total_pages = (count.to_i/(results_per_page.nonzero? || 1).to_f).ceil
  next_page = results_page + 1 if results_page.between?(1, total_pages - 1)
  previous_page = results_page - 1 if results_page.between?(2, total_pages)
  first_page = results_page == 1
  last_page = results_page >= total_pages
  out_of_range = !results_page.between?(1,total_pages)

  { count: count,
    current_page: results_page,
    next_page: next_page,
    previous_page: previous_page,
    total_pages: total_pages,
    per_page: results_per_page,
    first_page: first_page,
    last_page: last_page,
    out_of_range: out_of_range }
end

#results ⇒ Object 



178
179
180
181
182
 
# File 'lib/query_helper.rb', line 178

def results
  execute_query()
  return paginated_results() if @api_payload
  return @results
end

#to_json(args) ⇒ Object 



148
149
150
 
# File 'lib/query_helper.rb', line 148

def to_json(args)
  results.to_json
end

#to_sql ⇒ Object 



152
153
154
155
156
157
158
159
160
161
 
# File 'lib/query_helper.rb', line 152

def to_sql
  query = build_query()
  return query if @bind_variables.length == 0
  begin
    return @model.sanitize_sql_array([query, @bind_variables])
  rescue NoMethodError
    # sanitize_sql_array is a protected method before Rails v5.2.3
    return @model.send(:sanitize_sql_array, [query, @bind_variables])
  end
end

#update(query: nil, model: nil, bind_variables: {}, filters: [], associations: [], as_json_options: nil, single_record: nil, custom_mappings: nil, preload: [], search_fields: nil, sql_filter: nil, sql_sort: nil, sort_tiebreak: nil, page: nil, per_page: nil, search_string: nil, metadata: nil) ⇒ Object 



56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
 
# File 'lib/query_helper.rb', line 56

def update(
  query: nil,
  model: nil,
  bind_variables: {},
  filters: [],
  associations: [],
  as_json_options: nil,
  single_record: nil,
  custom_mappings: nil,
  preload: [],
  search_fields: nil,
  sql_filter: nil,
  sql_sort: nil,
  sort_tiebreak: nil,
  page: nil,
  per_page: nil,
  search_string: nil,
  metadata: nil
)
  @query = query.class < ActiveRecord::Relation ? query.to_sql : query if query
  @model = query.class < ActiveRecord::Relation ? query.base_class : model if model || query
  @bind_variables.merge!(bind_variables)
  filters.each{ |f| add_filter(**f) }
  @associations = @associations | associations
  @single_record = single_record if single_record
  @as_json_options = as_json_options if as_json_options
  @custom_mappings = custom_mappings if custom_mappings
  @preload = preload if preload
  @search_fields = search_fields if search_fields
  @sql_filter = sql_filter if sql_filter
  @sql_sort = sql_sort if sql_sort
  @sql_sort.sort_tiebreak = sort_tiebreak if sort_tiebreak
  @search_string = search_string if search_string
  @page = determine_page(page: page, per_page: per_page) if page
  @per_page = determine_per_page(page: page, per_page: per_page) if per_page
  @metadata = metadata if metadata
  set_limit_and_offset()
  return self
end

#view_query ⇒ Object 



163
164
165
 
# File 'lib/query_helper.rb', line 163

def view_query
  to_sql
end