require 'active_record' module PwQuery class Service DEFAULT_LIMIT = 50 def initialize(model, params, options = {}, associations = nil) @model = model @params = params @options = options @associations = associations @total = @model.count @limit = @params.fetch(:limit, DEFAULT_LIMIT).to_i @meta = { total: @total, limit: @limit, page: @params.fetch(:page, 1).to_i, page_size: @total, total_pages: (@total.to_f / @limit).ceil } end def perform query = @model.all query = include_associations(query, @associations) query = apply_filters(query, @params[:filters]) query = apply_sorting(query, @params) query = apply_field_selection(query, @params) query = apply_date_range(query, @params[:date_range]) if @options[:date_scope] query = apply_time_range(query, @params[:time_range]) query = apply_search(query, @params[:search]) query = apply_range(query, @params[:range]) query = apply_pagination(query, @params) query = apply_aggregation(query, @params[:aggregation]) handle_query(query) { meta: @meta, data: query } end def include_associations(query, associations) query.includes(associations) end def apply_filters(query, filters) return query if filters.blank? filters.each do |key, value| next if value.blank? values = value.to_s.split(',').map(&:strip) condition = values.size > 1 ? values : value query = query.where(key => condition) end query end def apply_sorting(query, params) if params[:sort_by] apply_multi_sort(query, params[:sort_by]) else apply_sort(query, params[:sort] || 'created_at:DESC') end end def apply_sort(query, value) sort_field, sort_order = value.split(':') query.order("#{sort_field} #{sort_order}") end def apply_multi_sort(query, value) sort_params = value.split(',') sorting_criteria = sort_params.map { |param| param.split(':').join(' ') } query.order(sorting_criteria.join(', ')) end def apply_field_selection(query, params) if params[:projection] || params[:fields] fields = params[:projection] || params[:fields] query.select(fields.split(',')) else query end end def apply_date_range(query, value) start_date = value ? parse_date(value['start']) : 1.month.ago.beginning_of_day end_date = value ? parse_date(value['end']) : Time.current query.where(created_at: start_date...end_date) end def apply_time_range(query, value) return query if value.blank? start_time = parse_time(value['start'] || '00:00:00') end_time = parse_time(value['end'] || '23:59:59') table_name = query.model.table_name if start_time <= end_time query.where("CAST(#{table_name}.created_at AS time) BETWEEN ? AND ?", start_time, end_time) else query.where("CAST(#{table_name}.created_at AS time) >= ? OR CAST(#{table_name}.created_at AS time) <= ?", start_time, end_time) end end def apply_search(query, filters) return query if filters.blank? filters = filters.to_unsafe_h conditions = build_filter_conditions(query, filters) apply_conditions(query, conditions) end def build_filter_conditions(query, filters) filters.map do |key, value| next if value.blank? key.include?('.') ? association_condition(query, key, value) : direct_condition(query, key, value) end.compact end def association_condition(query, key, value) association_chain = key.split('.') attribute = association_chain.pop current_table = build_join_chain(query, association_chain) create_condition(current_table, attribute, value) end def build_join_chain(query, chain) current_model = query.klass current_table = current_model.table_name join_chain = [] chain.each do |assoc| reflection = current_model.reflect_on_association(assoc.to_sym) or raise "Unknown association: #{assoc} for model #{current_model}" table_alias = "#{reflection.klass.table_name}_as_#{current_table}" join_chain << { table: reflection.klass.table_name, alias: table_alias, foreign_key: reflection.foreign_key, parent_table: current_table } current_table = table_alias current_model = reflection.klass end query.joins(join_chain.map do |join| "LEFT JOIN #{join[:table]} AS #{join[:alias]} ON #{join[:alias]}.id = #{join[:parent_table]}.#{join[:foreign_key]}" end.join(' ')) end def direct_condition(query, key, value) create_condition(query.klass.table_name, key, value) end def create_condition(table, attribute, value) values = value.to_s.split(',').map(&:strip) column = get_column_type(table, attribute) case column&.type when :string, :text condition = if values.size > 1 values.map do |_v| "LOWER(#{table}.#{attribute}::text) LIKE LOWER(?)" end.join(' OR ') else "LOWER(#{table}.#{attribute}::text) LIKE LOWER(?)" end values = values.size > 1 ? values.map { |v| "%#{escape_like(v)}%" } : ["%#{escape_like(value)}%"] [condition, *values] else values.size > 1 ? ["#{table}.#{attribute} IN (?)", values] : ["#{table}.#{attribute} = ?", value] end end def apply_conditions(query, conditions) return query unless conditions.any? where_clause = conditions.map(&:first).join(' OR ') where_values = conditions.map(&:last).flatten query.where(where_clause, *where_values) end def apply_range(query, ranges) return query if ranges.blank? ranges.each do |key, value| range_values = value.split(',') min_value = range_values[0].to_f if range_values[0].present? max_value = range_values[1].to_f if range_values[1].present? if min_value.present? && max_value.present? query = query.where("#{key} BETWEEN ? AND ?", min_value, max_value) elsif min_value.present? query = query.where("#{key} >= ?", min_value) elsif max_value.present? query = query.where("#{key} <= ?", max_value) end end query end def apply_pagination(query, params) page = params.fetch(:page, 1).to_i offset_value = page == 1 ? 0 : (page - 1) * @limit total_pages = (@total.to_f / @limit).ceil page <= total_pages ? query.offset(offset_value).limit(@limit) : query.none end def apply_aggregation(query, value) return query if value.nil? || value.empty? aggregation_type, aggregation_field = value.split(':') case aggregation_type when 'count' query.count when 'sum' query.sum(aggregation_field) when 'average' query.average(aggregation_field) when 'max' query.maximum(aggregation_field) when 'min' query.minimum(aggregation_field) else query end end def get_column_type(table, attribute) klass = table.classify.constantize klass.columns_hash[attribute.to_s] rescue StandardError nil end def escape_like(string) string.gsub(/[\\%_]/) { |m| "\\#{m}" } end def parse_time(time_string) Time.parse(time_string).strftime('%H:%M:%S') rescue ArgumentError '00:00:00' end def parse_date(date_string) Date.parse(date_string) if date_string.present? rescue ArgumentError nil end private def handle_query(query) @meta['page_size'] = query.count query end end end