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.11"

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) ⇒ 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
 
# 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
)
  @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
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

#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 



90
91
92
 
# File 'lib/query_helper.rb', line 90

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

#build_query ⇒ Object 



104
105
106
107
108
109
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
 
# File 'lib/query_helper.rb', line 104

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 



161
162
163
164
165
166
167
168
169
170
 
# File 'lib/query_helper.rb', line 161

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

#results ⇒ Object 



172
173
174
175
176
 
# File 'lib/query_helper.rb', line 172

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

#to_json(args) ⇒ Object 



142
143
144
 
# File 'lib/query_helper.rb', line 142

def to_json(args)
  JSON.generate(results)
end

#to_sql ⇒ Object 



146
147
148
149
150
151
152
153
154
155
 
# File 'lib/query_helper.rb', line 146

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, page: nil, per_page: nil, search_string: nil) ⇒ Object 



54
55
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
 
# File 'lib/query_helper.rb', line 54

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,
  page: nil,
  per_page: nil,
  search_string: 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 
  @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
  set_limit_and_offset()
  return self
end

#view_query ⇒ Object 



157
158
159
 
# File 'lib/query_helper.rb', line 157

def view_query
  to_sql
end