Class: QueryResult

Inherits:
Object
  • Object
show all
Defined in:
lib/sql_builder/query_result.rb

Overview

wraps the result of a SQL query into a container of objects a new QueryResult will contain an array of objects implementing a custom class derived from QueryRow containing convenience methods to access values

Constant Summary collapse

TRUE_STRINGS = 
%w(1 true t)
COLUMN_TYPES = 
i(raw array bool dollars time date integer float json geo string)

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(raw) ⇒ QueryResult

Returns a new instance of QueryResult.



10
11
12
13
14
15
16
17
18
19
20
21
 
# File 'lib/sql_builder/query_result.rb', line 10

def initialize(raw)
  @columns = []
  @column_types = {}
  if raw.empty?
    @array = raw
  else
    @row_class = create_row_class raw.first
    @array = raw.map do |raw_row|
      @row_class.new self, raw_row
    end
  end
end

Instance Attribute Details

#column_types ⇒ Object (readonly)

Returns the value of attribute column_types.



8
9
10
 
# File 'lib/sql_builder/query_result.rb', line 8

def column_types
  @column_types
end

#columns ⇒ Object (readonly)

Returns the value of attribute columns.



8
9
10
 
# File 'lib/sql_builder/query_result.rb', line 8

def columns
  @columns
end

Instance Method Details

#as_json(options = {}) ⇒ Object 



109
110
111
112
113
 
# File 'lib/sql_builder/query_result.rb', line 109

def as_json(options={})
  @array.map do |row|
    row.as_json options
  end
end

#column_names ⇒ Object 



82
83
84
 
# File 'lib/sql_builder/query_result.rb', line 82

def column_names
  @columns.map{|c| c[:name]}
end

#compute_column(name, type = nil) ⇒ Object

computes a new column by evaluating a block for every row



126
127
128
129
130
131
132
133
134
 
# File 'lib/sql_builder/query_result.rb', line 126

def compute_column(name, type=nil)
  return unless @row_class # empty result
  name_s = name.to_s
  define_column_method @row_class, name_s, type
  each do |row|
    new_val = yield row
    row.send "#{name}=", new_val
  end
end

#compute_columns ⇒ Object

computes new columns by evaluating a block for every row the block should return a hash of new column values



138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
 
# File 'lib/sql_builder/query_result.rb', line 138

def compute_columns
  return unless @row_class # empty result
  columns_defined = @columns.pluck(:name).map { |key| [key.to_s, true] }.to_h
  each do |row|
    new_vals = yield row
    new_vals.each_key do |name|
      name=name.to_s
      unless columns_defined[name]
        define_column_method @row_class, name
        columns_defined[name]=true
      end
    end
    new_vals.each do |key, val|
      row.send "#{key}=", val
    end
  end
end

#count ⇒ Object 



66
67
68
 
# File 'lib/sql_builder/query_result.rb', line 66

def count
  @array.count
end

#define_column_method(row_class, name, type = nil) ⇒ Object 



164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
 
# File 'lib/sql_builder/query_result.rb', line 164

def define_column_method(row_class, name, type=nil)
  name_s = name.to_s
  type ||= @column_types[name_s] || QueryResult.column_type(name_s)
  unless COLUMN_TYPES.index type.to_sym
    raise "Invalid column type '#{type}', must be one of #{COLUMN_TYPES.map(&:to_s).join(', ')}"
  end
  @columns << {name: name_s, type: type}
  case type
  when :raw
    row_class.define_method name do
      self.instance_variable_get('@raw')[name_s]
    end
    row_class.define_method "#{name}=" do |val|
      self.instance_variable_get('@raw')[name_s] = val
    end
  when :array
    row_class.define_method name do
      val = self.instance_variable_get('@raw')[name_s]
      if val.is_a? String
        # this might not be the best implementation
        val.gsub('{', '').gsub('}', '').gsub('"', '').split(',')
      else
        val
      end
    end
    row_class.define_method "#{name}=" do |val|
      self.instance_variable_get('@raw')[name_s] = val
    end
  when :bool
    row_class.define_method name do
      raw = self.instance_variable_get('@raw')[name_s]
      TRUE_STRINGS.include?(raw.to_s.downcase)
    end
    row_class.define_method "#{name}=" do |val|
      self.instance_variable_get('@raw')[name_s] = TRUE_STRINGS.include?(val.to_s.downcase)
    end
  when :dollars
    row_class.define_method name do
      self.instance_variable_get('@raw')[name_s].to_i/100.0
    end
    row_class.define_method "#{name}=" do |val|
      self.instance_variable_get('@raw')[name_s] = ((val || 0)*100).to_i
    end
  when :time
    row_class.define_method name do
      raw_time = self.instance_variable_get('@raw')[name_s]
      case raw_time
      when String
        # always return times in the local timezone
        if SqlBuilder.default_timezone == :local
          time = Time.parse raw_time
        else # assume database times are in UTC
          time = raw_time.in_time_zone('UTC').getlocal
        end
        self.instance_variable_get('@raw')[name_s] = time
        time
      else
        raw_time
      end
    end
    row_class.define_method "#{name}=" do |val|
      self.instance_variable_get('@raw')[name_s] = val
    end
  when :date
    row_class.define_method name do
      raw_date = self.instance_variable_get('@raw')[name_s]
      case raw_date
      when String
        date = Date.parse(raw_date)
        self.instance_variable_get('@raw')[name_s] = date
        date
      else
        raw_date
      end
    end
    row_class.define_method "#{name}=" do |val|
      self.instance_variable_get('@raw')[name_s] = val
    end
  when :integer
    row_class.define_method name do
      raw = self.instance_variable_get('@raw')[name_s]
      if raw.blank?
        nil
      elsif raw.is_a? Integer
        raw
      elsif raw =~ /^-*\d+$/
        i = raw.to_i
        self.instance_variable_get('@raw')[name_s] = i
        i
      else
        raw
      end
    end
    row_class.define_method "#{name}=" do |val|
      self.instance_variable_get('@raw')[name_s] = val.to_i
    end
  when :float
    row_class.define_method name do
      self.instance_variable_get('@raw')[name_s].to_f
    end
    row_class.define_method "#{name}=" do |val|
      self.instance_variable_get('@raw')[name_s] = val.to_f
    end
  when :json
    row_class.define_method name do
      raw = self.instance_variable_get('@raw')[name_s]
      raw ? JSON.parse(raw) : nil
    end
    row_class.define_method "#{name}=" do |val|
      self.instance_variable_get('@raw')[name_s] = val&.as_json
    end
  when :geo
    row_class.define_method name do
      self.instance_variable_get('@raw')[name_s]&.parse_geo_point
    end
    row_class.define_method "#{name}=" do |val|
      self.instance_variable_get('@raw')[name_s] = val
    end
  else # string
    row_class.define_method name do
      self.instance_variable_get('@raw')[name_s]
    end
    row_class.define_method "#{name}=" do |val|
      self.instance_variable_get('@raw')[name_s] = val.to_s
    end
  end
