Class: WOWSQL::QueryBuilder

Inherits:
Object
  • Object
show all
Defined in:
lib/wowsql/query_builder.rb

Overview

Fluent query builder — all operations translate to PostgREST query parameters.

Examples:

result = client.table("users")
  .select("id", "email", "name")
  .eq("is_active", true)
  .order_by("created_at", "desc")
  .limit(20)
  .get

Instance Method Summary collapse

Constructor Details

#initialize(client, table_name) ⇒ QueryBuilder

Returns a new instance of QueryBuilder.



14
15
16
17
18
19
20
21
22
23
24
 
# File 'lib/wowsql/query_builder.rb', line 14

def initialize(client, table_name)
  @client     = client
  @table_name = table_name
  @filters    = []
  @select_cols = nil
  @group_by   = []
  @having     = []
  @order      = []
  @limit_val  = nil
  @offset_val = nil
end

Instance Method Details

#avg(column) ⇒ Object 



214
215
216
217
218
219
220
221
222
223
224
 
# File 'lib/wowsql/query_builder.rb', line 214

def avg(column)
  saved_sel, @select_cols = @select_cols, ["avg(#{column})"]
  @limit_val  = nil
  @offset_val = nil
  begin
    result = get
    (result.dig('data', 0, 'avg') || 0).to_f
  ensure
    @select_cols = saved_sel
  end
end

#between(column, min_val, max_val) ⇒ Object 



69
70
71
 
# File 'lib/wowsql/query_builder.rb', line 69

def between(column, min_val, max_val)
  filter(column, 'between', [min_val, max_val])
end

#count ⇒ Object 



183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
 
# File 'lib/wowsql/query_builder.rb', line 183

def count
  saved = [@select_cols, @group_by, @having, @order, @limit_val, @offset_val]
  @select_cols = nil
  @group_by    = []
  @having      = []
  @order       = []
  @limit_val   = 0
  @offset_val  = nil

  begin
    params = filters_to_params(@filters)
    params['limit'] = '0'
    @client.request('GET', "/#{@table_name}", params, nil, 'Prefer' => 'count=exact')
    WOWSQLClient.parse_total_from_content_range(@client.last_content_range, 0)
  ensure
    @select_cols, @group_by, @having, @order, @limit_val, @offset_val = saved
  end
end

#eq(column, value) ⇒ Object 



56
 
# File 'lib/wowsql/query_builder.rb', line 56

def eq(column, value)         filter(column, 'eq',       value) end

#filter(column, operator = nil, value = nil, logical_op: 'AND') ⇒ Object

── Filters ──────────────────────────────────────────────────



36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
 
# File 'lib/wowsql/query_builder.rb', line 36

def filter(column, operator = nil, value = nil, logical_op: 'AND')
  if column.is_a?(Hash)
    if column.key?('column') || column.key?(:column)
      col = column['column'] || column[:column]
      op  = column['operator'] || column[:operator]
      val = column['value'] || column[:value]
      lo  = column['logical_op'] || column[:logical_op] || logical_op
      @filters << { column: col.to_s, operator: op.to_s, value: val, logical_op: lo }
    else
      column.each do |col_name, col_value|
        @filters << { column: col_name.to_s, operator: 'eq', value: col_value, logical_op: logical_op }
      end
    end
  else
    raise ArgumentError, "filter() missing required argument: 'operator'" if operator.nil?
    @filters << { column: column.to_s, operator: operator.to_s, value: value, logical_op: logical_op }
  end
  self
end

#first ⇒ Object 



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

def first
  result = limit(1).get
  data   = result['data']
  data && !data.empty? ? data[0] : nil
end

#get(_additional_options = nil) ⇒ Hash Also known as: execute

Execute the query using PostgREST native query parameters.

Returns:

  • (Hash) —

    { data: Array, count: Integer, total: Integer, limit: Integer, offset: Integer }



131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
 
# File 'lib/wowsql/query_builder.rb', line 131

def get(_additional_options = nil)
  params = {}

  # SELECT
  sel = @select_cols ? @select_cols.dup : []
  unless @group_by.empty?
    sel = (sel + @group_by).uniq
  end
  params['select'] = sel.join(',') unless sel.empty?

  # FILTERS → PostgREST native column=op.value
  filters_to_params(@filters).each { |k, v| params[k] = v }

  # ORDER → PostgREST: order=col.asc,col2.desc
  unless @order.empty?
    parts = @order.map { |o| "#{o[:column]}.#{o[:direction] || 'asc'}" }
    params['order'] = parts.join(',')
  end

  params['limit']  = @limit_val.to_s  if @limit_val
  params['offset'] = @offset_val.to_s if @offset_val

  result = @client.request('GET', "/#{@table_name}", params, nil, 'Prefer' => 'count=exact')

  data   = normalise_data(result)
  total  = WOWSQLClient.parse_total_from_content_range(@client.last_content_range, data.length)

  {
    'data'   => data,
    'count'  => data.length,
    'total'  => total,
    'limit'  => @limit_val || 100,
    'offset' => @offset_val || 0
  }
end

#group_by(*columns) ⇒ Object 



103
104
105
106
107
 
# File 'lib/wowsql/query_builder.rb', line 103

def group_by(*columns)
  cols = columns.length == 1 && columns[0].is_a?(Array) ? columns[0] : columns
  @group_by = cols.map(&:to_s)
  self
end

#gt(column, value) ⇒ Object 



58
 
# File 'lib/wowsql/query_builder.rb', line 58

def gt(column, value)         filter(column, 'gt',       value) end

#gte(column, value) ⇒ Object 



