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

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

  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 



81
82
83
 
# File 'lib/query_helper.rb', line 81

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

#build_query ⇒ Object 



95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
 
# File 'lib/query_helper.rb', line 95

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()

  # 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: @sql_filter.where_clauses,
    having_clauses:  @sql_filter.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 



139
140
141
142
143
144
145
146
147
148
 
# File 'lib/query_helper.rb', line 139

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 



150
151
152
153
154
 
# File 'lib/query_helper.rb', line 150

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

#to_json(args) ⇒ Object 



120
121
122
 
# File 'lib/query_helper.rb', line 120

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

#to_sql ⇒ Object 



124
125
126
127
128
129
130
131
132
133
 
# File 'lib/query_helper.rb', line 124

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: []) ⇒ Object 



58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
 
# File 'lib/query_helper.rb', line 58

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

#view_query ⇒ Object 



135
136
137
 
# File 'lib/query_helper.rb', line 135

def view_query
  to_sql
end