Class: Tina4::QueryBuilder

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

Overview

QueryBuilder — Fluent SQL query builder.

Usage:

# Standalone
result = Tina4::QueryBuilder.from_table("users", db: db)
.select("id", "name")
.where("active = ?", [1])
.order_by("name ASC")
.limit(10)
.get

# From ORM model
result = User.query
.where("age > ?", [18])
.order_by("name")
.get

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(table, db: nil, primary_key: nil) ⇒ QueryBuilder

Returns a new instance of QueryBuilder.



22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
 
# File 'lib/tina4/query_builder.rb', line 22

def initialize(table, db: nil, primary_key: nil)
  @table = table
  @db = db
  @columns = ["*"]
  @select_params = []
  @wheres = []
  @params = []
  @joins = []
  @group_by_cols = []
  @havings = []
  @having_params = []
  @order_by_cols = []
  @order_by_params = []
  @primary_key = primary_key&.to_s
  @limit_val = nil
  @offset_val = nil
end

Class Method Details

.from_table(table_name, db: nil, primary_key: nil) ⇒ QueryBuilder

Create a QueryBuilder for a table.

Parameters:

  • table_name (String) —

    The database table name.

  • db (Object, nil) (defaults to: nil) —

    Optional database connection.

Returns:



45
46
47
 
# File 'lib/tina4/query_builder.rb', line 45

def self.from_table(table_name, db: nil, primary_key: nil)
  new(table_name, db: db, primary_key: primary_key)
end

Instance Method Details

#bbox(column, min_lon, min_lat, max_lon, max_lat, srid: Point::DEFAULT_SRID) ⇒ Object

Raises:

  • (ArgumentError) 


144
145
146
147
148
149
150
151
152
 
# File 'lib/tina4/query_builder.rb', line 144

def bbox(column, min_lon, min_lat, max_lon, max_lat, srid: Point::DEFAULT_SRID)
  values = [min_lon, min_lat, max_lon, max_lat].map { |value| Float(value) }
  raise ArgumentError, "Bounding-box coordinates must be finite" unless values.all?(&:finite?)
  west, south, east, north = values
  Point.new(west, south, srid: srid)
  Point.new(east, north, srid: srid)
  raise ArgumentError, "Bounding box must be ordered west, south, east, north" if west > east || south > north
  where(SQLTranslator.bbox(engine, column, srid), values)
end

#count ⇒ Integer

Execute the query and return the row count.

Returns:

  • (Integer) —

    Number of matching rows.



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
 
# File 'lib/tina4/query_builder.rb', line 240

def count
  ensure_db!

  # Build a count query by replacing columns
  original = @columns
  original_select_params = @select_params
  original_order = @order_by_cols
  original_order_params = @order_by_params
  @columns = ["COUNT(*) as cnt"]
  @select_params = []
  @order_by_cols = []
  @order_by_params = []
  sql = to_sql
  @columns = original
  @select_params = original_select_params
  @order_by_cols = original_order
  @order_by_params = original_order_params

  all_params = @params + @having_params

  row = @db.fetch_one(sql, all_params.empty? ? [] : all_params)
  return 0 if row.nil?

  # Handle case-insensitive column names
  (row["cnt"] || row["CNT"] || row[:cnt] || row[:CNT] || 0).to_i
end

#exists? ⇒ Boolean

Check whether any matching rows exist.

Returns:

  • (Boolean) 


270
271
272
 
# File 'lib/tina4/query_builder.rb', line 270

def exists?
  count > 0
end

#first ⇒ Hash?

Execute the query and return a single row.

Returns:

  • (Hash, nil) —

    A single row hash, or nil.



229
230
231
232
233
234
235
 
# File 'lib/tina4/query_builder.rb', line 229

def first
  ensure_db!
  sql = to_sql
  all_params = @select_params + @params + @having_params + @order_by_params

  @db.fetch_one(sql, all_params.empty? ? [] : all_params)
end

#get ⇒ Object

Execute the query and return the database result.

