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

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

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
55
56
57
58
59
60
 
# 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)
  @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

  if @page && @per_page
    # Determine limit and offset
    limit = @per_page
    offset = (@page - 1) * @per_page

    # Merge limit/offset variables into bind_variables
    @bind_variables.merge!({limit: limit, offset: offset})
  end
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

#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 



87
88
89
 
# File 'lib/query_helper.rb', line 87

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

#build_query ⇒ Object 



101
102
103
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
 
# File 'lib/query_helper.rb', line 101

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 



158
159
160
161
162
163
164
165
166
167
 
# File 'lib/query_helper.rb', line 158

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 



169
170
171
172
173
 
# File 'lib/query_helper.rb', line 169

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

#to_json(args) ⇒ Object 



139
140
141
 
# File 'lib/query_helper.rb', line 139

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

#to_sql ⇒ Object 



143
144
145
146
147
148
149
150
151
152
 
# File 'lib/query_helper.rb', line 143

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) ⇒ Object 



62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
 
# File 'lib/query_helper.rb', line 62

def update(
  query: nil,
  model: nil,
  bind_variables: {},
  filters: [],
  associations: [],
  as_json_options: nil,
  single_record: nil,
  custom_mappings: nil,
  preload: [],
  search_fields: 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 
  return self
end

#view_query ⇒ Object 



154
155
156
 
# File 'lib/query_helper.rb', line 154

def view_query
  to_sql
end