59
 
# File 'lib/wowsql/query_builder.rb', line 59

def gte(column, value)        filter(column, 'gte',      value) end

#having(column, operator, value) ⇒ Object 



109
110
111
112
 
# File 'lib/wowsql/query_builder.rb', line 109

def having(column, operator, value)
  @having << { column: column.to_s, operator: operator.to_s, value: value }
  self
end

#ilike(column, pattern) ⇒ Object 



63
 
# File 'lib/wowsql/query_builder.rb', line 63

def ilike(column, pattern)    filter(column, 'ilike',    pattern) end

#in_list(column, values) ⇒ Object 



66
 
# File 'lib/wowsql/query_builder.rb', line 66

def in_list(column, values)   filter(column, 'in',       values) end

#is_not_null(column) ⇒ Object 



65
 
# File 'lib/wowsql/query_builder.rb', line 65

def is_not_null(column)       filter(column, 'is_not',   nil) end

#is_null(column) ⇒ Object 



64
 
# File 'lib/wowsql/query_builder.rb', line 64

def is_null(column)           filter(column, 'is',       nil) end

#like(column, pattern) ⇒ Object 



62
 
# File 'lib/wowsql/query_builder.rb', line 62

def like(column, pattern)     filter(column, 'like',     pattern) end

#limit(n) ⇒ Object

── Pagination ───────────────────────────────────────────────



116
117
118
119
 
# File 'lib/wowsql/query_builder.rb', line 116

def limit(n)
  @limit_val = n.to_i
  self
end

#lt(column, value) ⇒ Object 



60
 
# File 'lib/wowsql/query_builder.rb', line 60

def lt(column, value)         filter(column, 'lt',       value) end

#lte(column, value) ⇒ Object 



61
 
# File 'lib/wowsql/query_builder.rb', line 61

def lte(column, value)        filter(column, 'lte',      value) end

#neq(column, value) ⇒ Object 



57
 
# File 'lib/wowsql/query_builder.rb', line 57

def neq(column, value)        filter(column, 'neq',      value) end

#not_between(column, min_val, max_val) ⇒ Object 



73
74
75
 
# File 'lib/wowsql/query_builder.rb', line 73

def not_between(column, min_val, max_val)
  filter(column, 'not_between', [min_val, max_val])
end

#not_in(column, values) ⇒ Object 



67
 
# File 'lib/wowsql/query_builder.rb', line 67

def not_in(column, values)    filter(column, 'not_in',   values) end

#offset(n) ⇒ Object 



121
122
123
124
 
# File 'lib/wowsql/query_builder.rb', line 121

def offset(n)
  @offset_val = n.to_i
  self
end

#or_filter(column, operator, value) ⇒ Object 



77
78
79
 
# File 'lib/wowsql/query_builder.rb', line 77

def or_filter(column, operator, value)
  filter(column, operator, value, logical_op: 'OR')
end

#order(column, direction = 'asc') ⇒ Object 



99
100
101
 
# File 'lib/wowsql/query_builder.rb', line 99

def order(column, direction = 'asc')
  order_by(column, direction)
end

#order_by(column, direction = 'asc') ⇒ Object

── Ordering / grouping ──────────────────────────────────────



83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
 
# File 'lib/wowsql/query_builder.rb', line 83

def order_by(column, direction = 'asc')
  if column.is_a?(Array)
    column.each do |item|
      if item.is_a?(Hash)
        @order << { column: (item[:column] || item['column']).to_s,
                    direction: (item[:direction] || item['direction'] || 'asc').to_s }
      elsif item.is_a?(Array) && item.length == 2
        @order << { column: item[0].to_s, direction: item[1].to_s }
      end
    end
  else
    @order << { column: column.to_s, direction: (direction || 'asc').to_s }
  end
  self
end

#paginate(page: 1, per_page: 20) ⇒ Object 



226
227
228
229
230
231
232
233
234
235
236
237
238
 
# File 'lib/wowsql/query_builder.rb', line 226

def paginate(page: 1, per_page: 20)
  page       = [page.to_i, 1].max
  offset_val = (page - 1) * per_page
  result     = limit(per_page).offset(offset_val).get
  total      = result['total'] || result['count'] || 0
  {
    'data'        => result['data'],
    'page'        => page,
    'per_page'    => per_page,
    'total'       => total,
    'total_pages' => total > 0 ? (total + per_page - 1) / per_page : 0
  }
end

#select(*columns) ⇒ Object

── Column selection ─────────────────────────────────────────



28
29
30
31
32
 
# File 'lib/wowsql/query_builder.rb', line 28

def select(*columns)
  cols = columns.length == 1 && columns[0].is_a?(Array) ? columns[0] : columns.map(&:to_s)
  @select_cols = cols.empty? ? nil : cols
  self
end

#single ⇒ Object

Raises:



175
176
177
178
179
180
181
 
# File 'lib/wowsql/query_builder.rb', line 175

def single
  result = limit(2).get
  data   = result['data'] || []
  raise WOWSQLError.new('No records found')                      if data.empty?
  raise WOWSQLError.new('Multiple records found, expected one')  if data.length > 1
  data[0]
end

#sum(column) ⇒ Object 



202
203
204
205
206
207
208
209
210
211
212
 
# File 'lib/wowsql/query_builder.rb', line 202

def sum(column)
  saved_sel, @select_cols = @select_cols, ["sum(#{column})"]
  @limit_val  = nil
  @offset_val = nil
  begin
    result = get
    (result.dig('data', 0, 'sum') || 0).to_f
  ensure
    @select_cols = saved_sel
  end
end