v3.13.39: with no .limit() set, get returns ALL matching rows. It previously applied a silent default LIMIT 100 — a data-loss-on-read footgun where the 101st row vanished without a trace. An explicit .limit(n) is still honoured; to_sql never injects a default LIMIT either. When no limit was requested we pass limit: nil to db.fetch — its "no truncation" sentinel (fetch only appends LIMIT when limit is truthy and the SQL doesn't already carry one).

Returns:

  • (Object) —

    The result from db.fetch.



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

def get
  ensure_db!
  sql = to_sql
  all_params = @select_params + @params + @having_params + @order_by_params

  @db.fetch(
    sql,
    all_params.empty? ? [] : all_params,
    limit: @limit_val,
    offset: @offset_val || 0
  )
end

#group_by(column) ⇒ self

Add a GROUP BY column.

Parameters:

  • column (String) —

    Column name.

Returns:

  • (self) 


107
108
109
110
 
# File 'lib/tina4/query_builder.rb', line 107

def group_by(column)
  @group_by_cols << column
  self
end

#having(expression, params = []) ⇒ self

Add a HAVING clause.

Parameters:

  • expression (String) —

    HAVING expression with ? placeholders.

  • params (Array) (defaults to: []) —

    Parameter values.

Returns:

  • (self) 


117
118
119
120
121
 
# File 'lib/tina4/query_builder.rb', line 117

def having(expression, params = [])
  @havings << expression
  @having_params.concat(params)
  self
end

#intersects(column, geometry, srid: Point::DEFAULT_SRID) ⇒ Object 



139
140
141
142
 
# File 'lib/tina4/query_builder.rb', line 139

def intersects(column, geometry, srid: Point::DEFAULT_SRID)
  bound, form = Point.geometry_binding(geometry, srid: srid)
  where(SQLTranslator.intersects(engine, column, form, srid), [bound])
end

#join(table, on_clause) ⇒ self

Add an INNER JOIN.

Parameters:

  • table (String) —

    Table to join.

  • on_clause (String) —

    Join condition.

Returns:

  • (self) 


88
89
90
91
 
# File 'lib/tina4/query_builder.rb', line 88

def join(table, on_clause)
  @joins << "INNER JOIN #{table} ON #{on_clause}"
  self
end

#left_join(table, on_clause) ⇒ self

Add a LEFT JOIN.

Parameters:

  • table (String) —

    Table to join.

  • on_clause (String) —

    Join condition.

Returns:

  • (self) 


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

def left_join(table, on_clause)
  @joins << "LEFT JOIN #{table} ON #{on_clause}"
  self
end

#limit(count, offset = nil) ⇒ self

Set LIMIT and optional OFFSET.

Parameters:

  • count (Integer) —

    Maximum rows to return.

  • offset (Integer, nil) (defaults to: nil) —

    Number of rows to skip.

Returns:

  • (self) 


177
178
179
180
181
 
# File 'lib/tina4/query_builder.rb', line 177

def limit(count, offset = nil)
  @limit_val = count
  @offset_val = offset unless offset.nil?
  self
end

#or_where(condition, params = []) ⇒ self

Add a WHERE condition with OR.

Parameters:

  • condition (String) —

    SQL condition with ? placeholders.

  • params (Array) (defaults to: []) —

    Parameter values.

Returns:

  • (self) 


77
78
79
80
81
 
# File 'lib/tina4/query_builder.rb', line 77

def or_where(condition, params = [])
  @wheres << ["OR", condition]
  @params.concat(params)
  self
end

#order_by(expression) ⇒ self

Add an ORDER BY clause.

Parameters:

  • expression (String) —

    Column and direction (e.g. "name ASC").

Returns:

  • (self) 


127
128
129
130
 
# File 'lib/tina4/query_builder.rb', line 127

def order_by(expression)
  @order_by_cols << expression
  self
end

#order_by_distance(column, point, direction: "ASC", srid: Point::DEFAULT_SRID) ⇒ Object

Raises:

  • (ArgumentError) 


161
162
163
164
165
166
167
168
169
170
 
# File 'lib/tina4/query_builder.rb', line 161

def order_by_distance(column, point, direction: "ASC", srid: Point::DEFAULT_SRID)
  direction = direction.to_s.upcase
  raise ArgumentError, "Distance order direction must be ASC or DESC" unless %w[ASC DESC].include?(direction)
  raise ArgumentError, "Stable spatial ordering needs a primary key; use ORM.query or pass primary_key:" if @primary_key.to_s.empty?
  point = Point.parse(point, srid: srid)
  @order_by_cols << "#{SQLTranslator.distance(engine, column, point.srid)} #{direction}"
  @order_by_params.concat([point.lon, point.lat])
  @order_by_cols << "#{SQLTranslator.spatial_identifier(@primary_key, 'primary key')} ASC"
  self
end

#select(*columns) ⇒ self

Set the columns to select.

Parameters:

  • columns (Array<String>) —

    Column names.

Returns:

  • (self) 


53
54
55
56
57
58
59
 
# File 'lib/tina4/query_builder.rb', line 53

def select(*columns)
  unless columns.empty?
    @columns = columns
    @select_params = []
  end
  self
end

#select_distance(column, point, alias_name: "distance", srid: Point::DEFAULT_SRID) ⇒ Object 



154
155
156
157
158
159
 
# File 'lib/tina4/query_builder.rb', line 154

def select_distance(column, point, alias_name: "distance", srid: Point::DEFAULT_SRID)
  point = Point.parse(point, srid: srid)
  @columns << SQLTranslator.distance_as(engine, column, alias_name, point.srid)
  @select_params.concat([point.lon, point.lat])
  self
end

#to_mongo ⇒ Hash

Convert the fluent builder state into a MongoDB-compatible query hash.

Returns:

  • (Hash) —

    with keys :filter, :projection, :sort, :limit, :skip (only non-empty).



277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
 
# File 'lib/tina4/query_builder.rb', line 277

def to_mongo
  result = {}

  # -- projection --
  if @columns != ["*"]
    result[:projection] = @columns.each_with_object({}) { |col, h| h[col.strip] = 1 }
  end

  # -- filter --
  unless @wheres.empty?
    param_index = 0
    and_conditions = []
    or_conditions = []

    @wheres.each_with_index do |(connector, condition), i|
      mongo_cond, param_index = parse_condition_to_mongo(condition, param_index)
      if i == 0 || connector == "AND"
        and_conditions << mongo_cond
      else
        or_conditions << mongo_cond
      end
    end

    if or_conditions.any?
      and_merged = merge_mongo_conditions(and_conditions)
      all_branches = [and_merged] + or_conditions
      result[:filter] = { "$or" => all_branches }
    else
      result[:filter] = merge_mongo_conditions(and_conditions)
    end
  end

  # -- sort --
  unless @order_by_cols.empty?
    sort = {}
    @order_by_cols.each do |expr|
      parts = expr.strip.split(/\s+/)
      field = parts[0]
      direction = (parts[1] && parts[1].upcase == "DESC") ? -1 : 1
      sort[field] = direction
    end
    result[:sort] = sort
  end

  # -- limit / skip --
  result[:limit] = @limit_val unless @limit_val.nil?
  result[:skip] = @offset_val unless @offset_val.nil?

  result
end

#to_sql ⇒ String

Build and return the SQL string without executing.

Returns:

  • (String) —

    The constructed SQL query.



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

def to_sql
  sql = "SELECT #{@columns.join(', ')} FROM #{@table}"

  sql += " #{@joins.join(' ')}" unless @joins.empty?

  sql += " WHERE #{build_where}" unless @wheres.empty?

  sql += " GROUP BY #{@group_by_cols.join(', ')}" unless @group_by_cols.empty?

  sql += " HAVING #{@havings.join(' AND ')}" unless @havings.empty?

  sql += " ORDER BY #{@order_by_cols.join(', ')}" unless @order_by_cols.empty?

  sql
end

#where(condition, params = []) ⇒ self

Add a WHERE condition with AND.

Parameters:

  • condition (String) —

    SQL condition with ? placeholders.

  • params (Array) (defaults to: []) —

    Parameter values.

Returns:

  • (self) 


66
67
68
69
70
 
# File 'lib/tina4/query_builder.rb', line 66

def where(condition, params = [])
  @wheres << ["AND", condition]
  @params.concat(params)
  self
end

#within_distance(column, point, radius_metres, srid: Point::DEFAULT_SRID) ⇒ Object

Raises:

  • (ArgumentError) 


132
133
134
135
136
137
 
# File 'lib/tina4/query_builder.rb', line 132

def within_distance(column, point, radius_metres, srid: Point::DEFAULT_SRID)
  radius = Float(radius_metres)
  raise ArgumentError, "Spatial radius must be finite and greater than or equal to zero" unless radius.finite? && radius >= 0
  point = Point.parse(point, srid: srid)
  where(SQLTranslator.within_distance(engine, column, point.srid), [point.lon, point.lat, radius])
end