end

#each ⇒ Object 



23
24
25
26
27
28
 
# File 'lib/sql_builder/query_result.rb', line 23

def each
  return @array.to_enum(:each) unless block_given?
  @array.each do |row|
    yield row
  end
end

#empty? ⇒ Boolean

Returns:

  • (Boolean) 


78
79
80
 
# File 'lib/sql_builder/query_result.rb', line 78

def empty?
  @array.empty?
end

#first ⇒ Object 



74
75
76
 
# File 'lib/sql_builder/query_result.rb', line 74

def first
  @array.first
end

#group_by ⇒ Object 



44
45
46
47
48
49
 
# File 'lib/sql_builder/query_result.rb', line 44

def group_by
  return @array.to_enum(:group_by) unless block_given?
  @array.group_by do |row|
    yield row
  end
end

#index_by ⇒ Object 



51
52
53
54
55
56
 
# File 'lib/sql_builder/query_result.rb', line 51

def index_by
  return @array.to_enum(:index_by) unless block_given?
  @array.index_by do |row|
    yield row
  end
end

#length ⇒ Object 



70
71
72
 
# File 'lib/sql_builder/query_result.rb', line 70

def length
  @array.count
end

#map ⇒ Object 



30
31
32
33
34
35
 
# File 'lib/sql_builder/query_result.rb', line 30

def map
  return @array.to_enum(:map) unless block_given?
  @array.map do |row|
    yield row
  end
end

#remove_column(name) ⇒ Object 



156
157
158
 
# File 'lib/sql_builder/query_result.rb', line 156

def remove_column(name)
  @columns.delete_if {|c| c[:name] == name.to_s}
end

#select ⇒ Object 



37
38
39
40
41
42
 
# File 'lib/sql_builder/query_result.rb', line 37

def select
  return @array.to_enum(:select) unless block_given?
  @array.select do |row|
    yield row
  end
end

#set_column_order(order) ⇒ Object 



97
98
99
100
101
102
103
104
105
106
107
 
# File 'lib/sql_builder/query_result.rb', line 97

def set_column_order(order)
  new_columns = []
  order.each do |name|
    @columns.each do |set|
      if set[:name] == name
        new_columns.append set
      end
    end
  end
  @columns = new_columns
end

#set_column_type(column, type) ⇒ Object 



86
87
88
89
90
91
92
93
94
95
 
# File 'lib/sql_builder/query_result.rb', line 86

def set_column_type(column, type)
  unless self.column_names.index column.to_s
    raise "No column '#{column}' in the result"
  end
  unless COLUMN_TYPES.index type.to_sym
    raise "Invalid column type '#{type}', must be one of #{COLUMN_TYPES.map(&:to_s).join(', ')}"
  end
  @column_types[column.to_s] = type
  define_column_method @row_class, column.to_s
end

#sort_by ⇒ Object 



58
59
60
 
# File 'lib/sql_builder/query_result.rb', line 58

def sort_by
  self.to_a.sort_by
end

#to_a ⇒ Object 



62
63
64
 
# File 'lib/sql_builder/query_result.rb', line 62

def to_a
  @array
end

#to_csv ⇒ Object 



115
116
117
118
119
120
121
122
123
 
# File 'lib/sql_builder/query_result.rb', line 115

def to_csv
  return '' if @array.empty?
  CSV.generate do |csv|
    csv << column_names
    self.each do |row|
      csv << @columns.map{|col| row.serialize_value(col)}
    end
  end
end