class QueryHelper
Constants
- VERSION
Attributes
api_payload[RW]
as_json_options[RW]
associations[RW]
bind_variables[RW]
executed_query[RW]
metadata[RW]
model[RW]
page[RW]
per_page[RW]
preload[RW]
query[R]
search_field[RW]
search_string[RW]
single_record[RW]
sql_filter[RW]
sql_sort[RW]
Public Class Methods
new( 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: {} )
click to toggle source
# 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
Public Instance Methods
add_filter(operator_code:, criterion:, comparate:)
click to toggle source
# 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()
click to toggle source
# 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()
click to toggle source
# 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)
click to toggle source
# 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
query=(value)
click to toggle source
# File lib/query_helper.rb, line 100 def query=(value) if value.class < ActiveRecord::Relation @query = value.to_sql @model = value.base_class else @query = value end return "" end
results()
click to toggle source
# File lib/query_helper.rb, line 178 def results execute_query() return paginated_results() if @api_payload return @results end
to_json(args)
click to toggle source
# File lib/query_helper.rb, line 148 def to_json(args) results.to_json end
to_sql()
click to toggle source
# 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 )
click to toggle source
# 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()
click to toggle source
# File lib/query_helper.rb, line 163 def view_query to_sql end
Private Instance Methods
clean_results()
click to toggle source
# File lib/query_helper.rb, line 263 def clean_results @results.map!{ |r| r.except("_query_full_count") } if @page && @per_page && !@single_record end
create_column_maps()
click to toggle source
# File lib/query_helper.rb, line 267 def create_column_maps ColumnMap.create_column_mappings( query: @query, custom_mappings: @custom_mappings, model: @model ) end
determine_count()
click to toggle source
# File lib/query_helper.rb, line 239 def determine_count # Determine total result count (unpaginated) if @single_record @count = 1 else @count = @page && @per_page && @results.length > 0 ? @results.first["_query_full_count"] : @results.length end end
determine_page(page:, per_page:)
click to toggle source
# File lib/query_helper.rb, line 209 def determine_page(page:, per_page:) return page.to_i if page return 1 if !page && per_page return nil end
determine_per_page(page:, per_page:)
click to toggle source
# File lib/query_helper.rb, line 215 def determine_per_page(page:, per_page:) return per_page.to_i if per_page return 100 if !per_page && page return nil end
load_associations()
click to toggle source
# File lib/query_helper.rb, line 248 def load_associations @results = Associations.load_associations( payload: @results, associations: @associations, as_json_options: @as_json_options ) end
paginated_results()
click to toggle source
# File lib/query_helper.rb, line 233 def paginated_results { pagination: pagination_results(), data: @results, metadata: @metadata } end
preload_associations()
click to toggle source
# File lib/query_helper.rb, line 256 def preload_associations Associations.preload_associations( payload: @results, preload: @preload ) end
search_filter(column_maps)
click to toggle source
# File lib/query_helper.rb, line 275 def search_filter(column_maps) raise ArgumentError.new("search_fields not defined") unless @search_fields.length > 0 placement = :where maps = column_maps.select do |cm| placement = :having if cm.aggregate @search_fields.include? cm.alias_name end bind_variable = ('a'..'z').to_a.shuffle[0,20].join.to_sym @bind_variables[bind_variable] = "%#{@search_string}%" filter = "#{maps.map{|m| "coalesce(#{m.sql_expression}::varchar, '')"}.join(" || ")} ilike :#{bind_variable}" return { filter: filter, placement: placement } end
set_limit_and_offset()
click to toggle source
# File lib/query_helper.rb, line 221 def set_limit_and_offset 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[:limit] = limit @bind_variables[:offset] = offset end end